automatically adding coding to new sheets

zoog25

Active Member
Joined
Nov 21, 2011
Messages
418
Hello All,

I'm currently working on a workbook that has "Worksheet_Change" events on them. So here is the situation, i have a worksheet from a different workbook that i want to do a workbook open event in which when the workbook opens, it brings in a sheet from a different workbook and then adds on coding to the sheet. The new sheet has no coding, so i'd like to add something like this to the sheet

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

'code

End Sub

So it possible to add that to the new sheet.

Additionally lets say i would like to take that new sheet and send it back to it's original workbook. Is there a way to remove coding.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
You can use VBComponents. e.g.
VBA Code:
Sub AddAnEvent()
    Dim StartLine As Long
    With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
        StartLine = .CreateEventProc("Change", "Worksheet") + 1
        .InsertLines StartLine, _
        "Msgbox ""Changed a cell!"",vbOkOnly"
    End With
End Sub
 
Upvote 0
Solution
Hello Kenneth,

Thank you for getting me started. The code is working. Now i do have an additional questions, show do i add additional lines?
 
Upvote 0
You can use string concatenation or another insertlines as shown in 2nd sub.
VBA Code:
Sub AddEvent1()
  ActiveWorkbook.VBProject.VBComponents(Sheets("Sheet1").CodeName).CodeModule.AddFromString _
  "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" & vbNewLine & _
  "MsgBox (""is it working ?"")" & vbNewLine & _
  "End Sub"
End Sub

Sub AddAnEvent2()
    Dim StartLine As Long
    'https://support.microsoft.com/en-us/topic/programmatic-access-to-office-vba-project-is-denied-960d5265-6592-9400-31bc-b2ddfb94b445
    With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
        StartLine = .CreateEventProc("Change", "Worksheet") + 1
        .InsertLines StartLine, vbTab + "Msgbox ""Changed a cell!"",vbOkOnly"
        .InsertLines StartLine + 1, vbTab + "Msgbox ""2nd Line!"",vbOkOnly"
    End With
End Sub
 
Upvote 0
Now i have one last question and i should be good. I'm looking at your method 2. Say you have nested coding so how do i use vbTab in order to say Tab over 2 or 3 over.
 
Upvote 0
The tabs are just to make it look structured as I normally do when I code.

Simply concatenate however many tabs if just a few.
VBA Code:
.InsertLines StartLine + 1, vbTab + vTab + "Msgbox ""2nd Line!"",vbOkOnly"

If you have many, you can do it another way using String(). e.g.
VBA Code:
.InsertLines StartLine + 1, String(2, vbTab) + "Msgbox ""2nd Line!"",vbOkOnly"
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top