Searching For Newly Added Command Button Procedure in VBA Excel

Question

I am trying to add code (a subroutine call) to a procedure within Sheet1 by finding the line number of the procedure's statement within sheet1 in VBE then adding the code to the next line over. The following code attempts to achieve this.

' This will search for and modify the appropriate Node#button_Click() subroutine
    With ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
            ProcLineNum = .ProcStartLine("Node" & NumNodes & "Button" & "_Click", 0)
            .InsertLines ProcLineNum + 1, "load_node_form(" & DQUOTE & "Node " & NumNodes & DQUOTE & ")"
    End With

The entire subroutine is the following:

Public Sub Node_Button_Duplication()
'
'Com: Copies and pastes Node 1's button to the appropriate column

Dim shp As Shape
Dim code As String
Dim ProcLineNum As Long
Const DQUOTE = """"

' Copy Node 1 button and paste in appropriate location

    ActiveSheet.Shapes("CommandButton1").Select
    Selection.Copy
    Cells(5, 10 + 7 * (NumNodes - 1) - 1).Select
    ActiveSheet.Paste
    Selection.ShapeRange.IncrementLeft 47.25
    Selection.ShapeRange.IncrementTop -13.5


    Set shp = ActiveSheet.Shapes(Selection.Name)

    With shp.OLEFormat.Object
        .Object.Caption = "Node" & Str(NumNodes)
        .Name = "Node" & NumNodes & "Button"
    End With

' This will search for and modify the appropriate Node#button_Click() subroutine
    With ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
            ProcLineNum = .ProcStartLine("Node" & NumNodes & "Button" & "_Click", 0)
            .InsertLines ProcLineNum + 1, "load_node_form(" & DQUOTE & "Node " & NumNodes & DQUOTE & ")"
    End With

End Sub

The subroutine will copy and paste a button ("CommandButton1"), rename it, then attempts to assign a subroutine call. The problem in finding the procedure is that once the new button is created, the "CommandButton#_Click() procedure doesn't show up in VBE until I go and select it from the editor, thus causing an error when my code tries to search for that procedure.

Just a thought, but I'd guess there are easier ways to do what you're doing. Basically, create a menu item that does whatever you're button does, and make the menu item context-sensitive, e.g., act on the active rows. Not that I know anything about your project, but I did one project early on that required the management of many buttons and then learned there were better ways. — Doug Glancy, May 18 '12 at 21:33
There probably is an easier way - this is just one avenue I'm exploring. Basically the end effect is that the user can add any number of buttons, each which will load the same form, but which button was pressed determines the filling of certain fields in the form, e.g. button1 will load form1 which will display button1 data, button2 will load form1 which will display button2 data, etc. — Ehudz, May 18 '12 at 21:41
Unless there's some reason you really need them, I would skip the CommandButtons (ActiveX) and go with the simpler "Form-type" button or even a simple shape instead. You can set up a single macro to handle all calls, and decide what to do based on the button's name (get this in the macro using `Application.Caller`) — Tim Williams, May 18 '12 at 22:21
It seems like Form buttons have less control over the button events than do ActiveX buttons. I tried to define the Form button behaviour but all I can do is assign a single macro to it. If Form buttons can give me the same code control of button events properties than I may further my investigation of Form buttons. But it seems ActiveX gives me that control without much exploration... — Ehudz, May 18 '12 at 22:34

score 1 · Accepted Answer · answered May 18 '12 at 21:46

Is this what you are trying?

Option Explicit

Public Sub Node_Button_Duplication()
    Dim shp As Shape
    Dim code As String
    Dim ProcLineNum As Long, NumNodes As Long

    Const DQUOTE = """"

    ActiveSheet.Shapes("CommandButton1").Select
    Selection.Copy
    Cells(5, 10 + 7 * (NumNodes - 1) - 1).Select
    ActiveSheet.Paste
    Selection.ShapeRange.IncrementLeft 47.25
    Selection.ShapeRange.IncrementTop -13.5

    Set shp = ActiveSheet.Shapes(Selection.Name)

    With shp.OLEFormat.Object
        .Object.Caption = "Node" & Str(NumNodes)
        .Name = "Node" & NumNodes & "Button"
    End With

    With ActiveWorkbook.VBProject.VBComponents( _
    ActiveWorkbook.Worksheets("Sheet1").CodeName).CodeModule
        .InsertLines Line:=.CreateEventProc("Click", "Node" & NumNodes & "Button") + 1, _
        String:=vbCrLf & _
        "load_node_form(" & DQUOTE & "Node " & NumNodes & DQUOTE & ")"
    End With
End Sub

I get a "Run-time error '9': Subscript out of range" error from the above code. — Ehudz, May 22 '12 at 14:24

Searching For Newly Added Command Button Procedure in VBA Excel

1 Answers1