Macro to assign Macro to form control button is not working

[LinkiePoo]

The macro below is supposed to assign an existing macro in each workbook to the form control button. The existing macro is called "Mail_Sheet_Outlook_Body". However, when I open the workbooks, the button is actually pointing to the "Assign_Button" macro in my c:drive, it's not pointing to the Mail_Sheet_Outlook_Body macro that's actually in the workbook. So, when the button is clicked, it gives an error and tries to open the "Assign_Button" file from the c:drive.

Any suggestions or help is appreciated! Thanks!

Sub Assign_Button()
    Dim FolderPath As String
    Dim FileName As String
    Dim wbThisWorkBk As Workbook
    Dim wbThisWB As Workbook
    Set wbThisWB = ActiveWorkbook

    Application.ScreenUpdating = False
    
    FolderPath = "c:\users\documents\Test\"
    
    FileName = Dir(FolderPath & "*.xl*")
        Do While FileName <> ""
        Set wbThisWB = Workbooks.Open(FolderPath & FileName)
     
    Worksheets("USD").Buttons("Button 1").OnAction = "Mail_Sheet_Outlook_Body"
    
    Range("A2").Select
    
    wbThisWB.Close savechanges:=True
        FileName = Dir()
    Loop
  
    Application.ScreenUpdating = True
    MsgBox ("Done!")
End Sub

 

[ChrisM]

Can you try tightening up your workbook references to make sure you are working in the right book? In the two lines below, are you wanting to modify the original workbook you have open, or the second one you just opened? I'm assuming it's the second?

Worksheets("USD").Buttons("Button 1").OnAction = "Mail_Sheet_Outlook_Body"

Range("A2").Select

Change to:

wbThisWB.Worksheets("USD").Buttons("Button 1").OnAction = "Mail_Sheet_Outlook_Body"

wbThisWB.Worksheets("xxxxx").Range("A2").Select

Where is the Mail_Sheet code located? a Module, ThisWorkbook, in a Sheet module? You might need to put a prefix on it, like "ThisWorkbook!Mail_Sheet_Outlook_Body"

 

[LinkiePoo]

I tried what you suggested. It's not working. Basically, I have a macro that opens 180 workbooks (each of these 180 workbooks already has the Mail_Sheet module in them). When opened, I want to assign the Mail_Sheet code to the button that exists in that workbook. The mail_sheet is in Module1.

Any other suggestions?

 

[ChrisM]

Hmm, how about:

wbThisWB.Worksheets("USD").Buttons("Button 1").OnAction = wbThisWb.Name & "!Mail_Sheet_Outlook_Body"

 

[LinkiePoo]

That gives me the error of "Unable to set the OnAction property of the Button class.

 

[ChrisM]

Ok, you are forcing me to get serious! I just duplicated your set up with two files. One blank file contains the assign_code macro, and the other file is on my C: drive, and it contains a blank button (no macro assigned) and the email macro code in an inserted Module.

The following code successfully ran:

Sub Assign_Button()

Dim wbThisWB As Workbook

Set wbThisWB = Workbooks.Open("C:\Book2.xlsm")
  
wbThisWB.Worksheets("Sheet1").Buttons("Button 1").OnAction = wbThisWB.Name & "!Mail_Sheet_Outlook_Body"
 
wbThisWB.Close savechanges:=True
     
MsgBox ("Done!")

Set wbThisWB = Nothing

End Sub

So there's a couple small differences between our codes, but I'm not sure why yours is not working!

 

[LinkiePoo]

I used your code exactly and just changed Book2 to an actual file name. I still got the unable to set the OnAction property of the Button class. I'll keep trying different things. Thanks for your help!!

 

[ChrisM]

Are you files in xlsm format?

 

[LinkiePoo]

Yes, they are .xlsm files.