Custom Menus Error at Close

C

CyberBuzzard

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
 
H

Herbert

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
 
J

Jim Thomlinson

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.
 
C

CyberBuzzard

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
 
C

CyberBuzzard

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top