Macro to assign a Macro

[jordan2322]

Hi guys,

I have a macro that prompts the sheet user to select the reports to 'load'. This opens up an external workbook, copies the data, then shuts the workbook down, and inserts command buttons onto the worksheet where the data was copied to.
Is it possible to assign a code to these command buttons that doesnt reside in the worksheet. i.e. can i have hte code in a module then assign to each of these newly created command buttons my macro all in the same operation?
the macro i wish to assign is below

Sub HideCrews()

    Dim i As Integer
    Dim j As String
    'Dim k As Integer
    Dim Col As String
    Dim Sht As String
    Dim This
    
    Application.ScreenUpdating = False
    
    Col = Sheets("Data").Cells(8, 1).Value
    
    Sht = ActiveSheet.Name
    
    Sheets("Data").Visible = True
    Sheets("Data").Select
    Sheets("Data").Range(Col & "3").Select
    Selection.End(xlDown).Select
    j = ActiveCell.Row
    
    For i = 3 To j
    Sheets(Sht).Select
    If (Sheets("Data").Cells(i, Col).Offset(, 1).Value = "0") Then
    Sheets(Sht).Range(Sheets("Data").Range(Col & i)).EntireRow.Hidden = True
    End If
    Next i

    Application.ScreenUpdating = True
    
End Sub

Sub ShowCrews()
    Application.ScreenUpdating = False
    Cells.Select
    Selection.EntireRow.Hidden = False
    Range("K14").Select
        Application.ScreenUpdating = True
End Sub

All this code does is references my control sheet "data" and hides Named Ranges

So i want my macro to assign a different macro to the command buttons that my macro creates.

thanks in advance

[OnErrorGoto0]

Hi,
If you are using Buttons.Add to create the buttons (rather than using ActiveX buttons) then you can simply assign the macro name to the OnAction property of said buttons

btn.ONAction = "HideCrews"

as an example.

[jordan2322]

Sub AddButtons()

    ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
        , DisplayAsIcon:=False, Left:=1059.75, Top:=8.25, Width:=99.75, Height _
        :=36).Select
    ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
        , DisplayAsIcon:=False, Left:=1064.25, Top:=52.5, Width:=96.75, Height _
        :=30.75).Select
    Selection.ShapeRange.IncrementLeft -3#
End Sub

This is what i'm using to add my buttons.
Thank you for your continuing support OnError!

[OnErrorGoto0]

Those are ActiveX buttons. Unless you have a particular need to use them, I would recommend using Forms buttons instead

Dim btn As Button
    Set btn = ActiveSheet.Buttons.Add(Left:=1059.75, Top:=8.25, Width:=99.75, Height:=36)
btn.Onaction = "HideCrews"

as an example.

[snb]

Here you can find how to do add eventcode for an activeX control.

[jordan2322]

Thanks heaps guys, got this to work using form control buttons and

Sub AssignMacro()
Dim i As Integer

With ActiveSheet
For i = 1 To .Shapes.Count
If .Shapes.Item(i).Type = msoFormControl Then
.Select
    If Selection = xlButtonControl Then
    .Shapes.Item(i).OnAction = "HideCrews"
    End If
End If
Next i
End With

End Sub

[OnErrorGoto0]

Much simpler to iterate the Buttons collection, but never mind.

[snb]

Like OnError said:

sub snb()
  for each btn in sheets(1).buttons
    btn.onaction="HideCrews"
  next
end sub