Selecting the right module

[Pete81]

I have a code which exports a module from a source workbook ("Directory Creation Test.xlsm"), then imports it to a new workbook, assigning he module to a button in the new book. The problem I am having is that when I click the button, the code from the source workbook is used instead. I need the selection.onaction part of the code to select specifically the module in the new workbook. Here's what I have so far:

Dim FName As String

FName = Range("A1") & ".xlsm"

Workbooks.Add.SaveAs Filename:="C:\Folders\" & FName, FileFormat:=52


Workbooks("Directory Creation Test").VBProject.VBComponents("Module6").Export Filename:=("C:\Folders\Modules\ReturnData.bas")
Workbooks(FName).VBProject.VBComponents.Import ("C:\Folders\Modules\ReturnData.bas")

ActiveSheet.Buttons.Add(100, 100, 47.25, 47.25).Select
    Selection.OnAction = Workbooks(FName) & "!ReturnData2"
    Selection.Characters.Text = "Return Data"

Running this code, I get run-time error 438: Object doesn't support this property or method, and seems to indicate that the Selection.OnAction line is the issue. Where is my code wrong?

 

[Domenic]

Try...

[color=darkblue]With[/color] ActiveSheet.Buttons.Add(100, 100, 47.25, 47.25)
    .OnAction = "'" & FName & "'!ReturnData2"
    .Caption = "Return Data"
[color=darkblue]End[/color] [color=darkblue]With[/color]

Hope this helps!

 

[Pete81]

Try...

[color=darkblue]With[/color] ActiveSheet.Buttons.Add(100, 100, 47.25, 47.25)
    .OnAction = "'" & FName & "'!ReturnData2"
    .Caption = "Return Data"
[color=darkblue]End[/color] [color=darkblue]With[/color]

Hope this helps!

Hi Domenic,

It worked perfectly. Thank you so much. If you have the time, could you explain why it worked, and what the additional apostrophes mean in the code?

 

[Domenic]

In your original code, you're trying to concatenate an object (Workbooks(FName) returns a Workbook object) and a string, which you can't do. And then trying to assign it to the OnAction property, which requires a string. The apostrophes are added in case the filename contains spaces. Although, it looks like in this case they're automatically included when needed.