Macros renaming with new workbook created from an .xlam add-in

[rdetreville]

Hello,

I've created a .xlam add-in tool that generates a report from a number of sources. It gathers data from various sources and then populates a new workbook "Book1". Then, it copies the hidden worksheet "MENU" from itself (the .xlam add-in file) which is a user interface menu for navigating the report. This MENU worksheet has VBA coding written in it. There are a number of textbox objects on this menu sheet which need to connect to these macros. Unfortunately, the Macro links won't stick because the macro name changes every time I run the add-in due to the sheet being renamed. I get the following error:

Even though the sheet is called "MENU", the macro names keep renaming themselves to the sheet number. For example, the sheet is now called Sheet3:

I've tried the following "worksheetactivate" code built into this "MENU" worksheet, but it doesn't work as the sheet number keeps changing. How can I link these text boxes to the correct macros? Is there a code that can create a new macro from string data and set it as a macro in a new module? Can I rename the sheet number using VBA? I tried to record a macro to rename the (Name) to Sheet99, but it didn't record anything.

Thanks for your help.

Richard

Private Sub Worksheet_Activate()

With Sheets("Menu")
.Shapes("Menubar").OnAction = "MENU.Menubar_Click"
.Shapes("butoverall").OnAction = "Sheet8.butoverall_Click"
.Shapes("butundis").OnAction = "Sheet8.butundis_Click"
.Shapes("butdisun").OnAction = "Sheet8.butdisun_Click"
.Shapes("butbusy").OnAction = "Sheet8.butbusy_Click"
.Shapes("butnotbusy").OnAction = "Sheet8.butnotbusy_Click"
.Shapes("butpmdate").OnAction = "Sheet8.butpmdate_Click"
.Shapes("butrevrec").OnAction = "Sheet8.butrevrec_Click"

.Shapes("trackeropen1").OnAction = "Sheet8.trackeropen1_Click"
.Shapes("trackeropen2").OnAction = "Sheet8.trackeropen2_Click"
.Shapes("trackeropen3").OnAction = "Sheet8.trackeropen3_Click"
End With


End Sub

 

[rdetreville]

Is there any way I can get the following to "ActiveWorkbook?"

Private Sub Worksheet_Activate()

With Sheets("Menu")
.Shapes("Menubar").OnAction = "Sheet8.Menubar_Click"

Or a way I can rename a Macro?

 

[rdetreville]

and I found the following, though this doesn't help since I don't know what the CodeName of the sheet will be each time it's created:

Sub AChangeOfName()
ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "Mudface"
'change the codename for Sheet1 to Mudface
End Sub

 

[rdetreville]

Or this??

vba - Writing a macro that writes a macro to another Excel-file - Stack Overflow

 

[rdetreville]

Going to try this...

Dim vbComp As Object
    Dim fName As String
        fName = "AddModule.bas"
        Application.VBE.ActiveVBProject.VBComponents("Modu  leX").Export fName
        Workbooks("WBToUpdate.xls").Activate
        ActiveWorkbook.VBProject.VBComponents.Import fName
    End Sub

 

[rdetreville]

I think I got it to work! I love answering my own questions haha

I put all the macros I need in a module called "CodeMove" and used the macro below to move that module from the .xlam file to my newly created workbook. I will test and report back.

Sub movecodes()
Dim vbComp As Object
Dim fName As String
fName = "CodeMove"
Application.VBE.ActiveVBProject.VBComponents("CodeMove").Export fName
ActiveWorkbook.VBProject.VBComponents.Import fName

End Sub

 

[rdetreville]

Worked like a charm! I'll leave this here for reference should anyone need it.