Workbook_SheetSelectionChange

[R.VENKATARAMAN]

in a programme visualised by me I make a selection change (in the sense I
activate a cell) in a particular sheet and fire some code. this is ok. but
subequently in the same programme I want to activate another sheet
(sh.name changes) and a cell in that sheet is activated and some other code
is fired. is it possible ?


a trivial example is given below
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
'i activated sheet2 and activate a cell in that sheet
If Sh.Name = "sheet1" Then GoTo line1
If Sh.Name = "sheet2" Then GoTo line2
line1:
Range("a1") = 12345
GoTo line3
line2:
Range("a1") = 4567
line3:
End Sub

when i activate the sheet2 makes selection change in that sheet and thus
fire the code it does not go to line 2 but only goes to line1. in both the
sheets range("a1") is entered by 12345 only. perhaps I have not understood
this event procedure. .

 

[Rob van Gelder]

Could be a matter of Upper vs. Lower case comparison. Sheet2 vs sheet2


By the way, GoTo statements are bad news...

Here are some other ways to do the same:

Private Sub Workbook_SsheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
If StrComp(Sh.Name, "sheet1", vbTextCompare) = 0 Then
Range("A1").Value = 12345
ElseIf StrComp(Sh.Name, "sheet2", vbTextCompare) = 0 Then
Range("A1").Value = 4567
Else
Range("A1").Value = 54321
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Select Case LCase(Sh.Name)
Case "sheet1"
Range("A1").Value = 12345
Case "sheet2"
Range("A1").Value = 4567
Case Else
Range("A1").Value = 54321
End Select
End Sub

 

[Ron de Bruin]

Hi

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Sheet1" Then sh.Range("a1") = 12345
If Sh.Name = "Sheet2" Then sh.Range("a1") = 4567
End Sub

But every sheet have also his own SelectionChange event in the sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

 

[R.VENKATARAMAN]

thanks to both of you Mr. Ron de Bruin and Mr. Rob van gelder. I learnt a
lot from you MVPs. I never thought that the lower and upper case difference
in the first letter of Sheet will give me this problem.

I agree <goto> is not a very happy expression. but I have to use it. It is
a little complicated requirement. I have final appear to have succeded in
preparing the programme taking into consideration all the possible choices.
i have to give it to my brother for practical test.

thatk you two once again.