This macro works, but this doesent. Any ideas please ?
Posted by David Bissland on October 28, 2001 1:41 PM
Hi,
I am using Excel 97 SR-2
These first 2 macros work OK. They firstly delete the "Save" option in the "File" Menu, and then restore it.
Sub menuItem_Delete()
Dim myCmd As Object
Set myCmd = CommandBars("Worksheet menu bar").Controls("File")
myCmd.Controls("Save").Delete
End Sub
Sub menuItem_Restore()
Dim myCmd As Object
Set myCmd = CommandBars("Worksheet menu bar").Controls("File")
' Id 3 refers to the Save menu item control.
myCmd.Controls.Add Type:=msoControlButton, Id:=3, Before:=5
End Sub
************************************************
If I change the above macros to try and delete and reinstate the "Macro" menu item in the Tools menu, (see below)the first part menuItem_Delete1() works OK, but when I run menuItem_Restore1() I get a "Run time error 5, Invalid Procedure Call or Argument" error message.
Sub menuItem_Delete1()
Dim myCmd As Object
Set myCmd = CommandBars("Worksheet menu bar").Controls("Tools")
myCmd.Controls("Macro").Delete
End Sub
Sub menuItem_Restore1()
Dim myCmd As Object
Set myCmd = CommandBars("Worksheet menu bar").Controls("Tools")
' Id 30017 refers to the Macro menu item control.
myCmd.Controls.Add Type:=msoControlButton, Id:=30017, Before:=11
End Sub
Any ideas appreciated.
Thanks
David
Posted by Mark O'Brien on October 28, 2001 4:33 PM
David,
The reason that the code to restore the "Macro" command doesn't work is because the command is because the "type" is wrong. It is not an "msoControlButton" it's a dropdown button or something. Anyway, the easiest way to fix the problem is just to delete the "Type:=" code. When you use the "ID:= 30017" Excel will know what kind of control to put in. The only problem I had is that it didn't get put into the proper section. i.e. the category dividing line was below "Macro" rather than above it. Maybe someone else can solve that problem. I am pretty sure there is a way to sort that out.
Posted by David Bissland on October 28, 2001 8:38 PM
Hi Mark,
Followed your instructions, and the "Macro" command did re-appear on the "Tools" menu, but when you clicked on the "Macro" part of the drop down menu, just a small empty box appeared instead of the option of "Macros", "Record Macro" and "Visual basic editor".
Still, I am a little bit further down the track than before.
Off the back of your head, do you know the visual basic command to re-set all the tool bars as in View/Toolbars/Customise/Reset because this would be a work around that I am using during testing.
Thanks once more for your help.
David The reason that the code to restore the "Macro" command doesn't work is because the command is because the "type" is wrong. It is not an "msoControlButton" it's a dropdown button or something. Anyway, the easiest way to fix the problem is just to delete the "Type:=" code. When you use the "ID:= 30017" Excel will know what kind of control to put in. The only problem I had is that it didn't get put into the proper section. i.e. the category dividing line was below "Macro" rather than above it. Maybe someone else can solve that problem. I am pretty sure there is a way to sort that out.
Posted by Juan Pablo on October 29, 2001 6:27 AM
To reset CommandBars use this
Application.CommandBars("Worksheet Menu Bar").Reset
Juan Pablo Hi Mark, Followed your instructions, and the "Macro" command did re-appear on the "Tools" menu, but when you clicked on the "Macro" part of the drop down menu, just a small empty box appeared instead of the option of "Macros", "Record Macro" and "Visual basic editor".
Posted by David Bissland on October 29, 2001 11:06 AM
Application.CommandBars("Worksheet Menu Bar").Reset Juan Pablo
Hi Juan,
Thanks for that.
Have a great day
David
Just for those interested, by using the various answers, I worked out the solution to my problem which is shown below.
Sub menuItem_Restore1()
'Dim myCmd As Object
Set myCmd = CommandBars("Worksheet menu bar").Controls("Tools")
myCmd.Controls.Add Type:=msoControlPopup, Id:=30017, Before:=10
myCmd.Reset
End Sub
