Listbox vs. Dropdown

[ryland00]

Hello,

I am wondering if anyone can help. I am looking for a way to control which tabs are able to be unhidden based on a list. So basically, something very similar to a list box, but not a list box.. Let me explain

I have tried using a list box, but it just doesn't seem sustainable. While at first, it worked exactly for what I wanted. I found some VBA code that allowed exactly what I was looking for, the user could put a check mark next to the tab name and those tabs were un-hidden. The problem with the list box is it doesn't seem to work well with Excel. Every time I go away from the tab with the list box and come back to it, the font is smaller or the box is larger.

Hope you are able to help!


Thanks much,

Ryan

 

[AKR22]

Is this listbox a form control or an ActiveX control? Do you mind posting the code you are using for the lisbox?

Cheers

 

[ryland00]

Is this listbox a form control or an ActiveX control? Do you mind posting the code you are using for the lisbox?

Cheers

I believe ActiveX Control and yes, please see below. The box issues are just too much for me to want to continue to use.

Private Sub Worksheet_Activate()

ListBox1.MultiSelect = fmMultiSelectMulti
ListBox1.List = Array("GLOBAL", "APJC", "BAM", "EMEAR", "INDIA", "LATAM", "USC", "LATAMSC")


End Sub




Private Sub ListBox1_Change()


For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) = True Then
        Sheets(ListBox1.List(i)).Visible = True
        Else
        Sheets(ListBox1.List(i)).Visible = False
    End If
Next i


End Sub

 

[humdingaling]

Setting the IntegralHeight to False stopped the resizing

 

[ryland00]

Setting the IntegralHeight to False stopped the resizing

I did actually have this option as False already, because I read the same thing. But that is when the font would shrink each time I left the tab, to the point where the user could no longer see the choices.

 

[humdingaling]

sorry i thought the listbox itself was shrinking

if only the font is shrinking
maybe just add line in code to force it to the size what you everytime

Private Sub Worksheet_Activate()

ListBox1.MultiSelect = fmMultiSelectMulti
ListBox1.List = Array("GLOBAL", "APJC", "BAM", "EMEAR", "INDIA", "LATAM", "USC", "LATAMSC")
ListBox1.Font.Size = 12

End Sub