OK I have a textbox problem. It only happens every few days or so, but unfortunately after the error occurs (-2147417848 Method 'Insert' of object 'Characters' failed), the textbox becomes corrupt. By this I mean that if you use the down arrow to go to the end of the textbox, the cursor freezes, and I have to use Ctrl-Alt-Del. There is no obvious sign of corruption in the textbox (n.b. a textbox from the drawing toolbar) - until I enlarge it. Then I can see little boxes then text taken from the data validation input messages, i.e garbage. To fix the problem I have to copy all the text by hand as selecting it all causes a crash, plus recreate the sheet, so this is a real problem. Here is the crashing code:
Sub warningtext_RENTED_ARREARS_NO_PROPERTY_ADD(textboxname)
Dim t1 As String
Dim t2 As String
Dim t3 As String
Dim t4 As String
Dim t5 As String
t1 = "THIS TENANT'S BOND DOES NOT COVER THEIR ARREARS, "
t2 = " WEEK'S NOTICE AND A $"
t3 = " ALLOWANCE FOR CLEANING AND DAMAGE. THEY HAVE NO SEIZABLE PROPERTY, "
t4 = "SO SHOULD BE EVICTED. IF THEY LEAVE TODAY, YOU WOULD LOSE "
t5 = " PLUS ANY SHOP ACCOUNT."
startpoint = Len(Range("a1"
) + 2
ActiveSheet.Shapes(textboxname).Select
Selection.Characters(Start:=startpoint, Length:=1).Insert _
Chr(10) & t1 & Worksheets("Control Panel"
.Range("K5"
& _
t2 & clean_charge & t3 & t4 & Format(Range("h13"
, "$0.00"
& t5 & Chr(10)
Bear in mind that there are many worksheets similar which are updated several times daily and this happens only now and again.
Sub warningtext_RENTED_ARREARS_NO_PROPERTY_ADD(textboxname)
Dim t1 As String
Dim t2 As String
Dim t3 As String
Dim t4 As String
Dim t5 As String
t1 = "THIS TENANT'S BOND DOES NOT COVER THEIR ARREARS, "
t2 = " WEEK'S NOTICE AND A $"
t3 = " ALLOWANCE FOR CLEANING AND DAMAGE. THEY HAVE NO SEIZABLE PROPERTY, "
t4 = "SO SHOULD BE EVICTED. IF THEY LEAVE TODAY, YOU WOULD LOSE "
t5 = " PLUS ANY SHOP ACCOUNT."
startpoint = Len(Range("a1"
ActiveSheet.Shapes(textboxname).Select
Selection.Characters(Start:=startpoint, Length:=1).Insert _
Chr(10) & t1 & Worksheets("Control Panel"
t2 & clean_charge & t3 & t4 & Format(Range("h13"
Bear in mind that there are many worksheets similar which are updated several times daily and this happens only now and again.