Combobox on form has focus on deletion of page (through code) error 1

[JasGrand]

Hello, i have a problem with my excel app... This is a hypothetical situation... but lets say the client clicks on a dropdown box on sheet 3 and then clicks on page 1 and clicks the delete page button... inputbox pops up and asks what page the client wants to delete... and he enters 3 (for sheet 3)... problem is... the combobox still has focus on sheet 3 (notice how all the menu's change when you have the combobox selected... we'll i'm thinking that's the problem)... when he clicks ok on the inputbox, it goes through the code, selects the sheet 3 and deletes it... on the delete command i get the following error:

"Runtime error 424: Object required"


Any help is appreciated...


Jason

 

[JasGrand]

sorry, the title of the post is wrong... the combobox is on the sheet not on a userform...

Thanks again,


Jason

 

[JasGrand]

Also, this only happens when i select a dropdown box... the page deletes normally when there is no combobox selected.

Thanks again,


Jason

 

[Loomah]

What code do you already have?

The basic suggestion is to deselect the ListBox after getting out of it what you want

eg

Private Sub ListBox1_Click()
MsgBox ListBox1.Value
End Sub

Private Sub ListBox1_GotFocus()
[a1].Activate
End Sub

But I can't suggest any more without knowing what you already have.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[JasGrand]

omg.... i've been doing it from a code module and for some reason it wasn't working... but i never even thought of putting it in the onchange sub... Ok, that takes care of when a user actually clicks and selects something from the dropdown box... but what if the user selects something that was already selected... or clickes the arrow button and then clicks the arror button again... the dropdown box still has the focus. Is there a way to make the combobox loose focus in that situation?

Thanks a million,


Jason

 

[JasGrand]

ok, new update... it's got nothing to do with the combobox having focus or not... the error occurs when a user selects something from the combobox... the error occurs whether or not the combobox has focus. recap... the error doesn't occur when the user doesn't select anything from the dropdown boxes...

this is the code i'm using to delete the sheet:

'// Turn Alerts Off
Application.DisplayAlerts = False

'// Select Sheet to Delete
ThisWorkbook.Sheets("Page " & varInput).Select

'// Delete Active Sheet
ThisWorkbook.Sheets("Page " & varInput).Delete

'// Turn Alerts On
Application.DisplayAlerts = True

Note: varInput contains the page number passed from the input box.

 

[JasGrand]

i can't even trap the error... just a msgbox with Runtime error 424: Object required pops up and i have to click the "End" button.

Any help is appreciated...


Jason

 

[Loomah]

Jason
I don't really know what's happening here. You'll need to try stepping through the troublesome code and let us know where the problem occurs then give all the code you have.

I've just run the following without any problems

Private Sub CommandButton1_Click()
Dim resp As String
resp = InputBox("Enter sheet no. to delete")
    On Error GoTo mash
    Application.DisplayAlerts = False
    Worksheets("Sheet" & CInt(resp)).Delete
    Application.DisplayAlerts = True
Exit Sub

mash:
If Err.Number = 9 Then
    MsgBox "No Sheet to delete"
Else
    MsgBox "Error " & Err.Number & " : " & Err.Description & " has occured!"
End If
Resume Next
End Sub

I should go out now!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[JasGrand]

do you have an email address where i can send the excel sheet?

thanks,


Jason

 

[Loomah]

I don't usually post my email address but on this occasion
bellm at globalnet dot co dot uk

But note the UK bit, it's gone 7pm here on a Friday night and I'm still lurking around this site!!! Must get a life.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[JasGrand]

Thanks, just emailed it to ya and sorry about the time...

Jason