MBarrett
New Member
- Joined
- Jul 29, 2024
- Messages
- 5
- Office Version
- Prefer Not To Say
- Platform
- 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: