How do you distribute an Excel 2003 spreadsheet application with buttons
linked to macros?

Do you actually have to relink all buttons to the macros for every new
location? I can't believe that - I must be overlooking something.

My spreadsheet has buttons which are linked to macros.

When the spreadsheet is moved to a different location (on the same computer
or a new computer), the following error occurs when a button is clicked:

"A document with the name fubar.xls is already open. You cannot open
two documents with the same name etc."

I moved all macros into the Sheet1 Excel Object. That didn't help.

I have tried using buttons from the control toolbox (which I read should
correct this but doesn't), and from the forms toolbox, and the "custom menu
item" from the macros list. Nothing works.

When I put the spreadsheet on another machine, sometimes the buttons are
there but I have to link them to the macros. Sometimes the buttons aren't
there at all.

I don't understand this and can't find anything to clarify it. Where are
the toolbars stored? Why can't the buttons just look in the current
workbook for the macros?

The buttons are on a toolbar, not a user form.


"BillE" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> How do you distribute an Excel 2003 spreadsheet application with buttons
> linked to macros?
>
> Do you actually have to relink all buttons to the macros for every new
> location? I can't believe that - I must be overlooking something.
>
> My spreadsheet has buttons which are linked to macros.
>
> When the spreadsheet is moved to a different location (on the same
> computer or a new computer), the following error occurs when a button is
> clicked:
>
> "A document with the name fubar.xls is already open. You cannot open
> two documents with the same name etc."
>
> I moved all macros into the Sheet1 Excel Object. That didn't help.
>
> I have tried using buttons from the control toolbox (which I read should
> correct this but doesn't), and from the forms toolbox, and the "custom
> menu item" from the macros list. Nothing works.
>
> When I put the spreadsheet on another machine, sometimes the buttons are
> there but I have to link them to the macros. Sometimes the buttons aren't
> there at all.
>
> I don't understand this and can't find anything to clarify it. Where are
> the toolbars stored? Why can't the buttons just look in the current
> workbook for the macros?
>
>

On a toolbar, right?

I think your life will be lots easier if you create that toolbar when the
workbook opens and destroy it when the workbook closes.

Some references if you want to look at that option:

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)

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

In xl2007, those toolbars and menu modifications will show up under the addins.



BillE wrote:
>
> The buttons are on a toolbar, not a user form.
>
> "BillE" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > How do you distribute an Excel 2003 spreadsheet application with buttons
> > linked to macros?
> >
> > Do you actually have to relink all buttons to the macros for every new
> > location? I can't believe that - I must be overlooking something.
> >
> > My spreadsheet has buttons which are linked to macros.
> >
> > When the spreadsheet is moved to a different location (on the same
> > computer or a new computer), the following error occurs when a button is
> > clicked:
> >
> > "A document with the name fubar.xls is already open. You cannot open
> > two documents with the same name etc."
> >
> > I moved all macros into the Sheet1 Excel Object. That didn't help.
> >
> > I have tried using buttons from the control toolbox (which I read should
> > correct this but doesn't), and from the forms toolbox, and the "custom
> > menu item" from the macros list. Nothing works.
> >
> > When I put the spreadsheet on another machine, sometimes the buttons are
> > there but I have to link them to the macros. Sometimes the buttons aren't
> > there at all.
> >
> > I don't understand this and can't find anything to clarify it. Where are
> > the toolbars stored? Why can't the buttons just look in the current
> > workbook for the macros?
> >
> >

--

Dave Peterson

Very nice! Thanks!

I don't understand why the menumaker spreadsheet has no trouble finding the
Createmenu macro when the spreadsheet opens, but my toolbar buttons can't
find their macros when they are clicked.



"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On a toolbar, right?
>
> I think your life will be lots easier if you create that toolbar when the
> workbook opens and destroy it when the workbook closes.
>
> Some references if you want to look at that option:
>
> 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)
>
> And if you use xl2007:
>
> If you want to learn about modifying the ribbon, you can start at Ron de
> Bruin's
> site:
> http://www.rondebruin.nl/ribbon.htm
> http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved
> as an
> addin)
> or
> http://www.rondebruin.nl/2007addin.htm
>
> In xl2007, those toolbars and menu modifications will show up under the
> addins.
>
>
>
> BillE wrote:
>>
>> The buttons are on a toolbar, not a user form.
>>
>> "BillE" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > How do you distribute an Excel 2003 spreadsheet application with
>> > buttons
>> > linked to macros?
>> >
>> > Do you actually have to relink all buttons to the macros for every new
>> > location? I can't believe that - I must be overlooking something.
>> >
>> > My spreadsheet has buttons which are linked to macros.
>> >
>> > When the spreadsheet is moved to a different location (on the same
>> > computer or a new computer), the following error occurs when a button
>> > is
>> > clicked:
>> >
>> > "A document with the name fubar.xls is already open. You cannot
>> > open
>> > two documents with the same name etc."
>> >
>> > I moved all macros into the Sheet1 Excel Object. That didn't help.
>> >
>> > I have tried using buttons from the control toolbox (which I read
>> > should
>> > correct this but doesn't), and from the forms toolbox, and the "custom
>> > menu item" from the macros list. Nothing works.
>> >
>> > When I put the spreadsheet on another machine, sometimes the buttons
>> > are
>> > there but I have to link them to the macros. Sometimes the buttons
>> > aren't
>> > there at all.
>> >
>> > I don't understand this and can't find anything to clarify it. Where
>> > are
>> > the toolbars stored? Why can't the buttons just look in the current
>> > workbook for the macros?
>> >
>> >
>
> --
>
> Dave Peterson

The event that runs the createmenu procedure is in the ThisWorkbook module.
It's called Workbook_Open. That workbook_open event will run each time excel
opens the file (if you allow macros to run).



BillE wrote:
>
> Very nice! Thanks!
>
> I don't understand why the menumaker spreadsheet has no trouble finding the
> Createmenu macro when the spreadsheet opens, but my toolbar buttons can't
> find their macros when they are clicked.
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > On a toolbar, right?
> >
> > I think your life will be lots easier if you create that toolbar when the
> > workbook opens and destroy it when the workbook closes.
> >
> > Some references if you want to look at that option:
> >
> > 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)
> >
> > And if you use xl2007:
> >
> > If you want to learn about modifying the ribbon, you can start at Ron de
> > Bruin's
> > site:
> > http://www.rondebruin.nl/ribbon.htm
> > http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved
> > as an
> > addin)
> > or
> > http://www.rondebruin.nl/2007addin.htm
> >
> > In xl2007, those toolbars and menu modifications will show up under the
> > addins.
> >
> >
> >
> > BillE wrote:
> >>
> >> The buttons are on a toolbar, not a user form.
> >>
> >> "BillE" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > How do you distribute an Excel 2003 spreadsheet application with
> >> > buttons
> >> > linked to macros?
> >> >
> >> > Do you actually have to relink all buttons to the macros for every new
> >> > location? I can't believe that - I must be overlooking something.
> >> >
> >> > My spreadsheet has buttons which are linked to macros.
> >> >
> >> > When the spreadsheet is moved to a different location (on the same
> >> > computer or a new computer), the following error occurs when a button
> >> > is
> >> > clicked:
> >> >
> >> > "A document with the name fubar.xls is already open. You cannot
> >> > open
> >> > two documents with the same name etc."
> >> >
> >> > I moved all macros into the Sheet1 Excel Object. That didn't help.
> >> >
> >> > I have tried using buttons from the control toolbox (which I read
> >> > should
> >> > correct this but doesn't), and from the forms toolbox, and the "custom
> >> > menu item" from the macros list. Nothing works.
> >> >
> >> > When I put the spreadsheet on another machine, sometimes the buttons
> >> > are
> >> > there but I have to link them to the macros. Sometimes the buttons
> >> > aren't
> >> > there at all.
> >> >
> >> > I don't understand this and can't find anything to clarify it. Where
> >> > are
> >> > the toolbars stored? Why can't the buttons just look in the current
> >> > workbook for the macros?
> >> >
> >> >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson