Excel - strange combo box 1

[RobBroekhuis]

Hi all,
I asked this question once before, about a year ago, but never found a solution, so I apologize for the slow stutter ;-)
I have an on-sheet combo box from the controls toolbox. The combo box contents get dynamically generated by some code that I'll put at the end of this post. Works like a charm, except that sometimes the combobox takes on a very odd appearance:

instead of

Even in the first version, all the list items are there, but somehow they appear inside the little Spin-Control-like sub-control. The strange version seems to appear when the listdown icon is first clicked; clicking it again makes the proper version appear. If you have any suggestions (even just a referral to a more appropriate forum), please chime in.
Thanks
Rob

Private Sub PromisedFilter_gotfocus()
   Dim PromLst(30) As String, PromCount As Integer, cell As Range, p As Integer
   Dim s As String, s1 As String, i As Integer, nrow As Integer, LastRow As Integer
   MakingList = True
   Application.EnableEvents = False
   PromisedFilter.Clear
   PromisedFilter.AddItem "(all)"
...code here to generate array PromLst from sheet contents...
   For i = 1 To PromCount
      If PromLst(i) <> &quot;Promised&quot; Then PromisedFilter.AddItem PromLst(i)
   Next i
   Application.ScreenUpdating = False
   Sheets(&quot;seeds&quot;).Activate
   Application.ScreenUpdating = True
   PromisedFilter.DropDown
   MakingList = False
   Application.EnableEvents = True
End Sub

Rob

 

[vladk]

RobBroekhuis,

Replace PromisedFilter_gotfocus with PromisedFilter_DropButtonClick

Vladk

 

[RobBroekhuis]

Vladk,
I thought for sure I'd tried that at some point, but by golly it seems to get rid of the oddity. However, now I have a seemingly unrelated problem: when I actually make a selection from the combobox, it works properly but clears the selection, so that I'm looking at an empty box. This didn't used to be the case (the combobox would "hold" the selection), and I do rely on it being in place because it is printed in the next step. Any ideas?
The code that runs when the selection is made is:

Private Sub PromisedFilter_Change()
   If MakingList Then Exit Sub
   If PromisedFilter = "(all)" Then
      ActiveSheet.Shapes("btnTradeList").Visible = False
      On Error Resume Next
      ActiveSheet.ShowAllData
   Else
      Sheets("filter criteria").Range("A2") = "*" & PromisedFilter & "*"
      Range("SeedTable").AdvancedFilter xlFilterInPlace, _
                 Sheets("filter criteria").Range("A1:A2")
      ActiveSheet.Shapes("btnTradeList").Visible = True
   End If
   Range("H2").Activate
End Sub

Rob

 

[RobBroekhuis]

I just did some more debugging and found that this is most likely occurring because the dropbuttonclick event fires twice, once when the drop button is clicked, and again when a selection is made. This may be why I didn't use this event in the first place, but I don't remember any more. I can probably program around this, but would appreciate any extra insights...


Rob

 

[xlbo]

just use application.enableevents = false as the 1st line in the sub and then turn it back again after - as to why.....surely you know better Rob ;-)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[vladk]

RobBroekhuis

Would it help?

Private Sub PromisedFilter_DropButtonClick()
Dim PromLst(30) As String, PromCount As Integer, cell As Range, p As Integer
Dim s As String, s1 As String, i As Integer, nrow As Integer, LastRow As Integer
Static blnDoNotSuppressDropButtonClick As Boolean

If blnDoNotSuppressDropButtonClick = False Then
Application.EnableEvents = False
PromisedFilter.Clear
PromisedFilter.AddItem "(all)"
' ...code here to generate array PromLst from sheet contents...

For i = 1 To PromCount
If PromLst(i) <> &quot;Promised&quot; Then PromisedFilter.AddItem PromLst(i)
Next i
Application.ScreenUpdating = False
Sheets(&quot;Sheet1&quot.Activate
Application.ScreenUpdating = True
PromisedFilter.DropDown
Application.EnableEvents = True
End If

blnDoNotSuppressDropButtonClick = Not blnDoNotSuppressDropButtonClick
End Sub

Vladk

 

[RobBroekhuis]

Geoff,
Actually I tried that, and it didn't work - the second dropbuttonclick event fires AFTER the change event is complete.
Vladk,
I was thinking along the same lines, but noticed that I don't get the second dropbuttonclick event if I make the selection from the combobox using the keyboard - so I can't rely on it always firing twice.
I think I've got it fixed now - I set a SheetChanged flag using the worksheet_changed event, and only allow the code inside the dropbuttonclick event to run when the flag is true. I set the flag to false at the end of the event. Since there really is no need to regenerate the list when the worksheet hasn't changed, and the worksheet will never have changed between the two times the dropbuttonclick event fires, this seems to do the trick. Not pretty, but not as ugly as some things I've wrought


Rob

 

[vladk]

RobBroekhuis,

Could you put code that generates the list into some worksheet event, for example Activate? Generally, this is not the best solution trying to generate the list at the very time of the selection. It is much better to separate two different tasks: 1. Adding items, 2. Picking them up.

Vladk

 

[RobBroekhuis]

Vladk,
I agree in principle. I could get most of the way by using worksheet events (_change event), but that would miss a few instances in which the list needs to change. For example, I'm pretty sure that event doesn't fire when a row is deleted. I can give that some more thought if I run into more issues with my current solution, but right now it seems to work. The code that generates the list only takes a fraction of a second to run, so the process is transparent to the user.
Thanks again for your insights. Any ideas what the underlying cause for the strange appearance was?


Rob

 

[vladk]

RobBroekhuis,

I don't know what caused strange appearance (for some reason I can't recreate it anymore). But what I found, when I click on the little Spin-Control-like sub-control, the GotFocus event is not firing.

Vladk

 

[vladk]

RobBroekhuis,

It seems that the presense of then empty body:

Private Sub PromisedFilter_DropButtonClick()

End Sub

fixes the problem with your original code still being inside of _GotFocus event.

 

[RobBroekhuis]

That's interesting... I'll give that a try. At any rate, I wouldn't expect the gotfocus to fire when clicking inside the control, because for the &quot;spin control&quot; to even be visible, the combobox control must already have focus (otherwise the dropdown list would not be visible). The change event fires just fine, whether from the properly displayed list or from the odd one, so that the problem was really one of aesthetics rather than functionality. That's why I lived with it for as long as I have, because until recently I was the only user of this application.
Cheers




Rob