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

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

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

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

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

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