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

Report
-
 Macronoob -
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 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
765
your if block is not right

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

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!