• 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.

Run-Time Error '424': Object Required

Y

Yodelayheewho

Member
I got this code from a tutorial. I think the item that is throwing the error has to do with the worksheet name. If you look in line four, I replaced 'Sheet1', from the tutorial, with 'Master', which is the name of my worksheet. I think I need to call out the worksheet name but not sure how.

Code: 
'Prevents user from entering duplicate Shop Order Number'
Private Sub TextShopOrderNumber_AfterUpdate()
Dim a As Long
a = Application.WorksheetFunction.CountIf(Master.Range("A:A"), Me.TextShopOrderNumber.Text)
If a >= 1 Then
MsgBox "The Shop Order Number Already Exists. Please Enter a Unique Shop Order Number"
End If
End Sub
 

Attachments

  • Sample_Systems Order Entry Master_VBA.xlsm
    117.4 KB · Views: 4
Was not a good idea as Sheet1 is the right CodeName of the Master worksheet !​
Or use the worksheet name within the Range statement …​
​
 
Belleke, you are awesome! This works perfectly!
Oops, I found an issue. When I tested and entered a Shop Order Number that already exists and hit Search and Display the message popped up. I just realized I need this to happen only if the user enters the number AND clicks on Add New. I want them to be able to search and display as much as they like. I just don't want them to add a new number if the same exists.
 
Last edited:
OH MY GOSH. I think I figured it out! Belleke, I took the code you created and added it to the start of the AddNew button code and it works! Thanks again!
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
 
You must log in or register to reply here.
Back
Top