All geek questions in one place

Reassign Macro to Command Button when Copying a File

I have a problem in XL regarding overriding a macro assignment with a switch when the original worksheet is copied to the destination sheet in a new workbook.

When I click on the radio button on the "Destination" sheet, it tries to open the RATHER "Source" link to run the macro assigned to the button.

I checked, and in the destination book there really is an identical copy of the macro, like the source source book. However, by right-clicking / Assign Macro on the switch, I found that the STILL path points to the original workbook / worksheet.

I tried the following code while running it from the Source worksheet, but it does not work.

code:

wbkDESTINATION.Sheets("1-Start").Select
ActiveSheet.Shapes("Option Button 1").Select
Selection.OnAction = "Sheet1.WEEKLY"

I would appreciate your suggestion on how to change the last Selection.OnActioncode so that XL knows to select it on the addressee sheet, instead of returning to the original workbook.

thanks

+3
source share
2 answers

D'ah! Just realized that you are using Forms controls. You can make sure that you call the desired procedure by explicitly referring to the book, for example:

wbkDESTINATION.Sheets("1-Start").Select
ActiveSheet.Shapes("Option Button 1").Select
Selection.OnAction = ThisWorkbook.name & "!Sheet1.WEEKLY"

I think that although the controls appear in the Shapes collection, they are actually OLE objects with their own _click events in the worksheet code module and cannot have alternative procedures assigned to them through onaction. However, help can be vague in this important distinction.

; _click . ? .

0

Source: https://habr.com/ru/post/1706571/

More articles:


All Articles