Combobox redraw?

[RobBroekhuis]

I've listed this on the ActiveX controls forum, but this one gets more traffic, so please accept my apologies for crossposting.
I'm having some trouble with a ComboBox on an Excel worksheet. The behavior I'm looking for is to dynamically create the dropdown list items every time the user activates the combobox. I've tried to do this using the gotfocus event handler:

Private Sub PromisedFilter_gotfocus()
Dim PromLst(30) As String, PromCount As Integer
Dim i As Integer
MakingList = True 'signal to change event
PromisedFilter.Clear
PromisedFilter.AddItem "(all)"
PromCount = 0
...lots of code here to build PromLst array

For i = 1 To PromCount
PromisedFilter.AddItem PromLst(i)
Next i
MakingList = False
End Sub

Basically, this works, but about half the time, the on-screen display of the combobox will be messed up: the dropped-down list will be mostly gray, with just a single selection line at the top WITH ITS OWN SCROLL ARROWS (right underneath the dropdown arrow). Looks downright weird. Clicking on the list dropdown arrow to collapse the list, and then again to drop it down, returns the list to its normal display. However, I haven't been able to reproduce this "return to normal" programmatically. Am I trapping the wrong event (i.e., should I use an event that triggers BEFORE the combobox receives the focus? Which event might that be)? Or can I somehow "redraw" the control at the end of my event handler code? I tried a number of approaches, none successful.
I'd appreciate any pointers.
Rob

 

[vb5prgrmr]

See my answer in the other forum where you posted

thread708-412925

 

[RobBroekhuis]

I tried, and it didn't help. Thanks, though - I was not aware of the DoEvents function before.
Rob

 

[xlbo]

Rob - have you tried using the Click event of the combo box rather than the gotFocus one
I've done similar many times (dynamically changing the contents every time it is used) and I've never had any problems with using the click event..... Rgds
~Geoff~

 

[RobBroekhuis]

Geoff,
Can you explain how you've done it in the past? I just tried to see when the click event is triggered, and I can only get it to go when the user makes a choice from the combo list - which is AFTER I want the list to update.
Thanks for your help
Rob

 

[RobBroekhuis]

Just in case I trigger an aha in anyone's memory - here's an image of the strange combobox look:

Rob

 

[xlbo]

Sorry Rob - was confusing myself - I was using the CHANGE event of one combo to set the list for another

Can't say I've seeen that before but looks like you have a listbox within your combobox.....weird I agree Rgds
~Geoff~

 

[vb5prgrmr]

Check the style property of the combo box...

 

[RobBroekhuis]

The .style = fmStyleDropdownList, which I think is what I want, no? The combobox works just fine, except for when its list is dynamically regenerated.
Rob

 

[RobBroekhuis]

Well, I found a workaround. I put this at the end of my GotFocus event handler:

Application.ScreenUpdating = False
Sheets(2).Activate
Sheets(1).Activate
Application.ScreenUpdating = True
PromisedFilter.DropDown

Ugly, yes, but it gets the job done, and is transparent to the user. I'm still open to any prettier suggestions...
Rob