Open Tab with cell selection.

[craigwojo]

Hi everyone,
Is there a way to have a tab, let's say, "VINYL" open up when you click on a cell, let's say, "E27"?

Thank you and God bless,
Craig

 

[kvsrinivasamurthy]

Use worksheet event code below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("E27")) Is Nothing Then
Sheets("VINYL").Activate
End If
End Sub

How to use worksheet event the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file as .xlsm

 

[Accelerate Excel]

Use worksheet event code below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("E27")) Is Nothing Then
Sheets("VINYL").Activate
End If
End Sub

How to use worksheet event the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file as .xlsm

I agree with the solution, maybe I would also add:

To open it in a new window:
Dim newWindow As Window
Set newWindow = ThisWorkbook.NewWindow
newWindow.Activate
ThisWorkbook.Sheets("VINYL").Activate


Add an error handler:

On Error Resume Next
(Rest of code)
On Error GoTo 0

 

[craigwojo]

Use worksheet event code below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("E27")) Is Nothing Then
Sheets("VINYL").Activate
End If
End Sub

How to use worksheet event the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file as .xlsmThe TA

The cell "K27" that needs to be selected is on the TAB called "TITLE" and would open the TAB called "METAL" and open up to the top of the page on METAL TAB. Sorry about not saying what the actual worksheet data is.
Thank you again.

 

[craigwojo]

Any recommendations?
Thak you.

 

[Joe4]

The cell "K27" that needs to be selected is on the TAB called "TITLE" and would open the TAB called "METAL" and open up to the top of the page on METAL TAB. Sorry about not saying what the actual worksheet data is.

Just modify the code given to you to match those minor changes (change range name and sheet name), and then select the first cell on that Sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("K27")) Is Nothing Then
        Sheets("METAL").Activate
        Range("A1").Select
    End If
End Sub

In order to get this to run against the "TITLE" tab, this code MUST be placed in the "TITLE" worksheet module in VBA.
"Worksheet_SelectionChange" event procedure code ALWAYS goes in Sheet modules in VBA, never in the "ThisWorkbook" or General modules.
An easy way to make sure you put it in the correct place is to go to the "TITLE" sheet, right-click on the Sheet tab name at the bottom of the screen, select "View Code", and then paste this code in the VB Editor window that pops up.

 

[craigwojo]

Hi everyone,
Thank you for your help. I ended up HYPERLINKing the cell to the tab. It works.

Thak you and God bless,
Craig