| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Menus Error at Close
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 procedu 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Custom Menus Error at Close
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 "CyberBuzzard" wrote: 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 procedu 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Custom Menus Error at Close
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. -- HTH... Jim Thomlinson "CyberBuzzard" wrote: 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 procedu 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Custom Menus Error at Close
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 "Herbert" wrote: 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 "CyberBuzzard" wrote: 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 procedu 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Custom Menus Error at Close
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 "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "CyberBuzzard" wrote: 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 procedu 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Custom menus | Excel Programming | |||
| Custom faces for custom menus/commandbars | Excel Programming | |||
| custom menus | Excel Programming | |||
| Custom menus | Excel Programming | |||
| Custom Menus | Excel Programming | |||
Linear Mode
