What am I doing Wrong?

MBarrett

New Member
Joined
Jul 29, 2024
Messages
5
Office Version
  1. Prefer Not To Say
Platform
  1. Windows
''''''''''''''''''Code for Initialize'''''''''''''


VBA Code:
Private Sub UserForm_Initialize()
    cmbstatusofworkorder.List = Array("Open", "In Progress", "PM Review", "Billing", "Closed")
    cmbtechnician1assigned.List = Array("Kurt A", "Chris A", "Chris G", "Greg L", "Ilia X", "Jean A", "Butch C", "Lorne B", "Matt G", "Nick C", "Scott G", "Scott R", "Kenny E", "Dennis S", "Brian H", "Bernie M", "John H", "Keith H")
    cmbtechnician2assigned.List = Array("Kurt A", "Chris A", "Chris G", "Greg L", "Ilia X", "Jean A", "Butch C", "Lorne B", "Matt G", "Nick C", "Scott G", "Scott R", "Kenny E", "Dennis S", "Brian H", "Bernie M", "John H", "Keith H")
    cmbnameofproperty.List = Array("010 Foxfire", "101 Birchwood", "102 Brookside", "103 Pinewood", "104 Redgate", "105 Wakefield", "106 Mascoma", "107 Village Green", "108 Whitman Woods", "109 NorthernView", "110 Woodside Haven", "111 Franklin Woods", "311 Ledgwood", "314 Runnells Landing", "321 Reserve at Federal Hill", "322 The Fells", "331 Catamount", "334 Pine Hollow", "336 Frost Resident's", "338 Hollis Crossing", "341 Wind Chimes", "344 Moulton Cove", "347 Webster Woods", "348 River Glen", "349 Bear View", "350 Tranquility", "353 Hollis Pines", "355 Mast Yard West", "356 Riverwalk", "357 Highland Homes", "501 NHCF", "502 Kalla", "505 46 S. Main", "521 2 Pillsbury", "529 Smile", "530 Bo & Pop", "531 Rosenfield", "540 Bindery", "544 TPC", "546 1 GP", "549 Capital Plaza", "551 62 Hall", "553 Granite Center", "555 7 Eagle", "556 Arts Alley", "557 Mascoma Bank", "802 82&84 S. Main", "804 Delta", "805 MVSB", "806 Sulloway", "807 B&G Club")
    cmbsearchby.List = Array("Work Order No", "Name of Property", "Status of Work Order", "Date Submitted", "Technician 1 Assigned", "Technician 2 Assigned", "PO Number")
    Me.Txtdatesubmitted.Value = Format(Date, "MMM-DD-YYYY")
   
    With txtworkorderno
        .Value = Format(Val(Range("D" & Rows.Count).End(xlUp)) + 1, "000000")
        .Enabled = True
       
    End With
   
    Me.txtt1date1.Value = "MM/DD/YYYY"
    Me.txtt1date1.ForeColor = RGB(217, 217, 217)
   
    Me.txtt2date1.Value = "MM/DD/YYYY"
    Me.txtt2date1.ForeColor = RGB(217, 217, 217)
   
  
    Txtsubmittedby.SetFocus
End Sub
Private Sub txtt1date1_Enter()
    If Me.txtt1date1.Value = "MM/DD/YYYY" Then
       Me.txtt1date1.Value = ""
       Me.txtt1date1.ForeColor = RGB(0, 0, 0)
     End If
    
End Sub
Private Sub txtt2date1_Enter()
    If Me.txtt2date1.Value = "MM/DD/YYYY" Then
       Me.txtt2date1.Value = ""
       Me.txtt2date1.ForeColor = RGB(0, 0, 0)
     End If
    
End Sub

Private Sub txtt1date1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Me.txtt1date1.Value = "" Then
       Me.txtt1date1.Value = "MM/DD/YYYY"
       Me.txtt1date1.ForeColor = RGB(217, 217, 217)
    End If
   
    If Me.txtt2date1.Value = "" Then
       Me.txtt2date1.Value = "MM/DD/YYYY"
       Me.txtt2date1.ForeColor = RGB(217, 217, 217)
     End If
    
