V
Valeria
Dear experts,
please help, I am getting mad at this part of code!
I have a macro writing some code in the worksheet module of another
workbook. It seems to work well, the code itself written by hand in the
workbook works, but when I run it, I get the message " the object invoked has
disconnected from its clients", and my Excel crashes down!
What does it mean? Why do I get such en error?
The code is the following:
Sub Write_VBA_For_Security_ID()
Dim StartLine As Long
Workbooks(Montly_Report).Worksheets("Approvals_PM_Violations").Activate
Range("a1").Select
Worksheets("Approvals_PM_Violations").Unprotect Password:="my_password"
With ActiveWorkbook.VBProject.VBComponents("Sheet4").CodeModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
"dim vrange as range" & Chr(13) & _
"dim vvrange as range" & Chr(13) & _
"Dim cell As Object" & Chr(13) & _
"Me.Unprotect Password:=""my_password""" & Chr(13) & _
"Set vrange = Range(""ID_Conf"")" & Chr(13) & _
"Set vvrange = Range(""Approval_Granted_For"")" & Chr(13) & _
"For Each cell In Target" & Chr(13) & _
"If Union(cell, vrange).Address = vrange.Address Then" & Chr(13) & _
"Target.Offset(0, 1).Value = Application.UserName" & Chr(13) & _
"Me.Unprotect Password:=""my_password""" & Chr(13) & _
"Target.Offset(0, 2).Value = Format(Date, ""DD-MMM-YYYY"")" & Chr(13) & _
"ElseIf Union(cell, vvrange).Address = vvrange.Address Then" & Chr(13) & _
"Target.Offset(0, 1).Value = Month(Now -33 + 30 * Target.Cells.Value) &
""/"" & ""01/"" & Year(Now -33 + 30 * Target.Cells.Value)" & Chr(13) & _
"End If" & Chr(13) & _
"Next cell" & Chr(13) & _
"On Error GoTo 0" & Chr(13) & _
"Me.Protect Password:=""my_password"""
End With
Worksheets(4).Protect Password:="my_password"
End Sub
Many, many thanks in advance!
Best regards,
Valeria
please help, I am getting mad at this part of code!
I have a macro writing some code in the worksheet module of another
workbook. It seems to work well, the code itself written by hand in the
workbook works, but when I run it, I get the message " the object invoked has
disconnected from its clients", and my Excel crashes down!
What does it mean? Why do I get such en error?
The code is the following:
Sub Write_VBA_For_Security_ID()
Dim StartLine As Long
Workbooks(Montly_Report).Worksheets("Approvals_PM_Violations").Activate
Range("a1").Select
Worksheets("Approvals_PM_Violations").Unprotect Password:="my_password"
With ActiveWorkbook.VBProject.VBComponents("Sheet4").CodeModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
"dim vrange as range" & Chr(13) & _
"dim vvrange as range" & Chr(13) & _
"Dim cell As Object" & Chr(13) & _
"Me.Unprotect Password:=""my_password""" & Chr(13) & _
"Set vrange = Range(""ID_Conf"")" & Chr(13) & _
"Set vvrange = Range(""Approval_Granted_For"")" & Chr(13) & _
"For Each cell In Target" & Chr(13) & _
"If Union(cell, vrange).Address = vrange.Address Then" & Chr(13) & _
"Target.Offset(0, 1).Value = Application.UserName" & Chr(13) & _
"Me.Unprotect Password:=""my_password""" & Chr(13) & _
"Target.Offset(0, 2).Value = Format(Date, ""DD-MMM-YYYY"")" & Chr(13) & _
"ElseIf Union(cell, vvrange).Address = vvrange.Address Then" & Chr(13) & _
"Target.Offset(0, 1).Value = Month(Now -33 + 30 * Target.Cells.Value) &
""/"" & ""01/"" & Year(Now -33 + 30 * Target.Cells.Value)" & Chr(13) & _
"End If" & Chr(13) & _
"Next cell" & Chr(13) & _
"On Error GoTo 0" & Chr(13) & _
"Me.Protect Password:=""my_password"""
End With
Worksheets(4).Protect Password:="my_password"
End Sub
Many, many thanks in advance!
Best regards,
Valeria