Help, events don't work

[denis60]

Help please
I'd try those events on EXCEL 97

Private Sub workbook_beforeclose(Cancel As Boolean)
Private Sub workbook_WindowDeactivate(ByVal Wn As Excel.Window)
Private Sub Worksheet_Deactivate()
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True

and nothing happen in my script

 

[RobBroekhuis]

Are your events on the proper code pages for the excel objects (ThisWorkbook, Worksheet1, etc.)? They won't work if put in a regular code module.
If they are in the right place, check the value of
application.enableevents
If this is false, set it to true.
Rob

 

[combo]

Those codes should be in object modules, i.e for workbook - in thisworkbook's module, for sheets - in a sheet's module.
To activate them, double-click object in project explorer in VB Editor.

 

[denis60]

Thanks guys it's seem so easy for you.

My next question is:

I'm working with 2 windows on the same workbook.
How can i trap the x (close) botton at the top right of the second window.
I know that the workbook_beforeclose event is activated only when the last window(workbook) is closed with the x botton

 

[RobBroekhuis]

Not completely what you want, but you may be able to use the workbook_windowdeactivate event. Why do you need to trap this? (Answer may help us give you better advice)
Rob

 

[denis60]

My first window is a command position, controling other windows. If someone close the second window abnormaly with the X(close) during the execution instead of my exit botton, my program will trap.

How the workbook_windowdeactivate event can tell me if the X(close) botton is operate instead of just changing the window focus between my first and second window

 

[RobBroekhuis]

You're right, the event can't tell you, because the window hasn't been closed yet when the deactivate event is called. I didn't realize that. You can have, instead, a windowactivate event which checks, upon entry, if there are two active windows belonging to the workbook

if activeworkbook.windows.count<2
(reopen your windows the way you want them here)
end if

This doesn't prevent the window from closing in the first place, but it may get around your problem. Alternatively, you could use a modeless userform to do the directing bit (instead of the second window on the same workbook), to avoid the issue altogether.

Rob

 

[denis60]

Yes rob, you're right, that exactly what i thought, making all the program in a userform could be more easy.

We could protect closing windows in oversizing windows to hide buttons and use window_deactivate to prevent moving windows to close it.

Thanks again Rob