End Sub


'''''''''''''''''Code for Validation'''''''''''''''
Private Sub txtnameofproperty_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If (KeyAscii >= 65 And KeyAscii <= 90 Or KeyAscii >= 97 And KeyAscii <= 122 Or KeyAscii = 32) Then
        KeyAscii = KeyAscii
    Else
        KeyAscii = 0
    End If

   
End Sub

''''''''''''''''''''Code for Reset''''''''''''''''''''''
Function Reset()
        Dim clt As Control
        For Each clt In Me.Controls
            Select Case TypeName(clt)
                   Case "TextBox"
                        clt.Value = ""
                   Case "OptionButton"
                        clt.Value = False
                   Case "ComboBox"
                        clt.ListIndex = -1
                   End Select
            Next clt
End Function

'''''''''''''''''''''''Code for Save Button''''''''''''''''''''
Private Sub Cmdsave_Click()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("WOLog")
    Dim lr As Long
    lr = sh.Range("B" & Rows.Count).End(xlUp).Row + 1
   
''''''''''''''''Validation'''''''''''''''''''''
    If Me.cmbnameofproperty.Value = "" Then
        MsgBox "Please enter the Property Name", vbOKOnly + vbInformation
        Exit Sub
    End If
       
    If Me.txtdescriptionofrequest.Value = "" Then
        MsgBox "Please enter the Description of Request", vbOKOnly + vbInformation
        Exit Sub
    End If
       
    If Me.txtresolutionofrequest.Value = "" Then
        MsgBox "Please enter Resolution of Request", vbOKOnly + vbInformation
        Exit Sub
    End If
   
    If Me.txtt1starttime1.Value = "" Then
        MsgBox "Please enter Start Time", vbOKOnly + vbInformation
        Exit Sub
    End If
   
    If Me.txtt1endtime1.Value = "" Then
        MsgBox "Please enter End Time", vbOKOnly + vbInformation
        Exit Sub
    End If
   
    If Application.WorksheetFunction.CountIf(sh.Range("B2:AV1500"), Me.txtworkorderno.Text) > 0 Then
        MsgBox "Data Already Exits", vbOKOnly + vbInformation, "Error"
    End If
   
    If MsgBox("Do you want to add data to worksheet?", vbYesNo + vbQuestion, "Question") = vbNo Then
        Exit Sub
    End If
    '''''''''''''''''''''''''''''''Add data in Excel Sheet''''''''''''''''''''''''''''''
   
    With sh
        .Cells(lr + 1, "B").Value = Me.Txtdatesubmitted.Value
        .Cells(lr + 1, "C").Value = Me.Txtsubmittedby.Value
        .Cells(lr + 1, "D").Value = Me.txtworkorderno.Value
        .Cells(lr + 1, "E").Value = Me.cmbnameofproperty.Value
        .Cells(lr + 1, "F").Value = Me.txttenantorlocation.Value
        .Cells(lr + 1, "G").Value = Me.cmbstatusofworkorder.Value
        .Cells(lr + 1, "H").Value = Me.txtdescriptionofrequest.Value
        .Cells(lr + 1, "I").Value = Me.cmbtechnician1assigned.Value
        .Cells(lr + 1, "J").Value = Me.txtresolutionofrequest.Value
        .Cells(lr + 1, "K").Value = Me.txtt1date1.Value
        .Cells(lr + 1, "L").Value = Me.txtt1date2.Value
        .Cells(lr + 1, "M").Value = Me.txtt1date3.Value
        .Cells(lr + 1, "N").Value = Me.txtt1starttime1.Value
        .Cells(lr + 1, "O").Value = Me.txtt1starttime2.Value
        .Cells(lr + 1, "P").Value = Me.txtt1starttime3.Value
        .Cells(lr + 1, "Q").Value = Me.txtt1endtime1.Value
        .Cells(lr + 1, "R").Value = Me.txtt1endtime2.Value
        .Cells(lr + 1, "S").Value = Me.txtt1endtime3.Value
        .Cells(lr + 1, "T").Value = Me.txtt1hours1.Value
        .Cells(lr + 1, "U").Value = Me.txtt1hours2.Value
        .Cells(lr + 1, "V").Value = Me.txtt1hours3.Value
        .Cells(lr + 1, "W").Value = Me.txtt1hourlyrate.Value
        .Cells(lr + 1, "X").Value = Me.txtitemname1.Value
        .Cells(lr + 1, "Y").Value = Me.txtitemname2.Value
        .Cells(lr + 1, "Z").Value = Me.txtitemname3.Value
        .Cells(lr + 1, "AA").Value = Me.txtquantity1.Value
        .Cells(lr + 1, "AB").Value = Me.txtquantity2.Value
        .Cells(lr + 1, "AC").Value = Me.txtquantity3.Value
        .Cells(lr + 1, "AD").Value = Me.txtcostperitem1.Value
        .Cells(lr + 1, "AE").Value = Me.txtcostperitem2.Value
        .Cells(lr + 1, "AF").Value = Me.txtcostperitem3.Value
        .Cells(lr + 1, "AG").Value = Me.txtponumber.Value
            If optyes.Value = True Then
            .Cells(lr + 1, "AH").Value = "Yes"
            End If
            If optno.Value = True Then
            .Cells(lr + 1, "AH").Value = "No"
            End If
        .Cells(lr + 1, "AI").Value = Me.txtt2date1.Value
        .Cells(lr + 1, "AJ").Value = Me.txtt2date2.Value
        .Cells(lr + 1, "AK").Value = Me.txtt2date3.Value
        .Cells(lr + 1, "AL").Value = Me.txtt2starttime1.Value
        .Cells(lr + 1, "AM").Value = Me.txtt2starttime2.Value
        .Cells(lr + 1, "AN").Value = Me.txtt2starttime3.Value
        .Cells(lr + 1, "AO").Value = Me.txtt2endtime1.Value
        .Cells(lr + 1, "AP").Value = Me.txtt2endtime2.Value
        .Cells(lr + 1, "AQ").Value = Me.txtt2endtime3.Value
        .Cells(lr + 1, "AR").Value = Me.txtt2hours1.Value
        .Cells(lr + 1, "AS").Value = Me.txtt2hours2.Value
        .Cells(lr + 1, "AT").Value = Me.txtt2hours3.Value
        .Cells(lr + 1, "AU").Value = Me.txtt2hourlyrate.Value
    End With
       
End Sub

'''''''''''''''''Code for Reset Button'''''''''''''''''''''''''
Private Sub cmdReset_Click()
    If MsgBox("Do you want to reset the data?", vbYesNo + vbQuestion, "Question") = vbYes Then
        Call Reset
        Call UserForm_Initialize
        Txtsubmittedby.SetFocus
        Exit Sub
    End If
