Jack,

The spaces in the caption is just a gimmick for me to space out my buttons.
You can put anything in there. In fact you can replace that with .FaceId =
156 where the number represents a different image. And than you can use the
control tip to alert the user to what the button is for. And in anything
not a caption - try to avoid using spaces (they just make things harder for
Excel.).

Example: sheet name = My Sheet
better = MySheet
macro name = My Macro
better = MyMacro
If you must have a space use either "-" or "_". This way excel doesn't see
a space.
My-Macro
My_Macro

The .OnAction = "MyMacro"
is the name of the macro you want to fire with the button. I usually copy
and paste to get it correctly entered.

So my macro was
Sub GetFile()

Look up "Face ID" on Google. There are some places where you can get a file
with the differenct faces available.

........


--
steveB

Remove "AYN" from email to respond
"Jack_Feeman" wrote in message
...
Hi Steve,

Guess I do need your help a bit.

In each of the button segments, what do I substitute for the ".OnAction =
"GetFile" if I want to run a macro when the button is clicked?

Also I named my custom toolbar with spaces in it like some of the default
ones, is that going to be a problem in this scenario?

Thanks again for your help

Jack

"STEVE BELL" wrote:

Jack,

Here is some code that I put in a regular module.

I have the workbook open macro fire the add toolbar, and the workbook
close
macro fire the delete toolbar macro.
This way you don't have to set up a special toolbar on each person's
machine. They just have to open your workbook.

Let me know if you need any help to make it work...

=====================================
Sub addToolbar()
Dim oCBMenuBar As CommandBar
Dim oCBCLeave As CommandBarControl
Dim iMenu As Integer
Dim i As Integer
On Error Resume Next
Application.CommandBars("AdAnalysis").Delete
Set oCBMenuBar = Application.CommandBars.Add(Name:="AdAnalysis")
With oCBMenuBar
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = " Open Sales "
.Style = msoButtonCaption
.TooltipText = "open Sales Data workbook"
.OnAction = "GetFile"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = " Import Sales Data "
.Style = msoButtonCaption
.TooltipText = "Add new Sales Data"
.OnAction = "SalesImprt"
End With
' With .Controls.Add(Type:=msoControlButton)
' .FaceId = 156
' .TooltipText = "Next month"
' .OnAction = "nextMonth"
' End With
' With .Controls.Add(Type:=msoControlButton)
' .FaceId = 157
' .TooltipText = "Last month"
' .OnAction = "lastMonth"
' End With
' With .Controls.Add(Type:=msoControlButton)
' .BeginGroup = True
' .Caption = "Summary"
' .Style = msoButtonCaption
' .TooltipText = "Show summary sheet"
' .OnAction = "gotoSummary"
' End With
.Position = msoBarTop
.Protection = msoBarNoMove
.Visible = True
End With
End Sub

Sub deleteToolbar()
On Error Resume Next
Application.CommandBars("AdAnalysis").Delete

End Sub
=============================

--
steveB

Remove "AYN" from email to respond
"Jack_Feeman" wrote in message
...
Time to regroup....Nothing worked in this instance...
Let me restate the problem as it stands now.

. Made a custom toolbar from the customize box
(ViewToolbarsCustomize).
. Added several buttons to the toolbar.
. Recorded several macros to do different customized functions.
. Used the store macro in "This Workbook" option vice the other two
{filename} or {all open workbooks}
. Assigned each macro to a button on the custom toolbar.
. Protected the worksheet with password.
When I test it on numerous computers here in IT, it works fine, but
when I
send it via e-mail or save to a shared network drive, the recipient's
copy
has all the macros referencing the previous location and only errors
out
with
cannot find macro.

When I go to that machine and re-assign the macros to the Workbook
again,
they all work fine. I must send this file to quite a few people
scattered
over the US/Canada and obviously cannot afford to go to each location
to
reassign the macros.

I have tried all the previously suggested fixes to no avail.
. Can I somehow further protect the workbook to prevent the macro
references
from reassigning themselves to the last saved location and keep the
"this
workbook" location I assigned them?
. Can I protect the VBA separately or is it a protect one protect all
situation?
. Can the way Office2k3 is installed affect the way macros are
referenced?
(Mine was installed from the CD_ROM; the ones that do not work were
pushed
out through the newtork.

Thanks again


"STEVE BELL" wrote:

Jack,

Glad you like the idea!

I use it all the time.

Also - I sometimes use the workbook_activate and workbook_deactivate
events
when I only want the toolbar to appear with that workbook ONLY.

--
steveB

Remove "AYN" from email to respond
"Jack_Feeman" wrote in message
...
Thanks Steve,

Never did it that way. Should be a great learning experience for me.

Thanks again.

Jack

"STEVE BELL" wrote:

A suggested method is to have the workbook create the toolbar when
it
is
opened.
And delete the toolbar when it closes.

Include at the start of the code to create a line to first delete
the
toolbar if it exists.

This way the users machine is not cluttered with copies of the
toolbar.
No
multiple copies of the toolbar exist. And when you update the
workbook
the
toolbar works just the way you designed it.

This should eliminate the problem for you...

--
steveB

Remove "AYN" from email to respond
"Jack_Feeman" wrote in
message
...
Thanks for the quick reply, Tom.
I added a custom toolbar from the Customize dialog
boxToolbarNew.
Then
assigned buttons to the toolbar and assigned macros to the
buttons.

To show the custom toolbar when the wookbook is opened, I added a
This_workbook_auto_open and a This_workbook_auto_close to close
the
toolbar
upon closing the workbook.

"Tom Ogilvy" wrote:

I assume you mean buttons from the menus. If so, how do you
create
the
toolbar? (Assume custom toolbar)

--
Regards,
Tom Ogilvy

"Jack_Feeman" wrote in
message
...
This is a weird problem that seemed to pop up after users were
upgraded
to
Office 2003. The template was developed in Excel 2003 and
seemed
to
work
fine
when users had Office 2k. After users were upgraded, all
macros
assignments
somehow changed to obsolete versions of the template each time
a
user
would
open it from either a netshare location I placed the file at
or
from
an
email
I attached the file to.

I changed the template to a regular workbook and updated all
macro
assignments to the toolbar buttons. Some problem from the
.xls.

I went to the user's office and loaded in the workbook and the
buttons
did
not work. I checked the macro list and there were double the
amount
of
macros
listed. I check ed the Window drop-down and somehow an old
copy
of
the
template was also loaded even though I had not opened it.

I searched the harddrive and found a few old versions of the
template
and
workbook which I deleted. Then I reassigned the macros to the
buttons
on
the
user's machine and it works fine. But when she forwarded it to
the
next
user,
they experienced the same thing, the buttons did not work
because
they
were
referencing old versions of the workbook.

This workbook will be used across the country and I won't be
able
to
go
around to reassign the macros on each of the users' machines.

When I assigned the macros, I selected "this workbook" to
assign
the
macro
to. When this issue started I tried to reassign the macros to
"file.name",
but that did not work. However, during the 'original'
development, I
may
have
forgot to set that parameter and left it on the default 'All
Open
Workbooks'.
Could this be the reason the macros are referencing old
versions?
Doesn't
selecting "New" start a fresh workbook without any references
to
user
workbooks?

Thanks for any insight!
Jack