Excel - Combo Box - Change Event - Error 2

[DaleWatson123321]

I have a Combo Box where I want the selection chosen by the user to trigger an update to a list used by a second Combo Box.

I have a VBA routine that works perfectly when run independently. However, when I attach this same routine to the ComboBox_Change event, it errors out.

It appears certain that the error relates to the Combo Box still being "selected".

So, here's the "key question"...

Does anyone know what code can be used to "de-select" a Combo Box ???

Your help will be very much appreciated !!!

Thanks. ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlbo]

Hey Dale - you wanna post the code ??

If not, have a look at the .SETFOCUS method
This will set the focus to a different element of the form

so if you click on Combo1, you might want to do
Combo2.setfocus

to take the focus away from combo1

However, I'm intrigued 'cos I've used combo boxes to update lists for other comboboxes and I've never run into that kind of error before. Generally, in excel, I've never had to use the setfocus property (it's much more widely used in Access)

Rgds
~Geoff~

 

[xlhelp]

Hi, Dale!

Please check out this link

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaof10/html/ofevtChange.asp

hopefully that helps.

Indu

 

[DaleWatson123321]

Thanks, guys, for your help, especially the link to the MSDN site.

Stars to both of you.

As it turned out, I had gone ahead come up with a solution on my own.

I tried the ComboBox1.SetFocus option, but couldn't get it to work.

Here the routine that now works.

Private Sub ComboBox2_Change()
Extract_UniqueModuleList
ComboBox2.Select
ComboBox2.Application.ActiveCell.Select
ComboBox1.Activate
End Sub

The "Extract_UniqueModuleList" routine populates ComboBox1 based on the selection from ComboBox2.

"ComboBox2.Select" de-activates the focus from "within" the box, to the box itself.

And "ComboBox2.Application.ActiveCell.Select" is then able to return focus to the ActiveCell.

Other Variations of the above caused Excel to crash, so I ended up with the above. For example, using ComboBox1.Dropdown causes Excel to crash.

Thanks again for your help.

...Dale