Go to worksheet that is specified by user in a drop down list

All

I am new to this forum and am hoping for help.

I want to be able to direct the user to a worksheet that is chosen by them from a drop down list.

I can provide the drop down list and I can attach a command button beside it so that the user chooses from the list then clicks the button to go to the desired worksheet.

Nothing I do allows me to do this?

your help will be appreciated

Welcome to the forum!

[VBA]Private Sub CommandButton1_Click()
Worksheets(ListBox1.Value).Select
End Sub[/VBA]

Quote:

---

Originally Posted by Kenneth Hobs

Welcome to the forum!

[vba]Private Sub CommandButton1_Click()
Worksheets(ListBox1.Value).Select
End Sub[/vba]

---

Thanks Kenneth

It is stopping at the ListBox1.Value?

Am I supposed to replace this with the name that I defined for the list or am i doing something else wrong?

thanks

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" And Range("B1").Text <> "" Then
Sheets(Range("B1").Text).Activate
End If
End Sub[/VBA]

In this case the dropdown is in cell "B1" and this code is in the Sheet1 module (change to suit)

Sorry CatDaddy

But I am a real beginner here how do I assign this bit of code to my command button so it will take the user to the worksheet that is returned from the drop down list at B1?

thanks

Hi.
See if this could help you:
Put the code below into a standard module then link it to a button, as you said
[vba]Sub ActivateSheet()
Dim sh As Worksheet
For Each sh In Worksheets
If sh.Name Like ActiveCell.Value Then
sh.Activate
Exit For
End If
Next sh
End Sub[/vba]
Other way, you may want to do the same job without using a button, so put the code bellow into the sheet module, this way:
1. copy the code from the board
2. right clique the sheet tab and choose 'View code'
3. paste the code into the blank panel that will open
4. done! 'Alt+Q' to go back to sheet and test
note - this code will be triggered as the user choose a sheet name from the drop down list; it will act only if drop down list is in column 3 (column C), change it to suit
[vba]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Or Target.Column <> 3 Then Exit Sub
Dim sh As Worksheet
For Each sh In Worksheets
If sh.Name Like Target.Value Then
sh.Activate
Exit For
End If
Next sh
End Sub[/vba]

Thank you all this has been a great first experience in this forum instant replies and all has ended well the code works and will do the job that I needed to do

appreciate your assistance

chris

Excel contains a builtin facility:

Right-click in the sheetbrowser part of the Excel window.

Hi all

I would like to be able to provide a relative term in my code that replaces the need for an exact Filepath that has to be changed each time I change the name of the file.

The code below is what I need to change and the bit coloured red and copied here is the filepath that I would like to have code going directly to the new filename rather than me having to physically change it in all the sheets each time I change the filename.


Thank you

Private Sub CommandButton2_Click()
' mcrfltroff11 Macro
' Macro recorded 8/08/2012 by cper1804
'
'
Application.Run "'December-12 PPH Branch Financial Report.xls'!mcrfilteroff10"
Range("C13").Select
Dim findWhat As String
findWhat = Sheets("AP QUERY SHEET").Range("C13") ' get whatever
' Sheets("AP by Unit").Select
Range("A2").Select
Application.Goto Reference:=Worksheets("AP by Unit").Range("A2"), _
Scroll:=True
Selection.AutoFilter
ActiveSheet.Range("$A$2:$B$4").AutoFilter Field:=1, _
Criteria1:=Sheets("AP by Unit").Range("A1")
End Sub