Execute code before workbook deactivate

    Hey gang.


    I've got a massive workbook with tons of code based largely around the worksheet_change and selection_change events. I have discovered a bug when a user has double clicked into a cell (cursor blinking in the cell) and they select another open window (Excel, or otherwise) prior to pressing 'Enter' or an arrow or something to finalize the changes to that cell, it causes an error. Is there an event such as before_deactivate where I can force the cell to change prior to activating the selected window?


    Cheers

    Re: Execute code before workbook deactivate


    Try using Application.EnableEvents, the EnableEvents property enables or disables events. An event is an action, like Change, that occurs in Excel, usually the result of a user action. If an event procedure is defined for that event, Excel will automatically
    execute that code if EnableEvents is True. If EnableEvents is False, the code is not executed.

    Re: Execute code before workbook deactivate


    Thanks, I'm familiar with EnableEvents. My bug is a little more problematic than that though. If they change a cell, I run some code to amend the comment in that cell. I can't disable events otherwise I will not capture that cell change. If they double click in the cell and then click another excel workbook before hitting 'Enter' the code breaks down because it begins referencing the active workbook rather than the one that was just deactivated. That's why I'm looking for a pseudo Before_Deactivate event or something along those lines. I hope I'm making sense here.

    Re: Execute code before workbook deactivate


    Hi, everyone.


    I'd like to resurrect this if I could. I am still trying to find a way to execute code prior to workbook_deactivate. I have a situation where if a user has double clicked into a cell and they select another workbook prior to pressing 'Enter', the code in my worksheet_change event errors out. Can this be done?

    Re: Execute code before workbook deactivate


    Hi,


    I've only looked at this quickly but could you change the event code to add in a workbook test, like this?


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Me.Parent.Name = ActiveWorkbook.Name Then
        MsgBox "changed"
    End If
End Sub

    Re: Execute code before workbook deactivate


    That's exactly what I was looking for. Thanks, Richie.


    I added a variable to capture which workbook they selected, then reactivated the parent workbook, and after the completion of the code, re-activated the workbook they selected.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login