Macro (attached to ActiveX List Box) running on open?

[Sarah52181]

I'm afraid I may be in a bit over my head here. I have a spreadsheet with multiple brands on it, and I've added an ActiveX listbox that allows you to select which brands you want to view (Brand A, Brand B, or "all brands"). It works great, however it appears that my macros are running on workbook open.

None of my macros (and there are several) have the "Workbook_Open" code in them, so I'm not sure what's going on?

It really doesn't do any harm, except that someone opening the workbook has to watch excel flash and blink and go through all of these steps/calculations evertime you open it.

Has anyone seen this before?

I thought Excel might be treating the "workbook open" as me making a selection on one of my listboxes (even if it was already selected) and thus running that macro, but the same thing happens even if I save the workook to display a sheet that doesn't have a listbox.

ETA: From what I've read, activeX controls are always autoloaded when a workbook containing them opens. That's unfortunate. Am I out of luck here for a speedy workbook open?

 

[vaskov17]

add a form control list box, instead of the activeX one

 

[Sarah52181]

Thanks! I started with a form control, but I couldn't figure out how to assign a different macro to each selection contained within the listbox. It was relatively easy with an activeX control.

Is there a way to do the same thing for a form control?

The code I used for ActiveX was:

Private Sub ListBox1_select()
Select Case ListBox1.ListIndex
Case 0: targetunhideall
Case 1: targetm
Case 2: targeth
End Select
End Sub

 

[Sarah52181]

ooooo - I could just use (form control) option buttons and assign a macro to each button. With a little clever formatting it will ultimately look just like the activeX listbox I started with only it won't take forever to open. Thanks!

 

[RoryA]

Similar code for the macro:

Private Sub ListBox1_select()
Dim lb as Listbox
Set lb = activesheet.listboxes(application.caller)
Select Case lb.ListIndex
Case 0: targetunhideall
Case 1: targetm
Case 2: targeth
End Select
End Sub

 

[vaskov17]

one way to get selection from a form control list box:

Sub example()
    Dim ws As Worksheet
    Dim lbName As String
    
    Set ws = ActiveSheet
    lbName = "List Box 1"
    
    MsgBox ws.Shapes(lbName).ControlFormat.List(ws.Shapes(lbName).ControlFormat.ListIndex)
End Sub

Assign the above code to the list box and it will display a message whenever you click on "List Box 1", which is the default name for the first list box added to the sheet.

Instead of the message box you can assign it to a variable and use it in your other code.

 

[Sarah52181]

Thanks guys! Great suggestions AND it's prompted me to come up with my own (at least temporarily) acceptable solution, which is also pretty great.