Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|