custom toolbar button questions

[Gary Keramidas]

i created a user form in my master workbook. created a new macro button to
run it. i then exported the user form and imported it into the client's
workbook. when i click the macro button, it opens my workbook and runs the
form.

so, i have 2 questions:

1. how do i edit the custom button to assign which macro it runs? ended up
deleting it and creating a new one.

2. how do i specify in the code to run show the userform from the current
workbook?

i tired thisworkbook.userform1.show and activeworkbook.userform1.show.



--


Gary

[Tom Ogilvy]

Code can't run userforms in other workbooks without taking extraordinary
measures, so it should use the userform in that workbook.

tools=>Customize,
This puts Excel in a design mode for commandbars. Leave the dialog showing,
then go to your button and right click on it. Select Assign macro. Change
the setting for what macro to run.


In code, you can do something like

Application.Commandbars("Custom 1").Controls(3).OnAction =
"MyWorkbooks.xls!MyMacro"

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:[email protected]...
> i created a user form in my master workbook. created a new macro button to
> run it. i then exported the user form and imported it into the client's
> workbook. when i click the macro button, it opens my workbook and runs the
> form.
>
> so, i have 2 questions:
>
> 1. how do i edit the custom button to assign which macro it runs? ended up
> deleting it and creating a new one.
>
> 2. how do i specify in the code to run show the userform from the current
> workbook?
>
> i tired thisworkbook.userform1.show and activeworkbook.userform1.show.
>
>
>
> --
>
>
> Gary
>
>
>

[Gary Keramidas]

ok, thanks. this is why i needed to change the macro, it was pointing to the
other workbook

'N:\My Documents\Excel\RECCU\FSA\Blank1.xls'!Run_Form

it would load this workbook and then run the form
--


Gary


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Code can't run userforms in other workbooks without taking extraordinary
> measures, so it should use the userform in that workbook.
>
> tools=>Customize,
> This puts Excel in a design mode for commandbars. Leave the dialog
> showing,
> then go to your button and right click on it. Select Assign macro.
> Change
> the setting for what macro to run.
>
>
> In code, you can do something like
>
> Application.Commandbars("Custom 1").Controls(3).OnAction =
> "MyWorkbooks.xls!MyMacro"
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Gary Keramidas" <GKeramidasATmsn.com> wrote in message
> news:[email protected]...
>> i created a user form in my master workbook. created a new macro button
>> to
>> run it. i then exported the user form and imported it into the client's
>> workbook. when i click the macro button, it opens my workbook and runs
>> the
>> form.
>>
>> so, i have 2 questions:
>>
>> 1. how do i edit the custom button to assign which macro it runs? ended
>> up
>> deleting it and creating a new one.
>>
>> 2. how do i specify in the code to run show the userform from the current
>> workbook?
>>
>> i tired thisworkbook.userform1.show and activeworkbook.userform1.show.
>>
>>
>>
>> --
>>
>>
>> Gary
>>
>>
>>
>
>

[Gary Keramidas]

i guess what happens, is no matter which macro i assign to the custom
button, it always references that workbook and that macro from which it was
installed. the button is part of "my excel" configuration, so no matter
which workbook i open, that button points to the workbook and macro assigned
to it. i would have to go to each computer that i want to access the form
and create a custom button. but then when that user moved to another
computer, it would create a problem again,

what's the best way to activate the form in each user's workbook, i don't
want it to load unless they click a button and i didn't want to create a
command button on each sheet.

--


Gary


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Code can't run userforms in other workbooks without taking extraordinary
> measures, so it should use the userform in that workbook.
>
> tools=>Customize,
> This puts Excel in a design mode for commandbars. Leave the dialog
> showing,
> then go to your button and right click on it. Select Assign macro.
> Change
> the setting for what macro to run.
>
>
> In code, you can do something like
>
> Application.Commandbars("Custom 1").Controls(3).OnAction =
> "MyWorkbooks.xls!MyMacro"
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Gary Keramidas" <GKeramidasATmsn.com> wrote in message
> news:[email protected]...
>> i created a user form in my master workbook. created a new macro button
>> to
>> run it. i then exported the user form and imported it into the client's
>> workbook. when i click the macro button, it opens my workbook and runs
>> the
>> form.
>>
>> so, i have 2 questions:
>>
>> 1. how do i edit the custom button to assign which macro it runs? ended
>> up
>> deleting it and creating a new one.
>>
>> 2. how do i specify in the code to run show the userform from the current
>> workbook?
>>
>> i tired thisworkbook.userform1.show and activeworkbook.userform1.show.
>>
>>
>>
>> --
>>
>>
>> Gary
>>
>>
>>
>
>

