To clarify:
The full SheetSelectionChange module is :

Private Sub oApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

'''ensure goto is from Sheet2 of this wbook only
If Not ActiveWorkbook.name = ThisWorkbook.name Or Not ActiveSheet.CodeName =
"Sheet2" Then Exit Sub

'''exit if a multiple selection is made
If Target.Count 1 Then Exit Sub

'''verify range and get dimensions
GetRange

'''ensure target is within current data range
If Not Intersect(Target, Range(Cells(startRow, startCol), Cells(endRow,
endCol))) Is Nothing Then
Sheets(Target.Row ).Activate
End If

End Sub

the relevant part of GetRange:
Sub GetRange()
With Sheets(2)
'''determine range dimensions
startRow = .Cells.Find("Ref", .Range("A1"), , xlWhole).Row
startCol = .Cells.Find("Ref", .Range("A1"), , xlWhole).Column
endRow = .Cells(65536, startCol).End(xlUp).Row
endCol = .Cells(startRow, 256).End(xlToLeft).Column
End With
End Sub

"Bob Phillips" wrote:

and startrow and startcol.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Geoff" wrote in message
...
I am having difficulty using the SheetSelectionChange event which refers
to a
dynamic range.
The range dimension is increased or reduced by adding or deleting wsheets.
Selecting a range.row. activates the corresponding wsheet.
If I increase the range by adding new wsheets the event always fires
correctly.
If I reduce the range by deleting a wsheet the event does not fire again
even when I add another wsheet.

Mysteriously I had it working properly without having to save and re-open
the wbook but I cannot repeat this.

I would appreciate any guidance on this please. T.I.A.

Geoff

This code is in a class module:
Dim WithEvents oApp As Application

Private Sub Class_Initialize()
Set oApp = Application
End Sub

Private Sub oApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)
If Not Intersect(Target, Range(Cells(startRow , startCol),
Cells(endRow,
endCol))) Is Nothing Then
Sheets(Target.Row).Activate
End If
End Sub

This code is in the ThisWorkBook module:
Dim oAppEvents As CAppEvents

Private Sub Workbook_Open()
Set oAppEvents = New CAppEvents
End Sub