Programmatically assign macro button

[rex759]

Hello,
What I’m trying to do is assign a macro to a button after it’s been copied from the source workbook.

The module name in the source workbook is “hide”
The macro name is “formatPrint”

I get one of two scenarios
-the button links to the Source workbook instead of the Temp workbook
-the button doesn’t link to anything.

Here’s a snippet of the code with both scenarios

'Copy the "HIDE" macro to new workbook
Const MODULE_NAME    As String = "hide"         ' Name of the module to transfer
Const TEMPFILE2       As String = "c:\Modul.bas" ' temp textfile
  
   '** export the module to a textfile
  Workbooks(mydatafile).VBProject.VBComponents(MODULE_NAME).Export TEMPFILE2
   'import the module to the new workbook
  Workbooks(TEMPFILE).VBProject.VBComponents.Import TEMPFILE2
   'kill the textfile
   Kill TEMPFILE2

‘++++++++++++++++++++
‘This  method doesn’t assign anything to the button
With Workbooks(TEMPFILE).Worksheets("sheet1").Buttons.Add(Range("a1").Top, Range("a1").Left, 89.25, 23.25)
.Name = "formatPrint"
.Caption = "Format to Print"
.OnAction = Workbooks(TEMPFILE).Name & "!formatPrint"
End With

‘++++++++++++++++++++
‘This  method  assigns the code from the source workbook
'Add macro button
Workbooks(TEMPFILE).Worksheets("sheet1").Buttons.Add(Range("a1").Top, Range("a1").Left, 89.25, 23.25).Select
'
Workbooks(TEMPFILE).Worksheets("sheet1").Select
ActiveSheet.Shapes("option button 1").Select
Selection.OnAction = ThisWorkbook.Name & "!printFormat"

Any ideas ?

 

[JoeMo]

Your two methods have a typo in one of the OnAction statements: printFormat should be formatPrint or vice-versa.

 

[rex759]

Sorry about that. It should be "formatPrint".

 

[JoeMo]

Then your second method might work if you make that change.

 

[rex759]

I tired it, didnt work.

 

[DeckMaster]

I am still new to VBA, so I'm having a little trouble following your code. However, I was able to programmatically assign a macro to a button in a new workbook using the following code.

Here, ThisWorkbook refers to the workbook in which the macro that you're running is housed.
NewWkbk refers to the new workbook that this macro has programmatically created.

I first create the workbook using a different code from yours. This code also assigns this workbook to the variable NewWkbk. I'm sure there's another way to assign the workbook to a variable, without adding a new workbook, but I don't know it.

Then the code "ActiveSheet.Buttons.Add(230.25, 57, 153, 96.75).Select" creates a new button, and the following line assigns the macro within that workbook. I hope this helps.

Dim FName As String
With ThisWorkbook
FName = .Path & "\code.txt"
.VBProject.VBComponents(MODULE_NAME).Export FName
End With

Set NewWkbk = Workbooks.Add

NewWkbk.VBProject.VBComponents.Import FName
    
NewWkbk.Activate
    ActiveSheet.Buttons.Add(230.25, 57, 153, 96.75).Select
    Selection.OnAction = NewWkbk.Name & "!formatPrint"

 

[rex759]

Thanks for your help. I'll give it a try.