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.