Using VBA to assign a macro to a button 1

[cosmogramma]

I posted a few days ago about an Excel template I've created. The template creates several new excel spreadsheets with data for different users, and I wish to transfer a button and a print formatting macro to these sheets.

In my code I have the following two statements to import the Module with the macro, and assign to the button.

newWbk.VBProject.VBComponents.Import ("W:\XXXX\Module1.bas")
newSht.Shapes("Button 1").OnAction = "PrintFormatted"

For some reason when the new spreadsheet is opened, it's still referencing the original template sheet. I can manually reassign the button the the imported macro and it works fine. Is there a way to automate this as I'm creating the new excel files though?

 

[SkipVought]

Hi,

Do you think that maybe there might be, perhaps, something in the code?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cosmogramma]

I'm not sure what you mean by something in the code.

I know that when I do get the new spreadsheet, and send it to another machine that button still referances the template code. When you right click and reassign the Printing Macro to the button, it works fine.

So the macro is being imported correctly, and the button is transfered to the new spreadsheet correctly. Somehow my assigning the code is not working.

 

[cosmogramma]

I've tried breaking it up into a couple of statements

newWbk.VBProject.VBComponents.Import("W:\XXXX\Module1.bas")
newWbk.Sheets(outShtName).Shapes("Button 1").Select
Selection.OnAction = "PrintFormatted"

When I single step through it, the Import of the module works fine, and selecting Button 1 works fine. When I try to assign "PrintFormatted" (definted in Module1.bas) I get:

Run-time error '438': Object doesn't support this property or Method.

Any suggestions?

 

[SkipVought]

Try this...

newSht.Shapes("Button 1").OnAction = [b]newWbk.Name & "!" & [/b]"Button1_Click"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cosmogramma]

BINGO!!! Thanks!

It gave me some kind of error about not having Button1_Click, but I just ranamed the Print code and it worked.

Is Button1_Click a specific method I need to assign code, or were you just using that as an example of what my code could be named?

 

[SkipVought]

That's the default code stub that Excel assigns to a button macro.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}