• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

UserForm MultiLine Textbox - Sudden Run-Time Error '1004': Application-Defined or Object-Defined Error

Y

Yodelayheewho

Member
Hello Everyone!
I needed to add more notes to one of my Shop Orders (MSO-40254). I enter the notes into a textbox (TextNotes) that is set as multiline. See attached for all the property settings.
This particular Shop Order has had a lot of activity, so there are a lot of notes. The following shows the word count, etc.
80556

To troubleshoot, I searched other Shop Orders and clicked Update and Save - and received no error message.
I did some reading and understand the multiline textbox has no character/line limits. However, I can't figure out what else it could be. This code has been working for months.

The error is pointing to the line in red text below.

I provide a sample file whenever possible, but for this, it would a long time to desensitize the data. I'm hoping I can resolve it without a sample file.

Thank you in advance!

Code: 
'Update and Save Button'
Private Sub UpdateandSaveOrder_Click()
Dim Shop_Order_Number As String
Shop_Order_Number = Trim(TextShopOrderNumber)
lastrow = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Worksheets("Master").Cells(i, 4).Value = Shop_Order_Number Then
Worksheets("Master").Cells(i, 1).Value = TextPrefix
Worksheets("Master").Cells(i, 2).Value = TextE10Status
Worksheets("Master").Cells(i, 3).Value = TextSuffix
Worksheets("Master").Cells(i, 4).Value = TextShopOrderNumber
Worksheets("Master").Cells(i, 5).Value = TextEmailSubjectLine
Worksheets("Master").Cells(i, 6).Value = TextNotes
Worksheets("Master").Cells(i, 7).Value = TextStage
Worksheets("Master").Cells(i, 8).Value = TextStartDate
Worksheets("Master").Cells(i, 9).Value = TextStageDue
Worksheets("Master").Cells(i, 10).Value = TextEndDate
Worksheets("Master").Cells(i, 11).Value = TextProposalNumber
Worksheets("Master").Cells(i, 12).Value = TextSalespersonInitials
Worksheets("Master").Cells(i, 13).Value = TextSalesperson
Worksheets("Master").Cells(i, 14).Value = TextProposalDate
Worksheets("Master").Cells(i, 15).Value = TextLeadTime
Worksheets("Master").Cells(i, 16).Value = TextPromisedDate
Worksheets("Master").Cells(i, 17).Value = TextProposalTerms
Worksheets("Master").Cells(i, 18).Value = TextExpirationDate
Worksheets("Master").Cells(i, 19).Value = TextCost
Worksheets("Master").Cells(i, 20).Value = TextMargin
Worksheets("Master").Cells(i, 21).Value = TextPONumber
Worksheets("Master").Cells(i, 22).Value = TextPODate
Worksheets("Master").Cells(i, 23).Value = TextPOReceivedDate
Worksheets("Master").Cells(i, 24).Value = TextPOAmount
Worksheets("Master").Cells(i, 25).Value = TextPOTerms
Worksheets("Master").Cells(i, 26).Value = TextAccessTermsCode
Worksheets("Master").Cells(i, 27).Value = TextShipVia
Worksheets("Master").Cells(i, 28).Value = TextShipType
Worksheets("Master").Cells(i, 29).Value = TextShipCharges
Worksheets("Master").Cells(i, 30).Value = TextShippingInstructions
Worksheets("Master").Cells(i, 31).Value = TextSalesOrderNumber
Worksheets("Master").Cells(i, 32).Value = TextQuoteNumber
Worksheets("Master").Cells(i, 33).Value = TextProjectManagerInitials
Worksheets("Master").Cells(i, 34).Value = TextProjectManager
Worksheets("Master").Cells(i, 35).Value = TextElectronicEngineer
Worksheets("Master").Cells(i, 36).Value = TextSystemDescription
Worksheets("Master").Cells(i, 37).Value = TextSCode
Worksheets("Master").Cells(i, 38).Value = TextBMTH
Worksheets("Master").Cells(i, 39).Value = TextTransferOrderNumber
Worksheets("Master").Cells(i, 40).Value = TextMultipleLines
Worksheets("Master").Cells(i, 41).Value = TextStandardPartsIncluded
Worksheets("Master").Cells(i, 42).Value = TextInstallationDays
Worksheets("Master").Cells(i, 43).Value = TextStartUpDays
Worksheets("Master").Cells(i, 44).Value = TextTrainingDaysOnsite
Worksheets("Master").Cells(i, 45).Value = TextTrainingDaysToledo
Worksheets("Master").Cells(i, 46).Value = TextVendorFieldServiceDays
Worksheets("Master").Cells(i, 47).Value = TextServiceTechnician
Worksheets("Master").Cells(i, 48).Value = TextStandardHours1and2
Worksheets("Master").Cells(i, 49).Value = TextStandardHours3
Worksheets("Master").Cells(i, 50).Value = TextSaturdaySundayorHolidays
Worksheets("Master").Cells(i, 51).Value = TextAdditionalOvertime
Worksheets("Master").Cells(i, 52).Value = TextTravelLessThan8Hours
Worksheets("Master").Cells(i, 53).Value = TextTravelMoreThan8Hours
Worksheets("Master").Cells(i, 54).Value = TextAirfare
Worksheets("Master").Cells(i, 55).Value = TextHotel
Worksheets("Master").Cells(i, 56).Value = TextCarRental
Worksheets("Master").Cells(i, 57).Value = TextMeals
Worksheets("Master").Cells(i, 58).Value = TextMileage
Worksheets("Master").Cells(i, 59).Value = TextParking
Worksheets("Master").Cells(i, 60).Value = TextServiceParts1
Worksheets("Master").Cells(i, 61).Value = TextServiceParts2
Worksheets("Master").Cells(i, 62).Value = TextBookingFees
Worksheets("Master").Cells(i, 63).Value = TextOptionalDescription
Worksheets("Master").Cells(i, 64).Value = TextTotal
Worksheets("Master").Cells(i, 65).Value = TextServiceGroup
Worksheets("Master").Cells(i, 66).Value = TextEnteredinE10
Worksheets("Master").Cells(i, 67).Value = TextConfirmationofPO
Worksheets("Master").Cells(i, 68).Value = TextRequestApproval
Worksheets("Master").Cells(i, 69).Value = TextRequestPM
Worksheets("Master").Cells(i, 70).Value = TextPMAssigned
Worksheets("Master").Cells(i, 71).Value = TextFoldersCopied
Worksheets("Master").Cells(i, 72).Value = TextSOtoTeamPM
Worksheets("Master").Cells(i, 73).Value = TextApproved
Worksheets("Master").Cells(i, 74).Value = TextSOAtoCustomer
Worksheets("Master").Cells(i, 75).Value = TextSOADateinE10
Worksheets("Master").Cells(i, 76).Value = TextEnteredinAccess
Worksheets("Master").Cells(i, 77).Value = TextRequestInvoice
Worksheets("Master").Cells(i, 78).Value = TextReceivedInvoice
Worksheets("Master").Cells(i, 79).Value = TextInvoiceNumber
Worksheets("Master").Cells(i, 80).Value = TextCustomerName
Worksheets("Master").Cells(i, 81).Value = TextDiamondDistributor
Worksheets("Master").Cells(i, 82).Value = TextAKAorNickname
Worksheets("Master").Cells(i, 83).Value = TextCUSTID
Worksheets("Master").Cells(i, 84).Value = TextAccessCUSTID
Worksheets("Master").Cells(i, 85).Value = TextBillToName
Worksheets("Master").Cells(i, 86).Value = TextBillToAddress1
Worksheets("Master").Cells(i, 87).Value = TextBillToAddress2
Worksheets("Master").Cells(i, 88).Value = TextBillToCity
Worksheets("Master").Cells(i, 89).Value = TextBillToState
Worksheets("Master").Cells(i, 90).Value = TextBillToZipCode
Worksheets("Master").Cells(i, 91).Value = TextBillToCountry
Worksheets("Master").Cells(i, 92).Value = TextAltBillToName
Worksheets("Master").Cells(i, 93).Value = TextAltBillToID
Worksheets("Master").Cells(i, 94).Value = TextTaxExempt
Worksheets("Master").Cells(i, 95).Value = TextContact1Name
Worksheets("Master").Cells(i, 96).Value = TextContact1Email
Worksheets("Master").Cells(i, 97).Value = TextContact2Name
Worksheets("Master").Cells(i, 98).Value = TextContact2Email
Worksheets("Master").Cells(i, 99).Value = TextShipToID
Worksheets("Master").Cells(i, 100).Value = TextAccessShipToID
Worksheets("Master").Cells(i, 101).Value = TextShipToName
Worksheets("Master").Cells(i, 102).Value = TextShipToAddress1
Worksheets("Master").Cells(i, 103).Value = TextShipToAddress2
Worksheets("Master").Cells(i, 104).Value = TextShipToCity
Worksheets("Master").Cells(i, 105).Value = TextShipToState
Worksheets("Master").Cells(i, 106).Value = TextShipToZipCode
Worksheets("Master").Cells(i, 107).Value = TextShipToCountry
Worksheets("Master").Cells(i, 108).Value = TextEndUserName
Worksheets("Master").Cells(i, 109).Value = TextEndUserID
Worksheets("Master").Cells(i, 110).Value = TextAccessEndUserID
Worksheets("Master").Cells(i, 111).Value = TextRansburgReport
Worksheets("Master").Cells(i, 112).Value = TextBGKReport
Worksheets("Master").Cells(i, 113).Value = TextShippedYesorNo
Worksheets("Master").Cells(i, 114).Value = TextShipDate
Worksheets("Master").Cells(i, 115).Value = TextPromiseDateAKAShipDate
Worksheets("Master").Cells(i, 116).Value = TextExpectedShipDateAKARecognizeRevenue
Worksheets("Master").Cells(i, 117).Value = TextStatusUpdated
End If
Next
ActiveWorkbook.Save
End Sub
 

