Re: Assigning macros to buttons on a custom toolbar

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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: *** name = My ***
> better = My***
> 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" <JackFeeman@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:EFD5A5E8-C014-4D1C-BE1F-98F0DE81599A@xxxxxxxxxxxxxxxx
> > 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 ***"
> >> ' .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" <JackFeeman@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:D194170E-AA34-4FAF-8163-DA3CC85C8831@xxxxxxxxxxxxxxxx
> >> > 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 work*** 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" <JackFeeman@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> >> news:0A7A0E12-697C-4BA9-B939-78888258AAB1@xxxxxxxxxxxxxxxx
> >> >> > 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" <JackFeeman@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> >> >> >> message
> >> >> >> news:6C53A52A-06F7-49BF-88CC-B2127932FCCD@xxxxxxxxxxxxxxxx
> >> >> >> > 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" <JackFeeman@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> >> >> >> >> message
> >> >> >> >> news:6B86AB1F-A06D-4F64-B0B2-FFA3747755A5@xxxxxxxxxxxxxxxx
> >> >> >> >> > 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
.

Quantcast