Deactivate event appears to be overriding next activate event

[ezrizer]

Hello,

I have workbook activate and also workbook open events to minimize the ribbon and a before close and deactivate event to maximize the ribbon. I have this in 4 separate workbooks and it works fine if only one of the 4 are open and the other 3 are not. However if I have 3 or 4 of the 4 containing these events open at the same time the deactivate event of one workbook seems to be overriding or cancelling the opposite activate event of the next book. Opposite meaning Deactivate maximize of the previous book is preventing the activate minimize of the next book. This happens if I'm just switching between the books and also as I close one to move onto the next.

Any ideas how to make the activate event occur regardless of what the previous deactivate event is /was?

Thanks

[TMS]

If you have a workbook activate and deactivate events, I wouldn't have thought you'd need the open and before close events. When you open a workbook, it becomes the active workbook so it will fire the workbook activate event.

Maybe there's some duplication or confusion.

It would probably help your cause if you shared your code and, ideally, a sample workbook or two.

Regards, TMS

[ezrizer]

Thanks for the reply. Will the deactivate fire on close?

[TMS]

Try putting this in the workbook module in an empty workbook and save as a macro enabled workbook.

Option Explicit

Private Sub Workbook_Open()
Debug.Print "open"
End Sub

Private Sub Workbook_Activate()
Debug.Print "activate"
End Sub

Private Sub Workbook_Deactivate()
Debug.Print "deactivate"
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Debug.Print "before save"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Debug.Print "before close"
End Sub

Close it, open it, save it, close it ... see the sequence of events.

You will see, for example:

So, in answer to your question, yes, it will fire when you close the workbook ... because the workbook is deactivated.

[ezrizer]

I have removed all of the WB Open events related to minimizing the ribbon and removed ALL WB BeforeClose events however if I have my 4 sheets open and toggle through them 2 act correctly the other 2 don't. It's not specific to any 2 of the workbooks it's the order in which they're activated and deactivated. I can only deduct that the maximize on deactivation is interfering with minimize on activation of next. I've tried adding code pause to the deactivation but got the same results

[TMS]

"It would probably help your cause if you shared your code and, ideally, a sample workbook or two."

[ezrizer]

sample Workbook attached with activate / deactivate code along with command buttons to simulate desired activate deactivate results.

First time attaching, hopefully I did it correctly.
Book1.xlsm

[ezrizer]

No replies so I guess no one has any ideas. I can add that I was having paste issues and figured out that some of my other activate/deactivate events were causing issues. Essentially if you have opposite activate / deactivate events excel can't handle it. Ex, I have 2 workbooks that I don't want to see the formula bar, so on activate I hide them but I don' t care if the formula bar shows on other workbooks so I show them on deactivate. If I'm switching back and forth between the two particular sheets that i want the bar hidden not only does it not work it also prevents pasting between the 2 books. Removing the hide/ unhide from the activate /deactivate events clears up the problem. I guess I can try moving the events to open / close but I'm not to optimistic. It appears that uniquely customizing excel for specific workbooks is limited.

[TMS]

I must apologise. I don't recall seeing your post with the sample workbook.

I have to be honest and say that, as an Excel user, it really pees me off when other workbooks take control of my environment, particularly when they don't or can't put it back the way they found it.

I found that sheet activate/deactivate macros were a pain because you couldn't copy and paste between sheets. I would have thought that workbook activate/deactivate events would be less of a problem.

If you move the events to open/close you mandate that other workbooks will be without a ribbon until you close your workbook, assuming that it closes cleanly.

Regards, TMS