A way to create a Command Bar without code???

J

JB

Is there a way to create a Command Bar without code???

I AM NOT A PROGRAMMER... I know excel and can make my way
arround VB for applications... though I could probobly do
it, I do not have the time it would take for me to try and
figure this out in code.
 
B

Bob Umlas

Yes.
1 - View/Toolbars/Customize
2 - Click New
3 - Enter a name for the toolbar, Click OK
4 - Click the Commands Tab
5 - Select the tool bar buttons you want to put on the command bar
6 - repeat 5 until it's what you want
7 - Done.
 
J

JB

ok... commandbar, toolbar are one and the same... got it.

That still leaves me without a solution to my original
post... (SEE BELOW) ...

I copied all of my macros into a blank spreadsheet... via
VB editor... moved modules containing macros from old
spreadsheet into a new.

.... while still in the new book, I created the toolbar,
inserted the macro buttons with links to the macros
recently moved to this new book.

I saved the file "my-macros.xls" and then saved as an add-
in "my-macros.xla", opened the spreadsheet I am working on
and loaded the add-in.

Everything works until I remove/delete "my-macros.xls".
then the macros are looking to the old .xls file, not to
the .xla add-in which I loaded.

THE ADD-IN FILE IS NOT SELF CONTAINED!!

I NEED TO BE ABLE TO DISTRIBUTE THE FILE TO ALL IN MY
DEPARTMENT WITHOUT HAVING TO SET UP NEW
TOOLBARS/BUTTONS/MACROS ON EVERY MACHINE FOR EVERY USER.

Thoughts??

Thanks for your help!
JB
 
B

Bernie Deitrick

JB,

Try running the macro below to change the link from the .xls to the .xla
file. You can also change the path by modifying the code, but as written
this assumes both files are in the same folder.

HTH,
Bernie
MS Excel MVP

Sub RepairUserDefinedButtons3()
Dim CmdBar As CommandBar
Dim i As Integer
On Error GoTo ErrorReading:

For Each CmdBar In CommandBars
For i = 1 To CmdBar.Controls.Count
If CmdBar.Controls(i).BuiltIn = False Then
If InStr(1, CmdBar.Controls(i).OnAction, _
"my-macros.xls") Then
CmdBar.Controls(i).OnAction = _
Replace(CmdBar.Controls(i).OnAction, _
"my-macros.xls", "my-macros.xla")
End If
End If
ErrorReading:
Next i
Next CmdBar
End Sub
 
T

Tom Ogilvy

Just change the onaction property of the buttons to point to the xla
instead.

Yelling at us won't help. We aren't having the problem or causing it.
 
Top