Excel vba - activate sheet from cell data [Solved/Closed]

Macronoob - Dec 8, 2011 at 12:41 PM - Latest reply:  Macronoob
- Dec 9, 2011 at 06:55 AM

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

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?????????

See more 

2 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Dec 9, 2011 at 06:08 AM
Thank you
your if block is not right

"$B$17" is what you meant and not "B17"

Thank you, rizvisa1 1

Something to say? Add comment

CCM has helped 1664 users this month

Thank you
OMG that was easy!!! That works great! Thanks a lot!