Assigning macros to buttons on a custom toolbar

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

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

--
Regards,
Tom Ogilvy

"Jack_Feeman" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Thanks for the quick reply, Tom.
I added a custom toolbar from the Customize dialog box>Toolbar>New. 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the quick reply, Tom.
> I added a custom toolbar from the Customize dialog box>Toolbar>New. 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>>
>>
>>

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" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for the quick reply, Tom.
> > I added a custom toolbar from the Customize dialog box>Toolbar>New. 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> >
> >>
> >>
> >>
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > Thanks for the quick reply, Tom.
>> > I added a custom toolbar from the Customize dialog box>Toolbar>New.
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

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 (View>Toolbars>Customize).
• 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Thanks for the quick reply, Tom.
> >> > I added a custom toolbar from the Customize dialog box>Toolbar>New.
> >> > 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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 (View>Toolbars>Customize).
> . 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Thanks for the quick reply, Tom.
>> >> > I added a custom toolbar from the Customize dialog box>Toolbar>New.
>> >> > 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" <[email protected]> wrote in
>> >> >> message
>> >> >> news:[email protected]...
>> >> >> > 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
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 (View>Toolbars>Customize).
> > . 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > Thanks for the quick reply, Tom.
> >> >> > I added a custom toolbar from the Customize dialog box>Toolbar>New.
> >> >> > 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" <[email protected]> wrote in
> >> >> >> message
> >> >> >> news:[email protected]...
> >> >> >> > 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
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> > (View>Toolbars>Customize).
>> > . 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in
>> >> >> message
>> >> >> news:[email protected]...
>> >> >> > Thanks for the quick reply, Tom.
>> >> >> > I added a custom toolbar from the Customize dialog
>> >> >> > box>Toolbar>New.
>> >> >> > 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" <[email protected]> wrote in
>> >> >> >> message
>> >> >> >> news:[email protected]...
>> >> >> >> > 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
>> >> >> >> >
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

Thanks Steve.

I usually do not put spaces in names, but the stock toolbars did and since
the name was visible to the user I put spaces in it to make it more readable
for them.

"STEVE BELL" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> > (View>Toolbars>Customize).
> >> > . 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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" <[email protected]> wrote in
> >> >> >> message
> >> >> >> news:[email protected]...
> >> >> >> > Thanks for the quick reply, Tom.
> >> >> >> > I added a custom toolbar from the Customize dialog
> >> >> >> > box>Toolbar>New.
> >> >> >> > 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" <[email protected]> wrote in
> >> >> >> >> message
> >> >> >> >> news:[email protected]...
> >> >> >> >> > 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

I had a similar problem that appeared whenever the workbook was
renamed. I resolved this by assigning the macro within Visual Basic as
in the following snippet:

CommandBars("SBIC").Controls("Sort Ascending").OnAction = _
ThisWorkbook.Name & "!SortDn"

Note that this WILL NOT work if you try to asssign the macro from the
Excel GUI. Maybe this will fix your problem.

regards,

mike

Thanks Mike,
I did do everything in the GUI.
The whole thing was that I had crafted a custom toolbar with recorded macros
assigned to the buttons. All were done from the Excel GUI. Whenever I would
send the file or upload it to a network share, the macro references assigned
to each button would no longer reflect "this workbook" but take on the new
location and the buttons would not work because they would be looking in the
wrong place for the macros. If I went over to the users' machines and
reassigned the macros to the workbook, they would work fine. Only thing, I
need to send this file all over the country.

I was surprised that when you select "this workbook" from the list when
recording the macro that it would not keep it assigned to "this workbook"
when it was sent via e-mail or uploaded to a different resource.

"mike" wrote:

> I had a similar problem that appeared whenever the workbook was
> renamed. I resolved this by assigning the macro within Visual Basic as
> in the following snippet:
>
> CommandBars("SBIC").Controls("Sort Ascending").OnAction = _
> ThisWorkbook.Name & "!SortDn"
>
> Note that this WILL NOT work if you try to asssign the macro from the
> Excel GUI. Maybe this will fix your problem.
>
> regards,
>
> mike
>
>

MY initial problem was ensuring that renaming the file did not clobber
my button assignments. But what I just noticed is that simply MOVING
the file alters the macro assignment to point to the original location.
I can only guess what is happening when the file is emailed! So, even
if you manually reassign the macros, your workbook is subject to
failing if the user decides they want to move the file or rename it.

If you will add a macro called "Auto_Open" to your project and assign
macros as I've suggested, I'll be very surprised if the problem
persists!

regards,

mike

Great Mike, thanks for the advice.

So it would go like this?
In Auto-open macro put the following for each button:

CommandBars("toolbar_name").Controls("button_name").OnAction = _
ThisWorkbook.Name & "macro.name"

Then end the auto_open macro.

Is there any need to do anything in the auto_close macro?

Thanks again
Jack


"mike" wrote:

> MY initial problem was ensuring that renaming the file did not clobber
> my button assignments. But what I just noticed is that simply MOVING
> the file alters the macro assignment to point to the original location.
> I can only guess what is happening when the file is emailed! So, even
> if you manually reassign the macros, your workbook is subject to
> failing if the user decides they want to move the file or rename it.
>
> If you will add a macro called "Auto_Open" to your project and assign
> macros as I've suggested, I'll be very surprised if the problem
> persists!
>
> regards,
>
> mike
>
>

Actually there is. Your toolbar will remain unless you programmatically
remove it. I would recommend activating the toolbar on open as removing
it on close.

In Auto_Open add the following just before you end the sub

Set myBar = CommandBars("toolbar_name")
myBar.Enabled = True

In Auto_Close add the following

Set myBar = CommandBars("toolbar_name")
myBar.Enabled = False

Great help, Mike. Now maybe I can get this albatross out of my hair.
Thanks for your time.
Jack

"mike" wrote:

> Actually there is. Your toolbar will remain unless you programmatically
> remove it. I would recommend activating the toolbar on open as removing
> it on close.
>
> In Auto_Open add the following just before you end the sub
>
> Set myBar = CommandBars("toolbar_name")
> myBar.Enabled = True
>
> In Auto_Close add the following
>
> Set myBar = CommandBars("toolbar_name")
> myBar.Enabled = False
>
>