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.

 

[pingme89]

Thanks for the help. I think I can figure it out from here.

 

[pingme89]

I tried your code but I get an error message.

Run-time error '91':

Object variable or With block variable not set. This error comes on the line you told me to edit: With VBP.VBComponents("VBA Tools").CodeModule

 

[Domenic]

Can you post the complete code that you're using?

 

[pingme89]

Can you post the complete code that you're using?

Please note that the below code is what I ended up using and works but not as I originally wanted it to.
I was originally trying to have a button called TestCode. This button would be used to test code. I have other buttons that would generate VBA code. Each of those buttons would, after generating VBA code, replace all the VBA code in TestCode_Click(). This I was unable to do. So I ended up putting a Macro into TestCode_Click(). The below code would delete all the Modules/Macros and create a new one and add code to Module1. This is less than ideal since I could only ever have ONE Macro as the code below would delete all Macros. I tried to limit it to deleting only Module1 but then again, if in the future I used more macros, the TestCode could never be guaranteed to be be in Module1.

Can you help me find a better solution? Thanks in advance.


Dim MobileNum As String
Dim MobileNumVariableName As String
Dim SMSTxt As String
Dim SMSTxtVariableName As String
Dim wbname As String
Dim Clipboard As MSForms.DataObject
Dim DataObj As New MSForms.DataObject


MobileNum = ThisWorkbook.Worksheets("VBA Tools").Range("I191").Value
MobileNumVariableName = ThisWorkbook.Worksheets("VBA Tools").Range("I192").Value
SMSTxt = ThisWorkbook.Worksheets("VBA Tools").Range("I193").Value
SMSTxtVariableName = ThisWorkbook.Worksheets("VBA Tools").Range("I194").Value


wbname = ThisWorkbook.Name




Code
DataObj.SetText Code
DataObj.PutInClipboard


Set cmdButton = ThisWorkbook.Worksheets("VBA Tools").Shapes("btnCodeSMS")
btnTestCode.Caption = "Test SMS "
btnTestCode.AutoSize = True


Rem Deletes Module1
On Error Resume Next
Set vbCom = Application.VBE.ActiveVBProject.VBComponents
For i = 1 To 100
vbCom.Remove VBComponent:= _
vbCom.Item("Module" & i)
Next i
Rem End of Delete Module 1


Code = "Sub TestCode()" & vbLf & Code & vbLf & vbLf & "End Sub"


Dim vbp As Object
Dim newmod As Object
Set vbp = ActiveWorkbook.VBProject
Set newmod = vbp.VBComponents.Add(1)
Dim StartLine As Long
Dim cLines As Long


On Error Resume Next
With ActiveWorkbook.VBProject.VBComponents("Module1").CodeModule
cLines = .CountOfLines + 1
.InsertLines cLines, Code
End With

 

[Domenic]

First of all, I see that I mistakenly assumed that the code for your button on the worksheet would be located in the sheet module. I see now that it's actually located in a Module1.

Secondly, did you allow access to the VBA project object model, as I described in my earlier post?

 

[pingme89]

yes I did. How can we avoid using a macro? Is it possible to edit the Commandbutton code directly?

 

[pingme89]

The button is actually located on the Sheet VBA Tools. So the code is actually on Sheet1 ( "VBA Tools") under btnTestCode_Click()