Programatically change VBA code in a Button

[pingme89]

I have a button called, btnTestCode.

In the VBE, it is listed as Private Sub btnTestCode_Click().

I would like to replace all the contents in it to other code.

Microsoft Visual Basic For Applications Extensibility 5.3 is already referenced.

I have never done this before and have no idea how to do it. Can someone share some insight?

Thanks in advance.

 

[ranman256]

change anything you want inside

Private Sub btnTestCode_Click()
    'code part
end sub

 

[Domenic]

Actually, it looks like you want to use code to replace the existing code. To do so, let's assume the following...

1) the userform contains the code to be replaced, and that the name of the userform is UserForm1

2) cell A1 contains the replacement text, and that its worksheet is the active sheet

3) a referece to Microsoft Visual Basic for Applications Extensibility has been set (VBE > Tools > References)

4) the macro security setting allows access to the VBA project object model (Ribbon > Developer > Macro Settings)

Then try the following code, which needs to be placed in a regular module...

Option Explicit

Sub ReplaceCode()


    Dim VBP As VBIDE.VBProject
    Dim sCode As String
    Dim sProcName As String
    Dim sEventName As String
    Dim StartLine As Long
    Dim bReplaced As Boolean
    
    On Error Resume Next
    Set VBP = ActiveWorkbook.VBProject
    If Err <> 0 Then
        MsgBox "You'll need to allow access to the VBA project object model!", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0
    
    sEventName = "btnTestCode_Click"
    
    sCode = Range("A1").Value
    
    bReplaced = False
    With VBP.VBComponents("UserForm1").CodeModule
        StartLine = .CountOfDeclarationLines + 1
        Do Until StartLine > .CountOfLines
            sProcName = .ProcOfLine(StartLine, vbext_pk_Proc)
            If InStr(1, sProcName, sEventName, vbTextCompare) Then
                .DeleteLines StartLine, .ProcCountLines(sProcName, vbext_pk_Proc)
                .InsertLines StartLine, sCode
                bReplaced = True
                Exit Do
            End If
            StartLine = StartLine + .ProcCountLines(sProcName, vbext_pk_Proc)
        Loop
    End With
    
    If bReplaced Then
        MsgBox "Code for '" & sEventName & "' has been replaced.", vbInformation
    Else
        MsgBox "Code for '" & sEventName & "' was not found and, therefore, could not be replaced.", vbInformation
    End If
    
    Set VBP = Nothing
    
End Sub

Change the name of the userform, accordingly.

Hope this helps!

 

[pingme89]

Thanks for the code. I think your code is trying to change code in a button on a Userform. I am trying to change code in a button on a Worksheet. What modifications should I use to refer to a button on a Worksheet instead of a Userform?

 

[Domenic]

In that case, you'll need to refer to worksheet instead of the userform. However, you'll need to use the code name for sheet, not the sheet name. You'll find the code name in the Properties Window (F4) under Name. So, for example, if the code name for the sheet is "Sheet1", you'll need to refer to it like this...

With VBP.VBComponents("Sheet1").CodeModule

Hope this helps!

 

[mikerickson]

There are cases where changing the code behind a button via code is a good idea.

But, if your goal is to have the button do one thing some times and another at different times, there are better ways to achieve that, that don't involve have your Trust Center set to high trust.

 

[pingme89]

To be honest, I am using VBA to make VBA code based on user's parameters. Once the code is done, I want to place the new code into a button to test. So the same button will have different VBA code every time new VBA code is made. What other method would you suggest?

 

[mikerickson]

I would suggest that for each project, you create a new test button.

 

[pingme89]

I see. So can you tell me how I can delete a button and recreate it with the same name?

 

[mikerickson]

I'm not sure about ActiveX controls (my Mac doesn't support them), but my first try would be to use the Properties window in the VBE to change the name of the button.

I'm wondering why the name of the control matters. As long as the name of the control and the name of the Click event match, the name wouldn't normally matter.