Sheet Select Macro
 

I have the folowing macro that works on one of my excel workbooks.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
If Not Intersect(Target, Range("B3:B52")) Is Nothing Then
On Error Resume Next
Set ws = Worksheets(Target.Row)
If ws Is Nothing Then
MsgBox "Sorry, but there is no worksheet named " & Target.Row
Else
ws.Activate
End If
End If
End Sub

This selects the sheet that corresponds to the cell. but I have cells in
J3:J52 as well but cant select the sheets that correspond to those can anyone
help?

Regards

Sheet Select Macro
 

Try
If Not Intersect(Target, Range("B3:B52,j3:j52")) Is Nothing Then


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"santaviga" wrote in message
...
I have the folowing macro that works on one of my excel workbooks.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
If Not Intersect(Target, Range("B3:B52")) Is Nothing Then
On Error Resume Next
Set ws = Worksheets(Target.Row)
If ws Is Nothing Then
MsgBox "Sorry, but there is no worksheet named " & Target.Row
Else
ws.Activate
End If
End If
End Sub

This selects the sheet that corresponds to the cell. but I have cells in
J3:J52 as well but cant select the sheets that correspond to those can
anyone
help?

Regards

Sheet Select Macro
 

Hi,

I don't understand. because you are using target.row if you select B3 it
will look for a sheet called 3 so if you extend this range as below then
selecting J3 will do exactly the same as selecting B3. If you have sheet
names in these cells perhaps you should be using

Set ws = Worksheets(Target.Text)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
If Not Intersect(Target, Range("B3:B52, J3:J52")) Is Nothing Then
On Error Resume Next
Set ws = Worksheets(Target.Row)
If ws Is Nothing Then
MsgBox "Sorry, but there is no worksheet named " & Target.Row
Else
ws.Activate
End If
End If
End Sub

Mike

"santaviga" wrote:

I have the folowing macro that works on one of my excel workbooks.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
If Not Intersect(Target, Range("B3:B52")) Is Nothing Then
On Error Resume Next
Set ws = Worksheets(Target.Row)
If ws Is Nothing Then
MsgBox "Sorry, but there is no worksheet named " & Target.Row
Else
ws.Activate
End If
End If
End Sub

This selects the sheet that corresponds to the cell. but I have cells in
J3:J52 as well but cant select the sheets that correspond to those can anyone
help?

Regards

Sheet Select Macro
 

This is what I have and need to do.

Cells B3:B52 (once clicked on one of these cells select the corresponding
sheet number)
Cells J3:J52 the same sheet numbers 50 to 100
Cells R3:R52 sheet numbers 100 to 150

So when I click on R3 this will open sheet number 100 and so on for all the
cells in B, J and R

Thanks

"Mike H" wrote:

Hi,

I don't understand. because you are using target.row if you select B3 it
will look for a sheet called 3 so if you extend this range as below then
selecting J3 will do exactly the same as selecting B3. If you have sheet
names in these cells perhaps you should be using

Set ws = Worksheets(Target.Text)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
If Not Intersect(Target, Range("B3:B52, J3:J52")) Is Nothing Then
On Error Resume Next
Set ws = Worksheets(Target.Row)
If ws Is Nothing Then
MsgBox "Sorry, but there is no worksheet named " & Target.Row
Else
ws.Activate
End If
End If
End Sub

Mike

"santaviga" wrote:

I have the folowing macro that works on one of my excel workbooks.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
If Not Intersect(Target, Range("B3:B52")) Is Nothing Then
On Error Resume Next
Set ws = Worksheets(Target.Row)
If ws Is Nothing Then
MsgBox "Sorry, but there is no worksheet named " & Target.Row
Else
ws.Activate
End If
End If
End Sub

This selects the sheet that corresponds to the cell. but I have cells in
J3:J52 as well but cant select the sheets that correspond to those can anyone
help?

Regards