Make Worksheet Deactivate Event only run once

[robzrob]

I have the code below, which runs when I deactivate a worksheet, but I
only want it to run the first time the worksheet is deactivated (in
any one session of using the workbook). If it’s activated and then
deactivated again (in any one session of using the workbook), I want
it not to run.

Private Sub Worksheet_Deactivate()
Counterstation = Workbooks("h...).Range("S38").Value
Select Case Counterstation
Case "CAM1": Do A
Case "CAM2": Do B
Case "CAM3": Do C
Case "CAM4": Do D
Case "HEL1": Do E
Case "HEL2": Do F
End Select
End Sub


I’ve tried this:

Private Sub Worksheet_Deactivate()
If x > 0 Then Exit Sub
Counterstation = Workbooks("h...).Range("S38").Value
Select Case Counterstation
Case "CAM1": Do A
Case "CAM2": Do B
Case "CAM3": Do C
Case "CAM4": Do D
Case "HEL1": Do E
Case "HEL2": Do F
End Select
x=1
End Sub

(with x set as 0 in another piece of code which runs 'on open'), but
it won’t work.

 

[Don Guillett]

You could have workbook_open put 0 in a cell somewhere>run the code and have
it put

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
I have the code below, which runs when I deactivate a worksheet, but I
only want it to run the first time the worksheet is deactivated (in
any one session of using the workbook). If it�s activated and then
deactivated again (in any one session of using the workbook), I want
it not to run.

Private Sub Worksheet_Deactivate()
Counterstation = Workbooks("h...).Range("S38").Value
Select Case Counterstation
Case "CAM1": Do A
Case "CAM2": Do B
Case "CAM3": Do C
Case "CAM4": Do D
Case "HEL1": Do E
Case "HEL2": Do F
End Select
End Sub


I�ve tried this:

Private Sub Worksheet_Deactivate()
If x > 0 Then Exit Sub
Counterstation = Workbooks("h...).Range("S38").Value
Select Case Counterstation
Case "CAM1": Do A
Case "CAM2": Do B
Case "CAM3": Do C
Case "CAM4": Do D
Case "HEL1": Do E
Case "HEL2": Do F
End Select
x=1
End Sub

(with x set as 0 in another piece of code which runs 'on open'), but
it won�t work.

 

[Bob Phillips]

TRy

Private Sub Worksheet_Deactivate()
Static ReRun As Boolean

If Not ReRun Then

Counterstation = Workbooks("h...").Range("S38").Value
Select Case Counterstation
Case "CAM1": Do A
Case "CAM2": Do B
Case "CAM3": Do C
Case "CAM4": Do D
Case "HEL1": Do E
Case "HEL2": Do F
End Select

ReRun = True
End If
End Sub



--

HTH

Bob

I have the code below, which runs when I deactivate a worksheet, but I
only want it to run the first time the worksheet is deactivated (in
any one session of using the workbook). If it’s activated and then
deactivated again (in any one session of using the workbook), I want
it not to run.

Private Sub Worksheet_Deactivate()
Counterstation = Workbooks("h...).Range("S38").Value
Select Case Counterstation
Case "CAM1": Do A
Case "CAM2": Do B
Case "CAM3": Do C
Case "CAM4": Do D
Case "HEL1": Do E
Case "HEL2": Do F
End Select
End Sub


I’ve tried this:

Private Sub Worksheet_Deactivate()
If x > 0 Then Exit Sub
Counterstation = Workbooks("h...).Range("S38").Value
Select Case Counterstation
Case "CAM1": Do A
Case "CAM2": Do B
Case "CAM3": Do C
Case "CAM4": Do D
Case "HEL1": Do E
Case "HEL2": Do F
End Select
x=1
End Sub

(with x set as 0 in another piece of code which runs 'on open'), but
it won’t work.

 

[broro183]

hi,

If x is declared within the "open" sub it will only hold its value for
as long as the "open" sub is running. To make it hold its value you may
be able to declare it as Static (see Help files). However, I have read
that the Static keyword can be unreliable.
Another approach is to define a named range in your file & change its
value once the deactivate macro has been run once, and then reset it
next time the file opens, eg:
(recorded in Excel 2007*)


VBA Code:
--------------------


'in the Open macro
ActiveWorkbook.Names.Add Name:="DeactivateMacroHasRun", RefersToR1C1:="=""F"""
'in the deactivate macro
With ActiveWorkbook.Names("DeactivateMacroHasRun")
If .Value = "=""T""" Then Exit Sub
.RefersToR1C1 = "=""T"""
End With
'rest of code...
--------------------




hth
Rob