Trouble with Sheet Deactivate macro
 

Hi All......

I am trying to set up Worksheet Deactivate macro to
1-unprotect the sheet
2-clear contents of a specific range
3-hide the sheet

My present code:
Private Sub Worksheet_Deactivate()
ActiveSheet.Unprotect
Range("FieldCelleratorsX2").ClearContents
ActiveSheet.Visible = False
End Sub

It's unpredictable, sometimes it hides the sheet I'm GOING to, sometimes it
won't UNPROTECT.......the only part that seems to work consistantly is the
"clearcontents" line..........what an I doing wrong please?

TIA
Vaya con Dios,
Chuck, CABGx3

Trouble with Sheet Deactivate macro
 

Hi Chuck,

I think the problem is caused because in the deactoivate event the
activesheet is the sheet gone to, not from.

You could use the worksbook sheetdeactivate event, as that passes the exited
sh as an argument. So just test it's name

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Name = "Chuck" Then
Sh.Unprotect
Sh.Range("FieldCelleratorsX2").ClearContents
Sh.Visible = False
End If
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"CLR" wrote in message
...
Hi All......

I am trying to set up Worksheet Deactivate macro to
1-unprotect the sheet
2-clear contents of a specific range
3-hide the sheet

My present code:
Private Sub Worksheet_Deactivate()
ActiveSheet.Unprotect
Range("FieldCelleratorsX2").ClearContents
ActiveSheet.Visible = False
End Sub

It's unpredictable, sometimes it hides the sheet I'm GOING to, sometimes
it
won't UNPROTECT.......the only part that seems to work consistantly is the
"clearcontents" line..........what an I doing wrong please?

TIA
Vaya con Dios,
Chuck, CABGx3

Trouble with Sheet Deactivate macro
 

Thanks Bob.......

Do I simply replace my present macro behind the Sheet with this one, or do I
put it behind the Workbook.......I have 36 sheets I want to do this to and
was thinking I needed a macro behind each sheet......

Vaya con Dios,
Chuck, CABGx3

"Bob Phillips" wrote:

Hi Chuck,

I think the problem is caused because in the deactoivate event the
activesheet is the sheet gone to, not from.

You could use the worksbook sheetdeactivate event, as that passes the exited
sh as an argument. So just test it's name

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Name = "Chuck" Then
Sh.Unprotect
Sh.Range("FieldCelleratorsX2").ClearContents
Sh.Visible = False
End If
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"CLR" wrote in message
...
Hi All......

I am trying to set up Worksheet Deactivate macro to
1-unprotect the sheet
2-clear contents of a specific range
3-hide the sheet

My present code:
Private Sub Worksheet_Deactivate()
ActiveSheet.Unprotect
Range("FieldCelleratorsX2").ClearContents
ActiveSheet.Visible = False
End Sub

It's unpredictable, sometimes it hides the sheet I'm GOING to, sometimes
it
won't UNPROTECT.......the only part that seems to work consistantly is the
"clearcontents" line..........what an I doing wrong please?

TIA
Vaya con Dios,
Chuck, CABGx3

Trouble with Sheet Deactivate macro
 

No, remove your current code, and just put in in ThisWorkbook, and it is
workbook scope. You just check sh.name if it is to apply to any specific
sheets.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"CLR" wrote in message
...
Thanks Bob.......

Do I simply replace my present macro behind the Sheet with this one, or do
I
put it behind the Workbook.......I have 36 sheets I want to do this to and
was thinking I needed a macro behind each sheet......

Vaya con Dios,
Chuck, CABGx3

"Bob Phillips" wrote:

Hi Chuck,

I think the problem is caused because in the deactoivate event the
activesheet is the sheet gone to, not from.

You could use the worksbook sheetdeactivate event, as that passes the
exited
sh as an argument. So just test it's name

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Name = "Chuck" Then
Sh.Unprotect
Sh.Range("FieldCelleratorsX2").ClearContents
Sh.Visible = False
End If
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"CLR" wrote in message
...
Hi All......

I am trying to set up Worksheet Deactivate macro to
1-unprotect the sheet
2-clear contents of a specific range
3-hide the sheet

My present code:
Private Sub Worksheet_Deactivate()
ActiveSheet.Unprotect
Range("FieldCelleratorsX2").ClearContents
ActiveSheet.Visible = False
End Sub

It's unpredictable, sometimes it hides the sheet I'm GOING to,
sometimes
it
won't UNPROTECT.......the only part that seems to work consistantly is
the
"clearcontents" line..........what an I doing wrong please?

TIA
Vaya con Dios,
Chuck, CABGx3

Trouble with Sheet Deactivate macro
 

Okey doke.........thank you muchly

Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" wrote:

No, remove your current code, and just put in in ThisWorkbook, and it is
workbook scope. You just check sh.name if it is to apply to any specific
sheets.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"CLR" wrote in message
...
Thanks Bob.......

Do I simply replace my present macro behind the Sheet with this one, or do
I
put it behind the Workbook.......I have 36 sheets I want to do this to and
was thinking I needed a macro behind each sheet......

Vaya con Dios,
Chuck, CABGx3

"Bob Phillips" wrote:

Hi Chuck,

I think the problem is caused because in the deactoivate event the
activesheet is the sheet gone to, not from.

You could use the worksbook sheetdeactivate event, as that passes the
exited
sh as an argument. So just test it's name

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Name = "Chuck" Then
Sh.Unprotect
Sh.Range("FieldCelleratorsX2").ClearContents
Sh.Visible = False
End If
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"CLR" wrote in message
...
Hi All......

I am trying to set up Worksheet Deactivate macro to
1-unprotect the sheet
2-clear contents of a specific range
3-hide the sheet

My present code:
Private Sub Worksheet_Deactivate()
ActiveSheet.Unprotect
Range("FieldCelleratorsX2").ClearContents
ActiveSheet.Visible = False
End Sub

It's unpredictable, sometimes it hides the sheet I'm GOING to,
sometimes
it
won't UNPROTECT.......the only part that seems to work consistantly is
the
"clearcontents" line..........what an I doing wrong please?

TIA
Vaya con Dios,
Chuck, CABGx3