End Sub
'''''''''''''''''Code for Exit Button'''''''''''''''''''''''''
Private Sub cmdexit_Click()
 If MsgBox("Do you want to exit the form?", vbYesNo + vbQuestion, "Question") = vbYes Then
    Unload Me
    End If

End Sub

'''''''''''''''''Code for Search Button'''''''''''''''''''''''''

Private Sub cmdSearch_Click()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("WOLog")
   
    Dim lr As Long
    lr = sh.Range("B" & Rows.Count).End(xlUp).Row
    Dim i As Long
   
              
        If Application.WorksheetFunction.CountIf(sh.Range("B4:AV1500"), Me.txtsearch.Text) = 0 Then
            MsgBox "No Match Found", vbOKOnly + vbInformation, "Error"
     
        Call Reset
        Exit Sub
    End If
   
    For i = 4 To lr
        If sh.Cells(i, "B").Value = Me.txtsearch.Text Or sh.Cells(i, "B").Value = Me.txtsearch.Text Or sh.Cells(i, "C").Value = Me.txtsearch.Text Or sh.Cells(i, "D").Value = Me.txtsearch.Text Or sh.Cells(i, "E").Value = Me.txtsearch.Text Or sh.Cells(i, "F").Value = Me.txtsearch.Text Or sh.Cells(i, "G").Value = Me.txtsearch.Text Or sh.Cells(i, "I").Value = Me.txtsearch.Text Or sh.Cells(i, "AV").Value = Me.txtsearch.Text Then
        Txtdatesubmitted = sh.Cells(i, "B").Value
        Txtsubmittedby = sh.Cells(i, "C").Value
        txtworkorderno = sh.Cells(i, "D").Value
        cmbnameofproperty = sh.Cells(i, "E").Value
        txttenantorlocation = sh.Cells(i, "F").Value
        cmbstatusofworkorder = sh.Cells(i, "G").Value
        txtdescriptionofrequest = sh.Cells(i, "H").Value
        cmbtechnician1assigned = sh.Cells(i, "I").Value
        txtresolutionofrequest = sh.Cells(i, "J").Value
        txtt1date1 = sh.Cells(i, "K").Value
        txtt1date2 = sh.Cells(i, "L").Value
        txtt1date3 = sh.Cells(i, "M").Value
        txtt1starttime1 = sh.Cells(i, "N").Value
        txtt1starttime2 = sh.Cells(i, "O").Value
        txtt1starttime3 = sh.Cells(i, "P").Value
        txtt1endtime1 = sh.Cells(i, "Q").Value
        txtt1endtime2 = sh.Cells(i, "R").Value
        txtt1endtime3 = sh.Cells(i, "S").Value
        txtt1hours1 = sh.Cells(i, "T").Value
        txtt1hours2 = sh.Cells(i, "U").Value
        txtt1hours3 = sh.Cells(i, "V").Value
        txtt1hourlyrate = sh.Cells(i, "W").Value
        txtitemname1 = sh.Cells(i, "X").Value
        txtitemname2 = sh.Cells(i, "Y").Value
        txtitemname3 = sh.Cells(i, "Z").Value
        txtquantity1 = sh.Cells(i, "AA").Value
        txtquantity2 = sh.Cells(i, "AB").Value
        txtquantity3 = sh.Cells(i, "AC").Value
        txtcostperitem1 = sh.Cells(i, "AD").Value
        txtcostperitem2 = sh.Cells(i, "AE").Value
        txtcostperitem3 = sh.Cells(i, "AF").Value
        txtponumber = sh.Cells(i, "AG").Value
            If sh.Cells(i, "AH").Value = Yes Then
            optyes.Value = "True"
            End If
            If sh.Cells(i, "AH").Value = No Then
            optno.Value = "True"
            End If
        txtt2date1 = sh.Cells(i, "AI").Value
        txtt2date2 = sh.Cells(i, "AJ").Value
        txtt2date3 = sh.Cells(i, "AK").Value
        txtt2starttime1 = sh.Cells(i, "AL").Value
        txtt2starttime2 = sh.Cells(i, "AM").Value
        txtt2starttime3 = sh.Cells(i, "AN").Value
        txtt2endtime1 = sh.Cells(i, "AO").Value
        txtt2endtime2 = sh.Cells(i, "AP").Value
        txtt2endtime3 = sh.Cells(i, "AQ").Value
        txtt2hours1 = sh.Cells(i, "AR").Value
        txtt2hours2 = sh.Cells(i, "AS").Value
        txtt2hours3 = sh.Cells(i, "AT").Value
        txtt2hourlyrate = sh.Cells(i, "AU").Value
        cmbtechnician2assigned = sh.Cells(i, "AV").Value
       
    End If
