Deactivate sheet

[Jay Northrop]

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

 

[Jim Cone]

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


message 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

 

[Guest]

Private Sub Worksheet_Deactivate()
Me.Range("A1:A10").Sort Key1:=Range("A1"), Order1:=xlAscending
End Sub

Regards
Rowan

 

[Guest]

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]

Thanks, Jim! This works and I appreciate your help on this.

Jay

 

[Dave Peterson]

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

Private Sub Worksheet_Deactivate()
Me.Range("A1:A10").Sort Key1:=Range("A1"), Order1:=xlAscending
End Sub

Regards
Rowan

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