How do I pass the contents of the “Target” range i

Question

Sheet 1 of my workbook contains (besides other data) a list of the other worksheets in column A. I wish to be able to click on any cell in column A5:A50 and go to the appropriate worksheet listed in that cell. My Sheet1 code is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A5:A50")) Is Nothing Then SelectWorksheet
End Sub

and Module2 is:

Sub SelectWorksheet()
    Dim strName As String
    strName = Sheet1.Range("Target").Text (Error occurrs here: "Method 'Range' of object 'Worksheet' failed")
    Sheets(strName).Select
End Sub

How do I get this to work as I expect? I know I could just click on the appropriate worksheet tab but I'm trying to learn how to code in VBA. Thanks. By the way, how do I get my post to show the code as typed in the question box?

Answer 1

Why not pass the sheet name from the cell to the sub?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A5:A50")) Is Nothing _
        And Target.Cells.Count = 1 Then SelectWorksheet (Target.Value)
End Sub

Sub SelectWorksheet(strName As String)
    Sheets(strName).Select
End Sub

I've also done a check to make sure that only one cell is in the selection.

Answer 2

Like this. You probably want to use the _SelectionChange event instead of the _Change event. Or you may find it necessary to use both events to trigger it. In any case here is how you pass the variable to another subroutine/module:

Sub Worksheet_SelectionChange(byVal Target as Range)
'Some code...
'
Call OtherMacro(Target) 
'
End Sub

And then in your other macro, declare a range variable as required argument, like so:

Sub SelectWorksheet(rng as Range)
'
Dim strName as String

' at this point you can work with the "rng" variable, because it's been received from the other subroutine    
strName = rng.Value
Sheets(strName).Activate
'
End Sub

You would need to add additional test to make sure user has not selected multiple cells, etc., but this should get you started.