Custom Menu Problems with Macros

Posted by Christopher Patrick on August 28, 2001 10:30 AM

Hi all-

If someone can help me out, I would greatly appreciate it. I have a custom toolbar, with custom buttons all tied to macros. When I created the buttons and macros, I selected the "This Workbook" option from the "Macros in" dialogue. It seems that if I copy the file and rename it though, that it continually has attatched the previous file name to the macro, and to run the macro, it must first open my original file...is there anyway to tell the buttons to only run the macros in their own workbook, and stay with those settings?

THanks

CHris

Posted by Jerid on August 28, 2001 10:56 AM

Can you provide a sample of your macro?

Jerid

Posted by Christopher Patrick on August 28, 2001 11:01 AM

Here is a sample simple macro I have. I just noticed also, that if I move the original file to any location other than originally saved, my customized macro buttons no longer work. It is somehow always appending the path and file name to the macro attatched to each button.

THanks for any help

Sub VTS()
'
' VTS Macro
' Macro recorded 8/22/2001 by GE Medical Systems
'

Sheets("VTR Documentation").Visible = False
Sheets("VTS Title Sheet").Select
Range("A1").Select

'
End Sub

Posted by Jerid on August 28, 2001 12:26 PM

for any help VTS Macro Macro recorded 8/22/2001 by GE Medical Systems

It's because when you assign the button to your Macro, Excel automaticly add the workbook name.

You are better off with a procedure that builds the toolbar for you. This is an example of what I do.

From Excel press Alt F11 to open the VBE, select insert module and copy this code into it. Make changes as needed.

[EXAMPLE - Must be in a Module]
Public TBarMenuBar As CommandBar

'Runs when the workbook is opened
Sub Auto_Open()
Dim TBarMenu As Object

'~~~~ Creates CommandBar ~~~~
Set TBarMenuBar = Application.CommandBars.Add(Name:="MyCmdBar", Position:=msoBarTop, Temporary:=True)

'#### Creates custom menu items ####
With TBarMenuBar.Controls
'~~~~ Create Button for MyMacro ~~~~
Set TBarMenu = .Add(Type:=msoControlButton, Temporary:=True)
TBarMenu.Caption = "My Macro"
TBarMenu.FaceId = 284
TBarMenu.OnAction = "MyMacro"
TBarMenu.Visible = True
End With

'~~~~ Make new bar visible ~~~~
TBarMenuBar.Visible = True
End Sub

'Your Macro
Sub MyMacro()
MsgBox "Test Macro"
End Sub

'Runs when the workbook is closed
Sub Auto_Close()
Application.CommandBars("MyCmdBar").Delete
End Sub

Hope this helps, Jerid

Posted by Christopher Patrick on August 28, 2001 12:43 PM

Re:Thank you so much....

Thank you so much...that is exactly what I needed! Take care.

CHris

VTS Macro Macro recorded 8/22/2001 by GE Medical Systems '#### Creates custom menu items #### With TBarMenuBar.Controls '~~~~ Create Button for MyMacro ~~~~ Set TBarMenu = .Add(Type:=msoControlButton, Temporary:=True) TBarMenu.Caption = "My Macro" TBarMenu.FaceId = 284 TBarMenu.OnAction = "MyMacro" TBarMenu.Visible = True End With '~~~~ Make new bar visible ~~~~ TBarMenuBar.Visible = True 'Your Macro