Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deactivate sheet
Hello!
I'm having trouble with Deactivate Sheet. My workbook has two sheets, Sheet1 and Sheet2. In Sheet1, I have this macro: Private Sub Worksheet_Deactivate() Worksheets("Sheet1").Activate Range("A1:A10").Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending End Sub However, when I click on Sheet2, Sheet1 won't "let go." I suppose it's because of the third line [Range("A1:A10").Select]. but, without that line, I get an error message. How do I get the macro to "let go" of sheet 1 and go to sheet 2 when I click on Sheet2? Also, I tested another similar Worksheet_Deactivate macro (not using a sort routine) which worked okay, but it left a selection "shadow" on Sheet2. Your help is appreciated. Thanks, Jay |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deactivate sheet
Jay,
'------------------- Private Sub Worksheet_Deactivate() 'Sheet1 With Worksheets("Sheet1") .Range("A1:A10").Sort Key1:=.Range("A1"), _ Order1:=xlAscending End With End Sub '------------------- Regards, Jim Cone San Francisco, USA "Jay Northrop" wrote in message news:HoZDe.46061$4o.26539@fed1read06... Hello! I'm having trouble with Deactivate Sheet. My workbook has two sheets, Sheet1 and Sheet2. In Sheet1, I have this macro: Private Sub Worksheet_Deactivate() Worksheets("Sheet1").Activate Range("A1:A10").Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending End Sub However, when I click on Sheet2, Sheet1 won't "let go." I suppose it's because of the third line [Range("A1:A10").Select]. but, without that line, I get an error message. How do I get the macro to "let go" of sheet 1 and go to sheet 2 when I click on Sheet2? Also, I tested another similar Worksheet_Deactivate macro (not using a sort routine) which worked okay, but it left a selection "shadow" on Sheet2. Your help is appreciated. Thanks, Jay |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deactivate sheet
Private Sub Worksheet_Deactivate()
Me.Range("A1:A10").Sort Key1:=Range("A1"), Order1:=xlAscending End Sub Regards Rowan "Jay Northrop" wrote: Hello! I'm having trouble with Deactivate Sheet. My workbook has two sheets, Sheet1 and Sheet2. In Sheet1, I have this macro: Private Sub Worksheet_Deactivate() Worksheets("Sheet1").Activate Range("A1:A10").Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending End Sub However, when I click on Sheet2, Sheet1 won't "let go." I suppose it's because of the third line [Range("A1:A10").Select]. but, without that line, I get an error message. How do I get the macro to "let go" of sheet 1 and go to sheet 2 when I click on Sheet2? Also, I tested another similar Worksheet_Deactivate macro (not using a sort routine) which worked okay, but it left a selection "shadow" on Sheet2. Your help is appreciated. Thanks, Jay |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deactivate sheet
I see Jim gives the same answer as me...
Private Sub Worksheet_Deactivate() With Worksheets("Sheet1").Range("A1:A10") .Sort Key1:=Range("A1"), Order1:=xlAscending End With End Sub The key here is to remember that cells or ranges don't have to be active or selected .... they don't even have to be on an active sheet, to be manipulated in most ways. "Jay Northrop" wrote: Hello! I'm having trouble with Deactivate Sheet. My workbook has two sheets, Sheet1 and Sheet2. In Sheet1, I have this macro: Private Sub Worksheet_Deactivate() Worksheets("Sheet1").Activate Range("A1:A10").Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending End Sub However, when I click on Sheet2, Sheet1 won't "let go." I suppose it's because of the third line [Range("A1:A10").Select]. but, without that line, I get an error message. How do I get the macro to "let go" of sheet 1 and go to sheet 2 when I click on Sheet2? Also, I tested another similar Worksheet_Deactivate macro (not using a sort routine) which worked okay, but it left a selection "shadow" on Sheet2. Your help is appreciated. Thanks, Jay |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deactivate sheet
Thanks, Jim! This works and I appreciate your help on this.
Jay "Jim Cone" wrote in message ... Jay, '------------------- Private Sub Worksheet_Deactivate() 'Sheet1 With Worksheets("Sheet1") .Range("A1:A10").Sort Key1:=.Range("A1"), _ Order1:=xlAscending End With End Sub '------------------- Regards, Jim Cone San Francisco, USA "Jay Northrop" wrote in message news:HoZDe.46061$4o.26539@fed1read06... Hello! I'm having trouble with Deactivate Sheet. My workbook has two sheets, Sheet1 and Sheet2. In Sheet1, I have this macro: Private Sub Worksheet_Deactivate() Worksheets("Sheet1").Activate Range("A1:A10").Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending End Sub However, when I click on Sheet2, Sheet1 won't "let go." I suppose it's because of the third line [Range("A1:A10").Select]. but, without that line, I get an error message. How do I get the macro to "let go" of sheet 1 and go to sheet 2 when I click on Sheet2? Also, I tested another similar Worksheet_Deactivate macro (not using a sort routine) which worked okay, but it left a selection "shadow" on Sheet2. Your help is appreciated. Thanks, Jay |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deactivate sheet
I like using the Me keyword, too. But I'd add it to the the key, too.
Private Sub Worksheet_Deactivate() Me.Range("A1:A10").Sort Key1:=me.Range("A1"), Order1:=xlAscending End Sub Rowan wrote: Private Sub Worksheet_Deactivate() Me.Range("A1:A10").Sort Key1:=Range("A1"), Order1:=xlAscending End Sub Regards Rowan "Jay Northrop" wrote: Hello! I'm having trouble with Deactivate Sheet. My workbook has two sheets, Sheet1 and Sheet2. In Sheet1, I have this macro: Private Sub Worksheet_Deactivate() Worksheets("Sheet1").Activate Range("A1:A10").Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending End Sub However, when I click on Sheet2, Sheet1 won't "let go." I suppose it's because of the third line [Range("A1:A10").Select]. but, without that line, I get an error message. How do I get the macro to "let go" of sheet 1 and go to sheet 2 when I click on Sheet2? Also, I tested another similar Worksheet_Deactivate macro (not using a sort routine) which worked okay, but it left a selection "shadow" on Sheet2. Your help is appreciated. Thanks, Jay -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deactivate worksheet | Excel Discussion (Misc queries) | |||
Deactivate a cell | Excel Programming | |||
deactivate fullscreen | Excel Programming | |||
Sort sheet on sheet's Deactivate event | Excel Programming | |||
Deactivate Formula | Excel Programming |