Custom Menus Error at Close

[Guest]

I have an error appearing when I close the workbook with my VBA code. I was
able to hide the custom menus I made for my application when, for example, I
open a new workbook. The problem arises when I close the workbook either with
the File|Close menu or with Alt|F4. The error “Invalid procedure call or
argument†appears every time I close the workbook.

In the “ThisWorkBook†Module I have written the following:
Private Sub Workbook_Open()
Call SampleMenu
Call GeneralFinMenu
Call FACILITIESMenu
Call ConstructionMenu
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Msg As Variant
Application.ScreenUpdating = False
If Not Me.Saved Then
Msg = MsgBox(Space(5) & vbCrLf & _
"Do you want to save the changes you made to " & _
Me.Name & "?" & _
vbCrLf & vbCrLf & vbCrLf, vbQuestion + vbYesNoCancel, _
"BOUNDARY SOFTWAREâ€)
If Msg = vbYes Then
Me.Save
ElseIf Msg = vbNo Then
Me.Saved = True
ElseIf Msg = vbCancel Then
Cancel = True
Exit Sub
End If
End If
Call DeleteMenuSample
Call DeleteMenuGeneral
Call DeleteMenuFACILITIES
Call DeleteMenuCostCats
End Sub

Private Sub WorkBook_Activate()
Call ShowMenus
End Sub

Private Sub WorkBook_Deactivate()
Call HideMenus
End Sub


On a standard Module I have the following procedure:
Sub HideMenus()
CommandBars(1).Controls("Sample Data").Visible = False
CommandBars(1).Controls("General FINANCIAL").Visible = False
CommandBars(1).Controls("FACILITIES").Visible = False
CommandBars(1).Controls("CONSTRUCTION Data").Visible = False
End Sub
This last procedure is the one marked by the error at closing.

--
Any help will be appreciated.

Regards,

CyberBuzzard

 

[Guest]

Hello,

I couldn't locate your problem but I 'd like to make a note:
The event Workbook_BeforeClose occurs before the event Workbook_Deactivate.
So if you delete your menues in BeforeClose there will be nothing to hide in
BeforeClose.
Or I might have overlooked something here ....

Herbert

 

[Guest]

Try compiling the code. In the VBE select Debug -> Compile VBA Project. The
syntax error should be highlighted. Cances are you have called a procedure
without supplying one of the argments of the procedure. Probably one of these
Call DeleteMenuSample
Call DeleteMenuGeneral
Call DeleteMenuFACILITIES
Call DeleteMenuCostCats

As a complete aside your variable msg should be declared as long. Not that
it will make a big difference in this case but it is a bit mroe efficient.

 

[Guest]

Thank you Herbert.

I had noticed your point about the sequence of events. Now, how do I keep my
menus from appearing in other workbooks and at the same time avoid the Error
message?
--
Any help will be appreciated.

Regards,

CyberBuzzard

 

[Guest]

Thank you Jim.

I runned the compiling but to no avail. I have noticed that the Compile
VBAProject utility sometimes misses badly on obvious errors I painfully find
later when an error appears.
--
Any help will be appreciated.

Regards,

CyberBuzzard