Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Sheet event BEFORE deactivating.
Hi All
Many moons ago I posted a message regarding triggering an event before the sheet deactivates... I had 2 responses, one of which was from Peter T as follows: Private Sub Worksheet_Deactivate() If Len(Range("A1")) = 0 Then ' empty cell A1 On Error GoTo errH: ' if necessary disable events Application.EnableEvents = False Me.Activate Application.Goto Range("A1"), True MsgBox "Complete cell A1" End If errH: Application.EnableEvents = True End Sub This works well except all the sheets in my workbook have a Worksheet_Activate event. The above code does reactivate the sheet if not filled in correctly, but the code for the target sheet still runs, which then gives errors. My question is, can the worksheet_activate code for the target sheet be switched off if the above code discovers a FALSE value on the original sheet? I'm not using buttons to navigate the workbook so I can't capture it that way. I hope thats clear(?) Thanks in advance Trevor Williams |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Sheet event BEFORE deactivating.
What is it that you want testing for false? -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=41022 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Sheet event BEFORE deactivating.
Hello again,
If you have the same code in all sheet modules might as put it in the Thisworkbook module. Have a go with the following (comment or remove sheet module code) ' ThisWorkbook module Private mbExit As Boolean Private Sub Workbook_SheetActivate(ByVal Sh As Object) If mbExit Then mbExit = False Exit Sub End If ' normal activate code End Sub Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) mbExit = False If Len(Sh.Range("A1")) = 0 Then ' empty cell A1 On Error GoTo errH: ' if necessary disable events Application.EnableEvents = False Sh.Activate Application.Goto Sh.Range("A1"), True mbExit = True MsgBox "Complete cell A1" End If errH: Application.EnableEvents = True End Sub Not sure if I followed the objective corectly so test thoroughly, incl similar code in 2+ workbooks and switching between them. Regards, Peter T "Trevor Williams" wrote in message ... Hi All Many moons ago I posted a message regarding triggering an event before the sheet deactivates... I had 2 responses, one of which was from Peter T as follows: Private Sub Worksheet_Deactivate() If Len(Range("A1")) = 0 Then ' empty cell A1 On Error GoTo errH: ' if necessary disable events Application.EnableEvents = False Me.Activate Application.Goto Range("A1"), True MsgBox "Complete cell A1" End If errH: Application.EnableEvents = True End Sub This works well except all the sheets in my workbook have a Worksheet_Activate event. The above code does reactivate the sheet if not filled in correctly, but the code for the target sheet still runs, which then gives errors. My question is, can the worksheet_activate code for the target sheet be switched off if the above code discovers a FALSE value on the original sheet? I'm not using buttons to navigate the workbook so I can't capture it that way. I hope thats clear(?) Thanks in advance Trevor Williams |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Sheet event BEFORE deactivating.
Hey Peter, good to hear from you again.
I'll implement, test, and report back today... finger crossed :) Thanks Trevor "Peter T" wrote: Hello again, If you have the same code in all sheet modules might as put it in the Thisworkbook module. Have a go with the following (comment or remove sheet module code) ' ThisWorkbook module Private mbExit As Boolean Private Sub Workbook_SheetActivate(ByVal Sh As Object) If mbExit Then mbExit = False Exit Sub End If ' normal activate code End Sub Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) mbExit = False If Len(Sh.Range("A1")) = 0 Then ' empty cell A1 On Error GoTo errH: ' if necessary disable events Application.EnableEvents = False Sh.Activate Application.Goto Sh.Range("A1"), True mbExit = True MsgBox "Complete cell A1" End If errH: Application.EnableEvents = True End Sub Not sure if I followed the objective corectly so test thoroughly, incl similar code in 2+ workbooks and switching between them. Regards, Peter T "Trevor Williams" wrote in message ... Hi All Many moons ago I posted a message regarding triggering an event before the sheet deactivates... I had 2 responses, one of which was from Peter T as follows: Private Sub Worksheet_Deactivate() If Len(Range("A1")) = 0 Then ' empty cell A1 On Error GoTo errH: ' if necessary disable events Application.EnableEvents = False Me.Activate Application.Goto Range("A1"), True MsgBox "Complete cell A1" End If errH: Application.EnableEvents = True End Sub This works well except all the sheets in my workbook have a Worksheet_Activate event. The above code does reactivate the sheet if not filled in correctly, but the code for the target sheet still runs, which then gives errors. My question is, can the worksheet_activate code for the target sheet be switched off if the above code discovers a FALSE value on the original sheet? I'm not using buttons to navigate the workbook so I can't capture it that way. I hope thats clear(?) Thanks in advance Trevor Williams |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XL2002 - Application Resize Event... | Excel Programming | |||
XL2002 - trigger sheet event BEFORE deactivating | Excel Programming | |||
Deactivating Multipage Change Event | Excel Programming | |||
Deactivating Multipage Change Event | Excel Programming | |||
Format Event Trigger (XL2002) | Excel Programming |