Next i
Me.txtt1date1.ForeColor = RGB(0, 0, 0)
Me.txtt2date1.ForeColor = RGB(0, 0, 0)
End Sub

''''''''''''''''''''''''''''''Code for Update Button''''''''''''''

Private Sub cmdUpdate_Click()

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("WOLog")
    Dim lr As Long
    lr = sh.Range("B" & Rows.Count).End(xlUp).Row
    Dim i As Long
   
   
''''''''''''''''Validation'''''''''''''''''''''
    If Me.txtnameofproperty.Value = "" Then
        MsgBox "Please enter the Property Name", vbOKOnly + vbInformation
        Exit Sub
    End If
       
    If Me.txtdescriptionofrequest.Value = "" Then
        MsgBox "Please enter the Description of Request", vbOKOnly + vbInformation
        Exit Sub
    End If
       
    If Me.txtresolutionofrequest.Value = "" Then
        MsgBox "Please enter Resolution of Request", vbOKOnly + vbInformation
        Exit Sub
    End If
   
    If Me.txtstarttime1.Value = "" Then
        MsgBox "Please enter Start Time", vbOKOnly + vbInformation
        Exit Sub
    End If
   
    If Me.txtendtime1.Value = "" Then
        MsgBox "Please enter End Time", vbOKOnly + vbInformation
        Exit Sub
    End If
      
    If MsgBox("Do you want to update the data?", vbYesNo + vbQuestion, "Question") = vbNo Then
        Exit Sub
    End If
    '''''''''''''''''''''''''''''''Update data in Excel Sheet''''''''''''''''''''''''''''''
    For i = 4 To lr
    If sh.Cells(i, "D").Value = txtsearch.Text Then
       With sh
        .Cells(i, "B").Value = Me.Txtdatesubmitted.Value
        .Cells(i, "C").Value = Me.Txtsubmittedby.Value
        .Cells(i, "D").Value = Me.txtworkorderno.Value
        .Cells(i, "E").Value = Me.txtnameofproperty.Value
        .Cells(i, "F").Value = Me.txttenantorlocation.Value
        .Cells(i, "G").Value = Me.cmbstatusofworkorder.Value
        .Cells(i, "H").Value = Me.txtdescriptionofrequest.Value
        .Cells(i, "I").Value = Me.cmbtechnician1assigned.Value
        .Cells(i, "J").Value = Me.txtresolutionofrequest.Value
        .Cells(i, "K").Value = Me.txtt1date1.Value
        .Cells(i, "L").Value = Me.txtt1date2.Value
        .Cells(i, "M").Value = Me.txtt1date3.Value
        .Cells(i, "N").Value = Me.txtt1starttime1.Value
        .Cells(i, "O").Value = Me.txtt1starttime2.Value
        .Cells(i, "P").Value = Me.txtt1starttime3.Value
        .Cells(i, "Q").Value = Me.txtt1endtime1.Value
        .Cells(i, "R").Value = Me.txtt1endtime2.Value
        .Cells(i, "S").Value = Me.txtt1endtime3.Value
        .Cells(i, "T").Value = Me.txtt1hours1.Value
        .Cells(i, "U").Value = Me.txtt1hours2.Value
        .Cells(i, "V").Value = Me.txtt1hours3.Value
        .Cells(i, "W").Value = Me.txtt1hourlyrate.Value
        .Cells(i, "X").Value = Me.txtitemname1.Value
        .Cells(i, "Y").Value = Me.txtitemname2.Value
        .Cells(i, "Z").Value = Me.txtitemname3.Value
        .Cells(i, "AA").Value = Me.txtquantity1.Value
        .Cells(i, "AB").Value = Me.txtquantity2.Value
        .Cells(i, "AC").Value = Me.txtquantity3.Value
        .Cells(i, "AD").Value = Me.txtcostperitem1.Value
        .Cells(i, "AE").Value = Me.txtcostperitem2.Value
        .Cells(i, "AF").Value = Me.txtcostperitem3.Value
        .Cells(i, "AG").Value = Me.txtponumber.Value
            If optyes.Value = True Then
            .Cells(i, "AH").Value = "Yes"
            End If
            If optno.Value = True Then
            .Cells(i, "AH").Value = "No"
            End If
         Cells(i, "AI").Value = Me.txtt1date1.Value
        .Cells(i, "AJ").Value = Me.txtt1date2.Value
        .Cells(i, "AK").Value = Me.txtt1date3.Value
        .Cells(i, "AL").Value = Me.txtt1starttime1.Value
        .Cells(i, "AM").Value = Me.txtt1starttime2.Value
        .Cells(i, "AN").Value = Me.txtt1starttime3.Value
        .Cells(i, "AO").Value = Me.txtt1endtime1.Value
        .Cells(i, "AP").Value = Me.txtt1endtime2.Value
        .Cells(i, "AQ").Value = Me.txtt1endtime3.Value
        .Cells(i, "AR").Value = Me.txtt1hours1.Value
        .Cells(i, "AS").Value = Me.txtt1hours2.Value
        .Cells(i, "AT").Value = Me.txtt1hours3.Value
        .Cells(i, "AU").Value = Me.txtt1hourlyrate.Value
        .Cells(i, "AV").Value = Me.cmbtechnician2assigned.Value
    End With
    End If
    Next i
    Call Reset
    Call UserForm_Initialize
    Txtsubmittedby.SetFocus
   
End Sub

''''''''''''''''''''''''Code for Delete Button''''''''''''''''''''''''''''
Private Sub cmddelete_Click()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("WOLog")
    Dim lr As Long
    lr = sh.Range("B" & Rows.Count).End(xlUp).Row
    Dim i As Long
   
    If MsgBox("Do you want to delete the data?", vbYesNo + vbQuestion, "Question") = vbNo Then
        Exit Sub
    End If
   
    For i = 4 To lr
        If sh.Cells(i, "D").Value = txtsearch.Text Then
            Rows(i).Delete
        End If
    Next i
    Call Reset
    Call UserForm_Initialize
    Txtsubmittedby.SetFocus