Attachments

  • UserForm.jpg
    UserForm.jpg
    373.6 KB · Views: 2
That’s weird because it was red when I submitted the question. Anyways, the line is: Worksheets("Master").Cells(i, 6).Value = TextNotes
 
OK, since the lines just prior to that line haven't complained it must be something to do with TextNotes. Problem is I have no idea of the context of TextNotes. To save time I think it'll need a file where the error occurs.
If you're not prepared to put it in the public domain, but you are prepared for just me to see it, you could share it on some file-sharing service (eg. oneDrive, dropBox, Box, whatever) and only give me access, be it by password or whatever. You could start a Conversation with me here at Chandoo, the conversation is private, and give me a link and a password or whatever is needed. Of course, you'll be able to remove the file altogether later.
 
Work is slow today, so I was able to create/provide a sample file attached. I really appreciate your willingness to assist me.
 

Attachments

  • Chandoo Sample_Systems Order Entry Master.xlsm
    116.5 KB · Views: 4
Weird. Looks like it's down to length of the text.
Try:
Code: 
a = TextNotes
Worksheets("Master").Cells(i, 6).Value = a
It worked here!
 
I'm very new to VBA and not sure where exactly I put the code you gave me. Plus, I already have Dim a As Long in the code for the Add New command button...
Code: 
'Add New Button'
Private Sub AddNew_Click()
'The next four lines of code prevents the user from entering duplicate Shop Order Number.
'D:D is the Shop Order Number column on the Master'
Dim a As Long
a = Application.WorksheetFunction.CountIf(Range("D:D"), Me.TextShopOrderNumber.Text)
If a >= 1 Then
MsgBox "The Shop Order Number Already Exists. Please Enter a Unique Shop Order Number"
Else
lastrow = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Master").Cells(lastrow + 1, 1).Value = TextPrefix
Worksheets("Master").Cells(lastrow + 1, 2).Value = TextE10Status
Worksheets("Master").Cells(lastrow + 1, 3).Value = TextSuffix
Worksheets("Master").Cells(lastrow + 1, 4).Value = TextShopOrderNumber
Worksheets("Master").Cells(lastrow + 1, 5).Value = TextEmailSubjectLine
Worksheets("Master").Cells(lastrow + 1, 6).Value = TextNotes
Worksheets("Master").Cells(lastrow + 1, 7).Value = TextStage
Worksheets("Master").Cells(lastrow + 1, 8).Value = TextStartDate
Worksheets("Master").Cells(lastrow + 1, 9).Value = TextStageDue
Worksheets("Master").Cells(lastrow + 1, 10).Value = TextEndDate
Worksheets("Master").Cells(lastrow + 1, 11).Value = TextProposalNumber
Worksheets("Master").Cells(lastrow + 1, 12).Value = TextSalespersonInitials
Worksheets("Master").Cells(lastrow + 1, 13).Value = TextSalesperson
Worksheets("Master").Cells(lastrow + 1, 14).Value = TextProposalDate
Worksheets("Master").Cells(lastrow + 1, 15).Value = TextLeadTime
Worksheets("Master").Cells(lastrow + 1, 16).Value = TextPromisedDate
Worksheets("Master").Cells(lastrow + 1, 17).Value = TextProposalTerms
Worksheets("Master").Cells(lastrow + 1, 18).Value = TextExpirationDate
Worksheets("Master").Cells(lastrow + 1, 19).Value = TextCost
Worksheets("Master").Cells(lastrow + 1, 20).Value = TextMargin
Worksheets("Master").Cells(lastrow + 1, 21).Value = TextPONumber
Worksheets("Master").Cells(lastrow + 1, 22).Value = TextPODate
Worksheets("Master").Cells(lastrow + 1, 23).Value = TextPOReceivedDate
Worksheets("Master").Cells(lastrow + 1, 24).Value = TextPOAmount
Worksheets("Master").Cells(lastrow + 1, 25).Value = TextPOTerms
Worksheets("Master").Cells(lastrow + 1, 26).Value = TextAccessTermsCode
Worksheets("Master").Cells(lastrow + 1, 27).Value = TextShipVia
Worksheets("Master").Cells(lastrow + 1, 28).Value = TextShipType
Worksheets("Master").Cells(lastrow + 1, 29).Value = TextShipCharges
Worksheets("Master").Cells(lastrow + 1, 30).Value = TextShippingInstructions
Worksheets("Master").Cells(lastrow + 1, 31).Value = TextSalesOrderNumber
Worksheets("Master").Cells(lastrow + 1, 32).Value = TextQuoteNumber
Worksheets("Master").Cells(lastrow + 1, 33).Value = TextProjectManagerInitials
Worksheets("Master").Cells(lastrow + 1, 34).Value = TextProjectManager
Worksheets("Master").Cells(lastrow + 1, 35).Value = TextElectronicEngineer
Worksheets("Master").Cells(lastrow + 1, 36).Value = TextSystemDescription
Worksheets("Master").Cells(lastrow + 1, 37).Value = TextSCode
Worksheets("Master").Cells(lastrow + 1, 38).Value = TextBMTH
Worksheets("Master").Cells(lastrow + 1, 39).Value = TextTransferOrderNumber
Worksheets("Master").Cells(lastrow + 1, 40).Value = TextMultipleLines
Worksheets("Master").Cells(lastrow + 1, 41).Value = TextStandardPartsIncluded
Worksheets("Master").Cells(lastrow + 1, 42).Value = TextInstallationDays
Worksheets("Master").Cells(lastrow + 1, 43).Value = TextStartUpDays
Worksheets("Master").Cells(lastrow + 1, 44).Value = TextTrainingDaysOnsite
Worksheets("Master").Cells(lastrow + 1, 45).Value = TextTrainingDaysToledo
Worksheets("Master").Cells(lastrow + 1, 46).Value = TextVendorFieldServiceDays
Worksheets("Master").Cells(lastrow + 1, 47).Value = TextServiceTechnician
Worksheets("Master").Cells(lastrow + 1, 48).Value = TextStandardHours1and2
Worksheets("Master").Cells(lastrow + 1, 49).Value = TextStandardHours3
Worksheets("Master").Cells(lastrow + 1, 50).Value = TextSaturdaySundayorHolidays
Worksheets("Master").Cells(lastrow + 1, 51).Value = TextAdditionalOvertime
Worksheets("Master").Cells(lastrow + 1, 52).Value = TextTravelLessThan8Hours
Worksheets("Master").Cells(lastrow + 1, 53).Value = TextTravelMoreThan8Hours
Worksheets("Master").Cells(lastrow + 1, 54).Value = TextAirfare
Worksheets("Master").Cells(lastrow + 1, 55).Value = TextHotel
Worksheets("Master").Cells(lastrow + 1, 56).Value = TextCarRental
Worksheets("Master").Cells(lastrow + 1, 57).Value = TextMeals
Worksheets("Master").Cells(lastrow + 1, 58).Value = TextMileage
Worksheets("Master").Cells(lastrow + 1, 59).Value = TextParking
Worksheets("Master").Cells(lastrow + 1, 60).Value = TextServiceParts1
Worksheets("Master").Cells(lastrow + 1, 61).Value = TextServiceParts2
Worksheets("Master").Cells(lastrow + 1, 62).Value = TextBookingFees
Worksheets("Master").Cells(lastrow + 1, 63).Value = TextOptionalDescription
Worksheets("Master").Cells(lastrow + 1, 64).Value = TextTotal
Worksheets("Master").Cells(lastrow + 1, 65).Value = TextServiceGroup
Worksheets("Master").Cells(lastrow + 1, 66).Value = TextEnteredinE10
Worksheets("Master").Cells(lastrow + 1, 67).Value = TextConfirmationofPO
Worksheets("Master").Cells(lastrow + 1, 68).Value = TextRequestApproval
Worksheets("Master").Cells(lastrow + 1, 69).Value = TextRequestPM
Worksheets("Master").Cells(lastrow + 1, 70).Value = TextPMAssigned
Worksheets("Master").Cells(lastrow + 1, 71).Value = TextFoldersCopied
Worksheets("Master").Cells(lastrow + 1, 72).Value = TextSOtoTeamPM
Worksheets("Master").Cells(lastrow + 1, 73).Value = TextApproved
Worksheets("Master").Cells(lastrow + 1, 74).Value = TextSOAtoCustomer
Worksheets("Master").Cells(lastrow + 1, 75).Value = TextSOADateinE10
Worksheets("Master").Cells(lastrow + 1, 76).Value = TextEnteredinAccess
Worksheets("Master").Cells(lastrow + 1, 77).Value = TextRequestInvoice
Worksheets("Master").Cells(lastrow + 1, 78).Value = TextReceivedInvoice
Worksheets("Master").Cells(lastrow + 1, 79).Value = TextInvoiceNumber
Worksheets("Master").Cells(lastrow + 1, 80).Value = TextCustomerName
Worksheets("Master").Cells(lastrow + 1, 81).Value = TextDiamondDistributor
Worksheets("Master").Cells(lastrow + 1, 82).Value = TextAKAorNickname
Worksheets("Master").Cells(lastrow + 1, 83).Value = TextCUSTID
Worksheets("Master").Cells(lastrow + 1, 84).Value = TextAccessCUSTID
Worksheets("Master").Cells(lastrow + 1, 85).Value = TextBillToName
Worksheets("Master").Cells(lastrow + 1, 86).Value = TextBillToAddress1
Worksheets("Master").Cells(lastrow + 1, 87).Value = TextBillToAddress2
Worksheets("Master").Cells(lastrow + 1, 88).Value = TextBillToCity
Worksheets("Master").Cells(lastrow + 1, 89).Value = TextBillToState
Worksheets("Master").Cells(lastrow + 1, 90).Value = TextBillToZipCode
Worksheets("Master").Cells(lastrow + 1, 91).Value = TextBillToCountry
Worksheets("Master").Cells(lastrow + 1, 92).Value = TextAltBillToName
Worksheets("Master").Cells(lastrow + 1, 93).Value = TextAltBillToID
Worksheets("Master").Cells(lastrow + 1, 94).Value = TextTaxExempt
Worksheets("Master").Cells(lastrow + 1, 95).Value = TextContact1Name
Worksheets("Master").Cells(lastrow + 1, 96).Value = TextContact1Email
Worksheets("Master").Cells(lastrow + 1, 97).Value = TextContact2Name
Worksheets("Master").Cells(lastrow + 1, 98).Value = TextContact2Email
Worksheets("Master").Cells(lastrow + 1, 99).Value = TextShipToID
Worksheets("Master").Cells(lastrow + 1, 100).Value = TextAccessShipToID
Worksheets("Master").Cells(lastrow + 1, 101).Value = TextShipToName
Worksheets("Master").Cells(lastrow + 1, 102).Value = TextShipToAddress1
Worksheets("Master").Cells(lastrow + 1, 103).Value = TextShipToAddress2
Worksheets("Master").Cells(lastrow + 1, 104).Value = TextShipToCity
Worksheets("Master").Cells(lastrow + 1, 105).Value = TextShipToState
Worksheets("Master").Cells(lastrow + 1, 106).Value = TextShipToZipCode
Worksheets("Master").Cells(lastrow + 1, 107).Value = TextShipToCountry
Worksheets("Master").Cells(lastrow + 1, 108).Value = TextEndUserName
Worksheets("Master").Cells(lastrow + 1, 109).Value = TextEndUserID
Worksheets("Master").Cells(lastrow + 1, 110).Value = TextAccessEndUserID
Worksheets("Master").Cells(lastrow + 1, 111).Value = TextRansburgReport
Worksheets("Master").Cells(lastrow + 1, 112).Value = TextBGKReport
Worksheets("Master").Cells(lastrow + 1, 113).Value = TextShippedYesorNo
Worksheets("Master").Cells(lastrow + 1, 114).Value = TextShipDate
Worksheets("Master").Cells(lastrow + 1, 115).Value = TextPromiseDateAKAShipDate
Worksheets("Master").Cells(lastrow + 1, 116).Value = TextExpectedShipDateAKARecognizeRevenue
Worksheets("Master").Cells(lastrow + 1, 117).Value = TextStatusUpdated
End If
End Sub
'Clear Form Button'
Private Sub ClearForm_Click()
For Each ctrl In Controls
        If TypeName(ctrl) = "TextBox" Then ctrl.Value = ""
