SheetSelectionChange

[Geoff]

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

 

[Bob Phillips]

Where are endrow and endcol calculated?

--
HTH

Bob Phillips

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

 

[Bob Phillips]

and startrow and startcol.

--
HTH

Bob Phillips

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

 

[Geoff]

In the SheetSelectionChange event like so:

If Not ActiveWorkbook.name = ThisWorkbook.name Or Not ActiveSheet.CodeName =
"Sheet2" Then Exit Sub

If Target.Count > 1 Or startRow = endRow Then Exit Sub

'''verify range and get dimensions
GetRange .......another module which gets range startrow, startcol etc

If Not Intersect(Target, etc etc

Geoff

 

[Geoff]

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]

Helps when we get all the facts!

Are those variables defined as Global public variables?

Why use application events when it only applies to one sheet?

--
HTH

Bob Phillips

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

 

[Geoff]

yes variables are declared globally.
the event does not work at all without doing it that way.

Geoff

 

[Geoff]

Taken out the class module and both redefined and repositioned the range
calculation.
Sorted now, thanks.

Geoff