Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Worksheet Deactivate Event only run once
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Worksheet Deactivate Event only run once
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 "robzrob" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Worksheet Deactivate Event only run once
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 -- broro183 Rob Brockett. Always learning & the best way to learn is to experience... ------------------------------------------------------------------------ broro183's Profile: 333 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=187313 http://www.thecodecage.com/forumz/chat.php |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
WorkBook Deactivate Event | Excel Programming | |||
Changing sheets after the DeActivate Event. | Excel Discussion (Misc queries) | |||
Workbook Deactivate event problem | Excel Programming | |||
Workbook Deactivate Event | Excel Programming | |||
Can´t Convert Formulas to Values with Deactivate event | Excel Programming |