cant run existing macros

[Guest]

I have a macro tool bar with custom macro buttons - all with macros assigned.
All have worked well for me in the past. However, when I click one of the
buttons now, the macros do not run. I get an error that reads " personal.xls
can not be found" When I open the VBE, the macros are still there and I can
re-assign the macro to the buttons. But when I close the workbook and reopen
MS Excel 2003, the macro buttons fail to retain the assigned macro. What can
I do help the buttons remember that they are to run the assigned macro.

Thanks in advance for help...and have a great day!

 

[Sandy Mann]

John,

I have a macro tool bar with custom macro buttons

I assume that you mean that you have a custom toolbar. If that is the case
then you have to re-attach the toolbar to the workbook after altering it or
it will only retain the original toolbar.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Dave Peterson]

Your life will become much simpler if you include code to create the toolbar
when the workbook is opened and include code to destroy the toolbar when the
workbook is closed.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

 

[Sandy Mann]

Dave,

I like your method of creating a toolbar. Is there any way of adding code
to *Begin a group* to separate the buttons?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Dave Peterson]

Add a line in the section.

For iCtr = LBound(MacNames) To UBound(MacNames)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" &
MacNames(iCtr)
.Caption = CapNamess(iCtr)
.Style = msoButtonIconAndCaption
.FaceId = 71 + iCtr
.TooltipText = TipText(iCtr)
.BeginGroup = True '<-- added
End With
Next iCtr

 

[Gord Dibben]

Sandy

Example when adding to the right-click menu

Note the .Begin Group which you would use in Dave's example code

With Application.CommandBars("Cell").Controls.Add(temporary:=True)
.BeginGroup = True
.Caption = "Clear Formats"
.OnAction = "MyMacros.xla" & "!ClearFormatting"


Gord

 

[Sandy Mann]

Thank you very much Dave. I did try typing
..Beg
to see if VBA would bring up any assistance but I only got *BuiltIn* I also
looked at all the other *With* options but the *also see* was either greyed
out or did not lead me to anything that helped with Begin a group.
--
Thank you again,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

Thank you Gord,

I have used John's *menumakr* method of building a menu in the past when I
was at
work but most of the users soon forgot that there it was there and they were
forever asking me to attach a tool bar for them. Dave's way seems very
neat.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Gord Dibben]

Can't go wrong following Dave's advice when it comes to coding.


Gord

 

[Sandy Mann]

Thank you very much Dave. I did try typing
.Beg
to see if VBA would bring up any assistance but I only got *BuiltIn*

My eyesight must be going. Now whenever I type even just the period in the
*With * section, *BeginGroup* is the second in the list - I swear that it
wasn't there before!

5 minutes later -

No, I just tested it again and I must have been outside of the *With*
previously. My eyesight must be worse than I thought!

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

Thank you very much Dave. I did try typing
.Beg
to see if VBA would bring up any assistance but I only got *BuiltIn* I
also
looked at all the other *With* options but the *also see* was either
greyed
out or did not lead me to anything that helped with Begin a group.
--
Thank you again,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Dave Peterson]

Welcome to my club!

Thank you very much Dave. I did try typing
.Beg
to see if VBA would bring up any assistance but I only got *BuiltIn*

My eyesight must be going. Now whenever I type even just the period in the
*With * section, *BeginGroup* is the second in the list - I swear that it
wasn't there before!

5 minutes later -

No, I just tested it again and I must have been outside of the *With*
previously. My eyesight must be worse than I thought!

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk