ActiveX ComboBox dropdown flashes, can't get list to appear

[zeus]

Hello,
I've been having trouble with my ActiveX ComboBoxes on my excel spreadsheet. I am not using the ComboBox on a UserForm, it's just on my sheet. Here is the situation:

I have a ComboBox whose chosen value will trigger my VBA to either hide or unhide rows which contain other ComboBoxes. I quickly realized that ComboBoxes don't hide well when the row is hidden. I have tried making Visible = False then hiding the row. This works great, but when the row is unhid and I click on the dropdown in my previously hidden ComboBox, the dropdown just flashes. I can't get my ComboBox to display the contents of my dropdown. The only (unacceptable) workaround I found is to put the sheet in Design Mode, move the ComboBox, turn off Design Mode, click the dropdown...and now my list is there. This solution is not acceptable b/c my ComboBox is in the wrong spot and I certainly can't expect my users to do this in order to have a valid dropdown.

I appreciate any thoughts you might have as to what's going on and how to fix it.
Thank you kindly!

 

[Yongle]

Instead of hiding comboboxes move them (out of sight) instead
- when relevant rows are hidden move comboboxes to a safe haven BEFORE row is hidden
- when relevant rows are made visible move comboboxes to where you want them AFTER row is made visible
(they could also be hidden if you wanted)

In example below ComboBox1 moves to T1 if 4th attribute is FALSE and to A10 if TRUE

Place VBA in SHEET module

Sub CallIt()
    Call ControlComboBox(ComboBox1, Range("A10"), Range("T1"), [I][COLOR=#ff0000]False[/COLOR][/I])
End Sub

Private Sub ControlComboBox(CB As ComboBox, isVis As Range, nonVis As Range, Vis As Boolean)
    With CB
        If Vis = True Then
            .Left = isVis.Left
            .Top = isVis.Top
        Else
            .Left = nonVis.Left
            .Top = nonVis.Top
        End If
    End With
End Sub

 

[zeus]

I can see this being a useful option, my hesitation is that I'm building a form in Excel (not using UserForm) so when things need to be hidden, there are quite a few ComboBoxes to hide, or to move if using this option, then I need to move them all back to the exact spot they came from. Doable but more coding.

Is hiding a ComboBox then successfully unhiding it a limitation of Excel? Seems like a pretty normal, simple functionality requirement.

 

[Yongle]

Seems like a pretty normal, simple functionality requirement

- hmm!

There are LOTS of potential problems with active-x controls on the worksheet
If you google the subject you will quickly appreciate the scale of the problems that some users suffer

I'm building a form in Excel (not using UserForm)

Why are you not using the proper tool for the task?
Is there something that makes using a UserForm impractical?

 

[zeus]

oh boy. ActiveX problems might be the deal breaker for me. I don't have a great reason for not creating a UserForm. Part of it is my infant knowledge of UserForms, the bigger part was the directive to create a form ASAP. I'm not sure of the capabilities within UserForm - I'll need to hide certain parts of the form depending on the value of a particular ComboBox, have the sections reappear when other values from the ComboBox dropdown are selected, use named ranges to populate the drop down lists, create custom views (this is basically the same as being able to hide certain parts of the form as mentioned earlier) etc. My needs aren't incredibly complex. Perhaps I better start investigating this option instead. Thank for for your comment on ActiveX problems. Makes me sad, but at least I know that it's potentially something that requires a work around and that perhaps a pseudo form on an excel spreadsheet may not be the right option.