Excel vba - activate sheet from cell data

Solved/Closed
Macronoob - Dec 8, 2011 at 12:41 PM
 Macronoob - Dec 9, 2011 at 06:55 AM
Hello,

First of all, thanks to all of you that answer these questions. You have no idea how much I use these sites to learn code for my macros.

My workbook has about 300 sheets. I have a summary sheet that I have all my macro buttons on. It's my "Start" sheet. On the "Start tab" Sheet, I would like to enter into a cell the name of one of the sheets and when I hit enter, it will take me to the sheet. Is that possible to do? I have been messing around with this and this is what I have so far...

1. I right clicked on the Start tab and selected View Code. Then I put in the actual code there.

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "B17" Then

Call Go_To_Tab

End If

End Sub

2. I then created the Macro Go_To_Tab which has the following code...

Sub Go_To_Tab()


Dim strWsName As String

strWsName = Sheet1.Range("B17")
Sheets(strWsName).Select


End Sub


When I go back to the Start tab and click in the cell B17 and type in the name of a sheet and hit enter, nothing happens. when I run the G_To_Tab macro by itself, it works fine. What am I doing wrong?????????




2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 9, 2011 at 06:08 AM
your if block is not right

"$B$17" is what you meant and not "B17"
1
OMG that was easy!!! That works great! Thanks a lot!
0