Excel XP & Win XP
Everything below is done via VBA.
I have a workbook, say A.xls, that has a number of macros and a number of
buttons here and there to trigger the macros. All the macros assigned to
the buttons are in that workbook.
I do some work with this workbook.
Then I SaveAs as B.xls.
In the B.xls workbook, the macro assignments all reference A.xls as the
location of the macros.
I want the macro assignments in B.xls to reference B.xls.
Is there some code that I can employ at the time of the SaveAs to carry the
macro assignments to the new workbook?
If not, what would be the best way to change the macro assignments to B.xls
after B.xls is created?
Thanks for your time. Otto

do you have hard coded references to A.xls?

if so, change them to thisworkbook.

don't know without seeing an example of the code that's in question.

--


Gary

"Otto Moehrbach" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Excel XP & Win XP
> Everything below is done via VBA.
> I have a workbook, say A.xls, that has a number of macros and a number of
> buttons here and there to trigger the macros. All the macros assigned to the
> buttons are in that workbook.
> I do some work with this workbook.
> Then I SaveAs as B.xls.
> In the B.xls workbook, the macro assignments all reference A.xls as the
> location of the macros.
> I want the macro assignments in B.xls to reference B.xls.
> Is there some code that I can employ at the time of the SaveAs to carry the
> macro assignments to the new workbook?
> If not, what would be the best way to change the macro assignments to B.xls
> after B.xls is created?
> Thanks for your time. Otto
>

Gary
Thanks for your help. In the A.xls WB the button assignment is simply
"MacroName". In the B.xls WB the assignment is "A.xls!MacroName". I want
the assignment to be simply MacroName in both workbooks so that any button
clicked on will run a macro that is in the WB that holds that button.
Thanks again. Otto
"Gary Keramidas" <GKeramidasAtMsn.com> wrote in message
news:(E-Mail Removed)...
> do you have hard coded references to A.xls?
>
> if so, change them to thisworkbook.
>
> don't know without seeing an example of the code that's in question.
>
> --
>
>
> Gary
>
> "Otto Moehrbach" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> Excel XP & Win XP
>> Everything below is done via VBA.
>> I have a workbook, say A.xls, that has a number of macros and a number of
>> buttons here and there to trigger the macros. All the macros assigned to
>> the buttons are in that workbook.
>> I do some work with this workbook.
>> Then I SaveAs as B.xls.
>> In the B.xls workbook, the macro assignments all reference A.xls as the
>> location of the macros.
>> I want the macro assignments in B.xls to reference B.xls.
>> Is there some code that I can employ at the time of the SaveAs to carry
>> the macro assignments to the new workbook?
>> If not, what would be the best way to change the macro assignments to
>> B.xls after B.xls is created?
>> Thanks for your time. Otto
>>
>
>

Joel
I wasn't clear. I have a workbook A.xls with macros and buttons to
execute the macros. With VBA I do a SaveAs and name it B.xls. A.xls is now
closed. I click a button in B.xls. I get an error because VBA is looking
for A.xls!MacroName. I want the macro that is assigned to a button in B.xls
to be a macro that is in WB B.xls. Otto
"Joel" <(E-Mail Removed)> wrote in message
news:27C3FBDE-4BEE-4C09-8FAD-(E-Mail Removed)...
> To reference the workbook with the macro make the following change
>
> from
> workbooks("A.xls")
>
> to
> ThisWorkbook
>
> "Otto Moehrbach" wrote:
>
>> Excel XP & Win XP
>> Everything below is done via VBA.
>> I have a workbook, say A.xls, that has a number of macros and a number of
>> buttons here and there to trigger the macros. All the macros assigned to
>> the buttons are in that workbook.
>> I do some work with this workbook.
>> Then I SaveAs as B.xls.
>> In the B.xls workbook, the macro assignments all reference A.xls as the
>> location of the macros.
>> I want the macro assignments in B.xls to reference B.xls.
>> Is there some code that I can employ at the time of the SaveAs to carry
>> the
>> macro assignments to the new workbook?
>> If not, what would be the best way to change the macro assignments to
>> B.xls
>> after B.xls is created?
>> Thanks for your time. Otto
>>
>>
>>

You could loop through all the buttons/objects on the sheet and look at the
..onaction string. Then change the oldname.xls to the newname.xls
(thisworkbook.name???).

But if you're only using buttons from the Forms toolbar, you may find it much
easier to replace them with the Commandbuttons from the control toolbox toolbar.

Those objects don't have macros assigned to them. They have events and those
event procedures live under the worksheets that own the commandbuttons.



Otto Moehrbach wrote:
>
> Excel XP & Win XP
> Everything below is done via VBA.
> I have a workbook, say A.xls, that has a number of macros and a number of
> buttons here and there to trigger the macros. All the macros assigned to
> the buttons are in that workbook.
> I do some work with this workbook.
> Then I SaveAs as B.xls.
> In the B.xls workbook, the macro assignments all reference A.xls as the
> location of the macros.
> I want the macro assignments in B.xls to reference B.xls.
> Is there some code that I can employ at the time of the SaveAs to carry the
> macro assignments to the new workbook?
> If not, what would be the best way to change the macro assignments to B.xls
> after B.xls is created?
> Thanks for your time. Otto

--

Dave Peterson

Thanks Dave. That sounds like a good idea. Otto
"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> You could loop through all the buttons/objects on the sheet and look at
> the
> .onaction string. Then change the oldname.xls to the newname.xls
> (thisworkbook.name???).
>
> But if you're only using buttons from the Forms toolbar, you may find it
> much
> easier to replace them with the Commandbuttons from the control toolbox
> toolbar.
>
> Those objects don't have macros assigned to them. They have events and
> those
> event procedures live under the worksheets that own the commandbuttons.
>
>
>
> Otto Moehrbach wrote:
>>
>> Excel XP & Win XP
>> Everything below is done via VBA.
>> I have a workbook, say A.xls, that has a number of macros and a number of
>> buttons here and there to trigger the macros. All the macros assigned to
>> the buttons are in that workbook.
>> I do some work with this workbook.
>> Then I SaveAs as B.xls.
>> In the B.xls workbook, the macro assignments all reference A.xls as the
>> location of the macros.
>> I want the macro assignments in B.xls to reference B.xls.
>> Is there some code that I can employ at the time of the SaveAs to carry
>> the
>> macro assignments to the new workbook?
>> If not, what would be the best way to change the macro assignments to
>> B.xls
>> after B.xls is created?
>> Thanks for your time. Otto
>
> --
>
> Dave Peterson