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

[ceepee]

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

[Kenneth Hobs]

Welcome to the forum!

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

[ceepee]

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

[CatDaddy]

[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)

[ceepee]

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

[omp001]

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]

[ceepee]

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

[snb]

Excel contains a builtin facility:

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

[ceepee]

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