List Box: How do you enable link?

highndry

Board Regular
Joined
Nov 28, 2005
Messages
247
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
What exactly do you want to do?

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

highndry

Board Regular
Joined
Nov 28, 2005
Messages
247
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

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Nov 28, 2005
Messages
247

ADVERTISEMENT

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

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Good evening highndry

How have you created the listbox?

DominicB
 

highndry

Board Regular
Joined
Nov 28, 2005
Messages
247

ADVERTISEMENT

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

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Hi highndry

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

DominicB
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Nov 28, 2005
Messages
247
It is in the sheet specific module, I have the following in the specific sheet:

Code:
'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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,059
Messages
5,545,759
Members
410,704
Latest member
Cobber2008
Top