Me.txtt1date1.ForeColor = RGB(0, 0, 0)
Me.txtt2date1.ForeColor = RGB(0, 0, 0)
End Sub

Private Sub cmdprint_Click()
    Me.PrintForm
End Sub
 
Last edited by a moderator:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
First, welcome to the forum.
Second, when posting code, please place the code in a code bracket. This can be done by clicking the "</>" icon when typing a response. (It makes it a lot easier to read, and you will be more likely to get help if you do).
Third, when running your code, you should get the error, and a message box that directs you to exactly where the error is, and highlights it for you in the VBA Editor.
Where is the code directing you to when this happens?
 
Upvote 0
Thank you for the tips. I will do that from now on. When I select Run Sub/Userform, it switches to my userform and gives me the error message "Run time error '424' Object required." When I hit the OK button it does not highlight anything in my code.
 
Upvote 0
First, welcome to the forum.
Second, when posting code, please place the code in a code bracket. This can be done by clicking the "</>" icon when typing a response. (It makes it a lot easier to read, and you will be more likely to get help if you do).
Third, when running your code, you should get the error, and a message box that directs you to exactly where the error is, and highlights it for you in the VBA Editor.
Where is the code directing you to when this happens?
Thank you for the tips. I will do that from now on. When I select Run Sub/Userform, it switches to my userform and gives me the error message "Run time error '424' Object required." When I hit the OK button it does not highlight anything in my code.
 
Upvote 0
What error do you get when you try to run the userform from inside of VBA Editor? As in, clicking the green triangle at the top?
 
Upvote 0
What error do you get when you try to run the userform from inside of VBA Editor? As in, clicking the green triangle at the top?
"Run time error '424' Object required." When I hit the OK button it does not highlight anything in my code.
 
Upvote 0

Forum statistics

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top