Re-assignment of Macro to Command button

**mrbundle** · 04-15-2009, 06:40 PM

Hello List

I have a problem in XL regarding redefining the Macro assignment of radio button when the source worksheet is copied to a Destination worksheet within a new workbook.

When i click on the radio button in the Destination worksheet, it attempt to open the Source workbook RATHER to run the macro assigned to the button.

I have checked and the Destination workbook DOES indeed have identical copy of the macro, as the original Source workbook. However, by Right clicking / Assign Macro on the radio button, i find that the path STILL points to the source workbook/worksheet.

I tried the following code, but it does not work.

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

I would appreciate your suggestion, how to modify the last Selection.OnAction code so that XL knows to pick it from the Destination worksheet, instead of jumping back to the original source workbook.

Thanks

**shg** · 04-15-2009, 07:21 PM

Welcome to the forum, mrbundle.

Please take a few minutes to read the forum rules, and then edit your post to add code tags.

**mrbundle** · 04-15-2009, 09:29 PM

Hello again List

Sorry, i ended up replying to my own post, while i was trying to edit it!

**shg** · 04-15-2009, 09:48 PM

This works fine for me, assigning a macro and then saving as a different name:

Sub x()
    Sheets("1-Start").Shapes("Option Button 1").OnAction = "Sheet1.WEEKLY"
End Sub

**mrbundle** · 04-16-2009, 10:21 AM

Hello again

I have checked the copied worksheet in the Destination workbook and it
DOES indeed have an identical copy of the macro, as the original
Source workbsheet/workbook. However, upon by Right clicking / Assign
Macro on the radio button in the Destination worksheet, i find that
the path STILL points to the source workbook/worksheet.

I tried the following 2 options of codes, but neither seems to work.

VBA Code:
'Re-assign/point Radio buttons to Macro copy with New Workbook

CODE 1

wbkNew.Worksheets(1).Activate
  'ActiveSheet.Shapes("Option Button 1").Select
  'Selection.OnAction = "Sheet1.DAILY" OR  'Selection.OnAction = "DAILY"
  Sheets("(1 Start)").Shapes("Option Button 2").OnAction = "Sheet1.DAILY"

CODE 2

wbkNew.Worksheets(1).Activate
  Sheets("(1 Start)").Shapes("Option Button 2").OnAction = "Sheet1.DAILY"

It's pretty frustrating especially when i have a deadline to do this.
Your suggestions would be greatly appreciated as to how to re-assign /
get the new worksheet OPTION button to point to the macro "Daily"
residing within the new worksheet. ( i tried putting the "Daily" macro
in a module instead of the new worksheet "sheet1" but still wont work.... :-(

**xling** · 05-04-2009, 03:58 PM

I had the same problem and based upon your code plus a couple of other sites came up with this:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim Action_name As String

Action_name = "'" & ActiveWorkbook.Name & "'*"

    If Sh.Name Like "Base_sheet_name*" Then
        If Not ((ActiveSheet.Shapes.Item(2).OnAction Like Action_name) And (ActiveSheet.Shapes.Item(2).OnAction Like Action_name)) Then
            MsgBox ActiveSheet.Shapes.Item(2).OnAction & ActiveSheet.Shapes.Item(1).OnAction
            ActiveSheet.Shapes.Item(1).OnAction = "ThisWorkbook.Macro1"
            ActiveSheet.Shapes.Item(2).OnAction = "ThisWorkbook.Macro2"
            MsgBox ActiveSheet.Shapes.Item(2).OnAction & ActiveSheet.Shapes.Item(1).OnAction
        End If
    End If
    
End Sub

This is for copying into a similar work book and checking two buttons for the correct call reference. Kept a couple of debug statements in it.

Hope this helps.

xling