List Box: How do you enable link?

[highndry]

Hi

I have created this list box where I have around 10 tab (worksheet) names (AFD, AFS, CB&T, etc.). How can I redirect to those tab when someone select a name. I can use hyperlink but then I would have to type in all the tab name and it will take up too much space.

Thanks

 

[Norie]

What exactly do you want to do?

Is this listbox to allow the user to select a worksheet?

 

[highndry]

Hi Norie

Yes, the file opens up with a summary page and on top in cel A3 I have "Cost Centers: List Box" where they will select the cost center and it will take them to that page.

 

[Smitty]

How about something like this:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ListBox1_Change()
    Sheets(ListBox1.Value).Select
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

HTH,

Smitty

 

[highndry]

Nothing happens with the above macro, I cannot even assign that macro to the list box.

 

[DominicB]

Good evening highndry

How have you created the listbox?

DominicB

 

[highndry]

Yes I have created the list box with all the worksheet names, the worksheet names are in a another sheet which is linked to the listbox. However, when I select the worksheet names ex "AFD" it doesn't take me to that worksheet.

 

[DominicB]

Hi highndry

Did you create the listbox from the Forms toolbar or the Control Toolbox toolbar?

DominicB

 

[Smitty]

What Dominic is referring to is did you create the ListBox from the Forms toolbar or the Control Toolbox?

The code I posted is for an ActiveX control from the Control Toolbox. The code needs to go in teh sheet specific module.

Smitty

 

[highndry]

It is in the sheet specific module, I have the following in the specific sheet:

'Private Sub ListBox1_Click()
Private Sub ListBox1_Change()
    Sheets(ListBox1.Value).Select
End Sub

Private Sub ComboBox1_Change()

End Sub

Private Sub ComboBox1_DropButtonClick()

End Sub

Private Sub ListBox1_Change()
    Sheets(ListBox1.Value).Select
End Sub