[Tom Ogilvy]

I interpreted you to run a macro in the new workbook containing the imported
form

But You want to run the imported form form a different workbook.

This is the situation that requires extraordinary measures. Extraordinary
measures means you have to have code in the users workbook that displays the
userform in the users workbook. If you want that, then it would be easier
to use the onaction property of the commandbar button to reassign the button
to that workbook when it is active using the workbook.activate or
worksheets.activate events.

Or
you need to modify the Master form to run from the master workbook ('N:\My
Documents\Excel\RECCU\FSA\Blank1.xls') but have the userform and its code
designed to work with data in the users workbook.

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:[email protected]...
> i guess what happens, is no matter which macro i assign to the custom
> button, it always references that workbook and that macro from which it
was
> installed. the button is part of "my excel" configuration, so no matter
> which workbook i open, that button points to the workbook and macro
assigned
> to it. i would have to go to each computer that i want to access the form
> and create a custom button. but then when that user moved to another
> computer, it would create a problem again,
>
> what's the best way to activate the form in each user's workbook, i don't
> want it to load unless they click a button and i didn't want to create a
> command button on each sheet.
>
> --
>
>
> Gary
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > Code can't run userforms in other workbooks without taking extraordinary
> > measures, so it should use the userform in that workbook.
> >
> > tools=>Customize,
> > This puts Excel in a design mode for commandbars. Leave the dialog
> > showing,
> > then go to your button and right click on it. Select Assign macro.
> > Change
> > the setting for what macro to run.
> >
> >
> > In code, you can do something like
> >
> > Application.Commandbars("Custom 1").Controls(3).OnAction =
> > "MyWorkbooks.xls!MyMacro"
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Gary Keramidas" <GKeramidasATmsn.com> wrote in message
> > news:[email protected]...
> >> i created a user form in my master workbook. created a new macro button
> >> to
> >> run it. i then exported the user form and imported it into the client's
> >> workbook. when i click the macro button, it opens my workbook and runs
> >> the
> >> form.
> >>
> >> so, i have 2 questions:
> >>
> >> 1. how do i edit the custom button to assign which macro it runs? ended
> >> up
> >> deleting it and creating a new one.
> >>
> >> 2. how do i specify in the code to run show the userform from the
current
> >> workbook?
> >>
> >> i tired thisworkbook.userform1.show and activeworkbook.userform1.show.
> >>
> >>
> >>
> >> --
> >>
> >>
> >> Gary
> >>
> >>
> >>
> >
> >
>
>

[Gary Keramidas]

ok, here's the scenario.

there are 10 workbooks, all will have the userform code. the 10 users, may
use different computers each day, so whatever activates the form needs to be
within the workbook, so it uses the user form in that workbook.

the userform is used to input data on 12 sheets, one for each month. i don't
want the userform to display automatically, just if they want to use it. so
a toolbar button seemed like a good way to go, i'd only have to add a
toolbar button on all of the pc's. but like i mentioned, assigning a macro
to that button, follows the workbook.

i could put a command button on all 120 sheets, but don't really want to do
that. i figure there has to be a way to assign a macro to a toolbar button,
even if it's a custom toolbar, that always points to the open workbooks
run_form macro, but i could be wrong.

--


Gary


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
>I interpreted you to run a macro in the new workbook containing the
>imported
> form
>
> But You want to run the imported form form a different workbook.
>
> This is the situation that requires extraordinary measures.
> Extraordinary
> measures means you have to have code in the users workbook that displays
> the
> userform in the users workbook. If you want that, then it would be easier
> to use the onaction property of the commandbar button to reassign the
> button
> to that workbook when it is active using the workbook.activate or
> worksheets.activate events.
>
> Or
> you need to modify the Master form to run from the master workbook ('N:\My
> Documents\Excel\RECCU\FSA\Blank1.xls') but have the userform and its code
> designed to work with data in the users workbook.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Gary Keramidas" <GKeramidasATmsn.com> wrote in message
> news:[email protected]...
>> i guess what happens, is no matter which macro i assign to the custom
>> button, it always references that workbook and that macro from which it
> was
>> installed. the button is part of "my excel" configuration, so no matter
>> which workbook i open, that button points to the workbook and macro
> assigned
>> to it. i would have to go to each computer that i want to access the
>> form
>> and create a custom button. but then when that user moved to another
>> computer, it would create a problem again,
>>
>> what's the best way to activate the form in each user's workbook, i don't
>> want it to load unless they click a button and i didn't want to create a
>> command button on each sheet.
>>
>> --
>>
>>
>> Gary
>>
>>
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:[email protected]...
>> > Code can't run userforms in other workbooks without taking
>> > extraordinary
>> > measures, so it should use the userform in that workbook.
>> >
>> > tools=>Customize,
>> > This puts Excel in a design mode for commandbars. Leave the dialog
>> > showing,
>> > then go to your button and right click on it. Select Assign macro.
>> > Change
>> > the setting for what macro to run.
>> >
>> >
>> > In code, you can do something like
>> >
>> > Application.Commandbars("Custom 1").Controls(3).OnAction =
>> > "MyWorkbooks.xls!MyMacro"
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> > "Gary Keramidas" <GKeramidasATmsn.com> wrote in message
>> > news:[email protected]...
>> >> i created a user form in my master workbook. created a new macro
>> >> button
>> >> to
>> >> run it. i then exported the user form and imported it into the
>> >> client's
>> >> workbook. when i click the macro button, it opens my workbook and runs
>> >> the
>> >> form.
>> >>
>> >> so, i have 2 questions:
>> >>
>> >> 1. how do i edit the custom button to assign which macro it runs?
>> >> ended
>> >> up
>> >> deleting it and creating a new one.
>> >>
>> >> 2. how do i specify in the code to run show the userform from the
> current
>> >> workbook?
>> >>
>> >> i tired thisworkbook.userform1.show and activeworkbook.userform1.show.
>> >>
>> >>
>> >>
>> >> --
>> >>
>> >>
>> >> Gary
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Gary Keramidas]

tried your idea, changed custom 1 to custom button (that's what it says the
name is anyway), would not work. no name works other than button, but that
works no matter what my custom button's name is.

i guess i just need a way to assign that code to the custom button on the
toolbar when the workbook opens.

--


Gary


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Code can't run userforms in other workbooks without taking extraordinary
> measures, so it should use the userform in that workbook.
>
> tools=>Customize,
> This puts Excel in a design mode for commandbars. Leave the dialog
> showing,
> then go to your button and right click on it. Select Assign macro.
> Change
> the setting for what macro to run.
>
>
> In code, you can do something like
>
> Application.Commandbars("Custom 1").Controls(3).OnAction =
> "MyWorkbooks.xls!MyMacro"
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Gary Keramidas" <GKeramidasATmsn.com> wrote in message
> news:[email protected]...
>> i created a user form in my master workbook. created a new macro button
>> to
>> run it. i then exported the user form and imported it into the client's
>> workbook. when i click the macro button, it opens my workbook and runs
>> the
>> form.
>>
>> so, i have 2 questions:
>>
>> 1. how do i edit the custom button to assign which macro it runs? ended
>> up
>> deleting it and creating a new one.
>>
>> 2. how do i specify in the code to run show the userform from the current
>> workbook?
>>
>> i tired thisworkbook.userform1.show and activeworkbook.userform1.show.
>>
>>
>>
>> --
>>
>>
>> Gary
>>
>>
>>
>
>

[Tom Ogilvy]

"Custom 1" was an example name of a custom floating commandbar.

Controls(3) referred to the 3rd control/button on that commandbar.

--
Regards,
Tom Ogilvy

"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:%[email protected]...
> tried your idea, changed custom 1 to custom button (that's what it says
the
> name is anyway), would not work. no name works other than button, but that
> works no matter what my custom button's name is.
>
> i guess i just need a way to assign that code to the custom button on the
> toolbar when the workbook opens.
>
> --
>
>
> Gary
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > Code can't run userforms in other workbooks without taking extraordinary
> > measures, so it should use the userform in that workbook.
> >
> > tools=>Customize,
> > This puts Excel in a design mode for commandbars. Leave the dialog
> > showing,
> > then go to your button and right click on it. Select Assign macro.
> > Change
> > the setting for what macro to run.
> >
> >
> > In code, you can do something like
> >
> > Application.Commandbars("Custom 1").Controls(3).OnAction =
> > "MyWorkbooks.xls!MyMacro"
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Gary Keramidas" <GKeramidasATmsn.com> wrote in message
> > news:[email protected]...
> >> i created a user form in my master workbook. created a new macro button
> >> to
> >> run it. i then exported the user form and imported it into the client's
> >> workbook. when i click the macro button, it opens my workbook and runs
> >> the
> >> form.
> >>
> >> so, i have 2 questions:
> >>
> >> 1. how do i edit the custom button to assign which macro it runs? ended
> >> up
> >> deleting it and creating a new one.
> >>
> >> 2. how do i specify in the code to run show the userform from the
current
> >> workbook?
> >>
> >> i tired thisworkbook.userform1.show and activeworkbook.userform1.show.
> >>
> >>
> >>
> >> --
> >>
> >>
> >> Gary
> >>
> >>
> >>
> >
> >
>
>

[Gary Keramidas]

thanks, i'm just putting a macro button on each sheet. then they can click
on it when they want it.

--


Gary


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> "Custom 1" was an example name of a custom floating commandbar.
>
> Controls(3) referred to the 3rd control/button on that commandbar.
>
> --
> Regards,
> Tom Ogilvy
>
> "Gary Keramidas" <GKeramidasATmsn.com> wrote in message
> news:%[email protected]...
>> tried your idea, changed custom 1 to custom button (that's what it says
> the
>> name is anyway), would not work. no name works other than button, but
>> that
>> works no matter what my custom button's name is.
>>
>> i guess i just need a way to assign that code to the custom button on the
>> toolbar when the workbook opens.
>>
>> --
>>
>>
>> Gary
>>
>>
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:[email protected]...
>> > Code can't run userforms in other workbooks without taking
>> > extraordinary
>> > measures, so it should use the userform in that workbook.
>> >
>> > tools=>Customize,
>> > This puts Excel in a design mode for commandbars. Leave the dialog
>> > showing,
>> > then go to your button and right click on it. Select Assign macro.
>> > Change
>> > the setting for what macro to run.
>> >
>> >
>> > In code, you can do something like
>> >
>> > Application.Commandbars("Custom 1").Controls(3).OnAction =
>> > "MyWorkbooks.xls!MyMacro"
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> > "Gary Keramidas" <GKeramidasATmsn.com> wrote in message
>> > news:[email protected]...
>> >> i created a user form in my master workbook. created a new macro
>> >> button
>> >> to
>> >> run it. i then exported the user form and imported it into the
>> >> client's
>> >> workbook. when i click the macro button, it opens my workbook and runs
>> >> the
>> >> form.
>> >>
>> >> so, i have 2 questions:
>> >>
>> >> 1. how do i edit the custom button to assign which macro it runs?
>> >> ended
>> >> up
>> >> deleting it and creating a new one.
>> >>
>> >> 2. how do i specify in the code to run show the userform from the
> current
>> >> workbook?
>> >>
>> >> i tired thisworkbook.userform1.show and activeworkbook.userform1.show.
>> >>
>> >>
>> >>
>> >> --
>> >>
>> >>
>> >> Gary
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>