Excel ThisWorkbook code not working

[thefox149]

Hi Guys

I am pretty good with VBA for Access but Excel is another universe.

I have code in the thisworkbook that does not allow the user to hit the X close button. This works fine.

However

I have code that creates a new window the new window and current window are next to each other.

The user can still close one of the windows ..I would have thought that the thisworkbook function would apply to both windows. The users should not be allowed to close the windows I want them to use my cmd buttons...any ideas on how to fire the thisworkbook function so it applies to both windows

here is the code

Private Sub Workbook_BeforeClose(Cancel As Boolean)


If CloseMode = vbFormControlMenu And Cancel_Code = Empty Then
Cancel = True
Exit Sub
End If
End Sub


then in my module i have

Public Cancel_Code As String

Sub Close_Workbook()
ActiveSheet.Select
Cancel_Code = "False"
Application.Quit

 

[GlennUK]

I will look at this later today, but in the meantime, think about this ... your event routine is Workbook_BeforeClose whereas the action you're wanting to test for is before a Window closing ( closing a secondary window does not close the workbook ).

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[thefox149]

good point. This workbook before close the second window is only being closed not the entire workbook hmm ...

 

[thefox149]

anyone got any ideas Im still stuck on this one

 

[GlennUK]

Do you still want multiple windows for a workbook, and not allow the user to use the 'X' to close either of them? If so, what about protecting the workbook ( Windows ), and the windows can't then be altered by the user?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[vbap]

Lookup WithEvents keyword it might lead you to what you want to do.

 

[GlennUK]

Hi vbap,

how is WithEvents ( specifically for Class Modules ), going to help here?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[vbap]

I haven't fully looked it up but you may be able to intercept any workbook closure using WithEvents on the Application object.

 

[vbap]

I believe I have some sample code...looking for it.

 

[vbap]

Here is that sample code...I wrote it 2-3 years ago and haven't used it since then so it took me awhile to find it.

When you run the Main routine you won't be able to close any workbook until after the timer fires. Hope that changing the code to suit your needs works for you.

Oh...I used the .Caption property as a tag to close the workbook window I wanted but I suppose you can do something different.


Public App As New clsEvents
Public ts As Variant 'datetimestamp
Sub Main()
With Application
.DisplayAlerts = False
.SheetsInNewWorkbook = 1
End With
Set App.XLEvents = Application
Call SetUpTimer
End Sub
Sub ProcessTimerEvent()
MsgBox "Timer has fired."
Set App = Nothing
End Sub
Sub SetUpTimer()
ts = Now + TimeValue("00:01:00")
Application.OnTime ts, "ProcessTimerEvent"
End Sub

Sub CancelTimer()
On Local Error Resume Next
Application.OnTime ts, "ProcessEvent", , False
CancelIntErr:
End Sub


This code goes into a class module...name the class module clsEvents if you will.

Public WithEvents XLEvents As Application

Private Sub XLEvents_NewWorkbook(ByVal Wb As Excel.Workbook)
Windows.Arrange ArrangeStyle:=xlHorizontal
Application.DisplayFormulaBar = False
End Sub

Private Sub XLEvents_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, Cancel As Boolean)
On Local Error GoTo XLEventErr
s = ActiveWorkbook.Windows(ActiveWindow.Caption).Caption
If s = "CloseIt" Then
Exit Sub
Else
Cancel = True
End If
Exit Sub
XLEventErr:
End Sub

 

[vbap]

Oops...you should change the literal string in the CancelTimer routine to "ProcessTimerEvent" should you want to prevent the timer from popping for some reason.