Next ctrl
End Sub
'Delete Order Button'
'The first section of code deletes the record from the worksheet'
Private Sub DeleteOrder_Click()
    Dim Shop_Order_Number As String
    Shop_Order_Number = Trim(TextShopOrderNumber)
On Error Resume Next
    With Worksheets("Master")
i = WorksheetFunction.Match(Shop_Order_Number, .Range("D:D"), 0)
If Err.Number = 0 Then
.Rows(i).Delete
Else
Err.Clear
MsgBox "Unknown Shop Order Number", vbInformation, Shop_Order_Number
Exit Sub
End If
End With
'The following code clears the textboxes'
For Each ctrl In Controls
        If TypeName(ctrl) = "TextBox" Then ctrl.Value = ""
Next ctrl
End Sub
 
Excellent! It works now. Thank you so very, very much!

See my revised code...

Code: 
'Update and Save Button'
Private Sub UpdateandSaveOrder_Click()
Dim Shop_Order_Number As String
Dim n As String 'Unlimited characters in Notes textbox
n = TextNotes
Shop_Order_Number = Trim(TextShopOrderNumber)
lastrow = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Worksheets("Master").Cells(i, 4).Value = Shop_Order_Number Then
Worksheets("Master").Cells(i, 1).Value = TextPrefix
Worksheets("Master").Cells(i, 2).Value = TextE10Status
Worksheets("Master").Cells(i, 3).Value = TextSuffix
Worksheets("Master").Cells(i, 4).Value = TextShopOrderNumber
Worksheets("Master").Cells(i, 5).Value = TextEmailSubjectLine
Worksheets("Master").Cells(1, 6).Value = n 'Unlimited characters in Notes textbox
Worksheets("Master").Cells(i, 7).Value = TextStage
Worksheets("Master").Cells(i, 8).Value = TextStartDate
Worksheets("Master").Cells(i, 9).Value = TextStageDue
Worksheets("Master").Cells(i, 10).Value = TextEndDate

So, I assume this works because you are setting the dimension to 'As String' which overrides the default properties of a textbox, which is a limited number of characters? Did I guess right?
 

Attachments

  • 2022-08-23_9-18-36.pdf
    32.4 KB · Views: 5
I tried Dim 'As Long' and it got a runtime 13 error. Type mismatch. So, I took a chance and tried 'As String'. I just did a Google search and the 'As Long' data type is for numbers; the 'As String' data type is for alpha characters. Again, thank you so much!
 
You must log in or register to reply here.
Back
Top