Clicking a cell that takes you to another sheet

[Prattlesnake]

Is it possible to click a cell, anywhere in the cell, and have it take you to a different sheet within the workbook?


I don't want to use a hyperlink because that will only work when you click the text and I don't want to use shapes (though shapes do accomplish this goal) because they're a little time consuming to use (aligning, sizing takes time, primarily).


Is this an Excel VBA/Macro type thing? I apologize if this is posted in the wrong forum.


-P

 

[DaveTurton]

By VBA:


paste this into the code of the sheet which contains the cell you are going to use. (Right click sheet tab and select view code)

[pre]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Row = 1 And Target.Column = 1 Then

Sheets("sheet2").Select

End If

End Sub

[/pre]

at the moment it is set to use A1 (row 1, column 1), change for what you need. It will take you to "Sheet2", change the name as required.

 

[Prattlesnake]

Thank you, DaveTurton, that works.


Is there a way to do this with merged cells, if the cells A1:C3 were merged and I wanted that block to be a button?

 

[shrivallabha]

Like this then:

[pre]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1:$C$3" Then
Sheets("Sheet2").Select
End If
End Sub

[/pre]

 

[Prattlesnake]

Thank's, shrivallaba!


If I wanted to do this for multiple ranges, in effect creating a series of buttons that would navigate to individual sheets in the workbook, how would I do that?

 

[bobhc]

Good day Prattlesnake


If you assign a macro to each button using shrivallabha's code then just alter the cell range references in the button macro

 

[SirJB7]

@b(ut)ob(ut)hc

Hi, old dog!

Good afternoon.

As an old friend said: everyone can navigate the way he prefers... and then the captain of the submarine ordered to unfold the sail.

Regards!

 

[shrivallabha]

If there are 'cases' then something like this [untested but should be enough to get you rolling]

[pre]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case "$A$1:$C$3"
Sheets("Sheet2").Select
Case "$A$4:$C$6"
Sheets("Sheet3").Select
'and the story goes on....
End Select
End Sub

[/pre]

 

[SirJB7]

@shrivallabha

Hi!

An undercover menu.

Regards!

 

[bobhc]

Good evening SirJB7 how is your eye now fully recovered, you seemed to have no difficulty at the demo


"and then the captain of the submarine ordered to unfold the sail." nothing wrong with a sail on a sub, just causes a bit of drag on dive.

 

[SirJB7]

@b(ut)ob(ut)hc

Hi, my friend!

Good afternoon.

If that submarine is yellow maybe he'd take me to St Marteen or Aruba or...

Regards!