Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Dim Startline As Long
- Startline = 1
- Dim x As Integer
- x = Errors.Count - 1
- Dim rng As Range
- Set rng = Range("D" & LastRow - x & ":" & "D" & LastRow)
- With ThisWorkbook.VBProject.VBComponents(VRS.CodeName).CodeModule
- Startline = .CreateEventProc("Change", "Worksheet") + 1
- .InsertLines Startline, "Dim rng As Range "
- Startline = Startline + 1
- .InsertLines Startline, "Set rng = Range(" & """" & CStr(rng.Address) & """" & ")"
- Startline = Startline + 1
- .InsertLines Startline, "If Target.Count > 1 Then Exit Sub"
- Startline = Startline + 1
- .InsertLines Startline, "If Intersect(Target, rng) Is Nothing Then Exit Sub"
- Startline = Startline + 1
- .InsertLines Startline, "MsgBox (""Value Changed!..."") "
- End With
- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim rng As Range
- Set rng = Range("D58:D62")
- If Target.Count > 1 Then Exit Sub
- If Intersect(Target, rng) Is Nothing Then Exit Sub
- MsgBox ("Value Changed!...")
- End Sub`
- Run-time error '9': Subscript out of range
- With ThisWorkbook.VBProject.VBComponents(WS.CodeName).CodeModule
- Startline = .CreateEventProc("Change", "Worksheet") + 1
- Sub Main()
- Dim ws As Worksheet
- Dim rng As Range
- Dim sCode As String
- Set ws = ThisWorkbook.Worksheets.Add
- Set rng = ws.Range("D1:D10")
- sCode = "Private Sub Worksheet_Change(ByVal Target As Range)" & vbNewLine & vbNewLine
- sCode = sCode & vbTab & "Dim rng As Range" & vbNewLine & vbNewLine
- sCode = sCode & vbTab & "Set rng = Me.Range(" & """" & rng.Address & """" & ")" & vbNewLine & vbNewLine
- sCode = sCode & vbTab & "If Target.Count > 1 Then Exit Sub" & vbNewLine
- sCode = sCode & vbTab & "If Intersect(Target, rng) Is Nothing Then Exit Sub" & vbNewLine & vbNewLine
- sCode = sCode & vbTab & "MsgBox (""Value Changed!..."") " & vbNewLine
- sCode = sCode & "End Sub"
- ThisWorkbook.VBProject.VBComponents(ws.CodeName).CodeModule.AddFromString sCode
- End Sub
Add Comment
Please, Sign In to add comment