Workbook_SheetSelectionChange
 

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. .

Workbook_SheetSelectionChange
 

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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"R.VENKATARAMAN" $$$ wrote in message
...
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. .

Workbook_SheetSelectionChange
 

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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"R.VENKATARAMAN" $$$ wrote in message ...
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. .

Workbook_SheetSelectionChange
 

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.

Ron de Bruin wrote in message
...
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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"R.VENKATARAMAN" $$$ wrote in message
...
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. .