My macro crashes on a different computer!

[rclark]

Hi forum. i have a very basic macro that has run fine for quite some time on my computer. I moved this to another user and it crashes about 20% of the time.

This is the error:

Run-time error'-2147417848 (80010108)':
Automation error
The object invoked has disconnected from its clients.

This is the code:

Sub Stamp()
'Stamp Macro
'Keyboard Shortcut: Ctrl+q
Application.ScreenUpdating = False
Sheets("Tracker").Select
ActiveSheet.Unprotect
Rows("4:4").Select
Selection.Copy
Selection.Insert Shift:=xlDown
Range("A4:f4").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("g5").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("g5").Select
Selection.Copy
Range("f4,g5").Select
Range("g5").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("h6:j6").Select
Application.CutCopyMode = False
Selection.Copy
Range("h5:j5").Select
ActiveSheet.Paste
Rows("5:5").Select
Application.CutCopyMode = False
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("C4").Select
ActiveCell.FormulaR1C1 = "No"
Range("A4").Select
Range("A4").Select
Application.ScreenUpdating = True
End Sub

when I go to debug, the red text line is highlighted.

Can someone help me recode this so I don't get the error? BTW this continues to work fine on 2 other computers and we have reinstalled Office on the one having the trouble and IT assures me it is my code.

Thanks in advance!

[Kenneth Hobs]

To investigate the problem, you need to be watching what they do to make it happen.

Using my crystal ball, I see someone running the macro from another sheet or horror of horrors, from another workbook even.

I suggest setting the protection in the workbook's open event and setting the UserInterface parameter so that code does not need to protect/unprotect.

Looking at your code, it appears that you are using recorded macro code. While that is fine as a learning tool, in normal programming, Select is often inefficient and not needed sometimes.

[rclark]

Thank you for the reply. The macro runs from a button I put on the screen of the main sheet. The user does have other workbooks open, but it definitley launches from the correct book and sheet. You are correct, this was recorded. Would you be so kind as to suggest a proper code to do this function and/or provide more detail into the protection thing you are talking about. Obviously, from the code you can tell I can get over my head in a tablespoon of water when it comes to this stuff. Thank you!

[snb]

Probably ?


Sub M_snb()
With Sheets("Tracker")
.unprotect
.rows(4).insert
.range("F4,G5,H5,J5")="=NOW()"
.protect
end with
End Sub

[rclark]

Thanks again. Is the protection thing a potential cause for the error, or was this just a parallel observation?

[Kenneth Hobs]

To go beyond the macro recorder see this excellent tutorial and site: http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/

In ThisWorkbook object:
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Protect "ken", UserInterfaceOnly:=True 'True allows code to change data.
Next ws
End Sub