From Personal.xlsb, set .onaction macro to a macro in a worksheet in a differert workbook.

[ijourneaux]

From personal.xlsb, I am trying to create a button in the calling workbook and assign a macro that exists in one of the worksheet in the calling workbook.

All of the combinations I have don't produce a macro that is correctly defined. I thought this was going to work since this is exactly how the macro is listed when you browse the assign macro dialog.

.OnAction = "'" & Workbooks(WorkbookName).Worksheets("CenterLine 2").CodeName & "'" & "!CenterLine2Auto_Button_Click"

I expected this
Sheet4.CenterLine2Auto_Button_Click

but got this
'C:\Users\userid\drive\Documents\Sheet4'!CenterLine2Auto_Button_Click

that doesn't work.

I tried
.OnAction = "'" & WorkbookName & "'" & "!CenterLine2Auto_Button_Click"
whicc gave this

'Process Monitor ver4.xlsm'!CenterLine2Auto_Button_Click

but that did not work either.

 

[RoryA]

Is there a reason the macro is in a worksheet code module rather than a normal module where it would seem to belong?

 

[ijourneaux]

Good question and I don't have a good answer. Why is a regular code module the correct place and when would you use the worksheet module?

 

[RoryA]

Generally speaking, in my opinion, code in a worksheet module would only be event code or a routine that is specific to, and only called from code in, that worksheet. Code that needs to be called from more than one place, or from the macros dialog or shapes, should be in a normal module.

 

[ijourneaux]

Seems reasonable. I will go try and see if moving the macro allows me to set the onAction shortly.

 

[ijourneaux]

This ended up working.
.OnAction = "'" & WorkbookName & "'" & "!CenterLine2Auto_Button_Click"