Hi All,
First post so go easy on me lol
I'm a complete newb to vba and I have created a user form to enter data to a spreadsheet and that part is working fine with no issues. The problem I'm looking for solution to is that the data entered to the form may need to be updated so I need to be able to add to the data of a specific cells and then save that data.
I hop I have explained this correctly!!
Code below:
First post so go easy on me lol
I'm a complete newb to vba and I have created a user form to enter data to a spreadsheet and that part is working fine with no issues. The problem I'm looking for solution to is that the data entered to the form may need to be updated so I need to be able to add to the data of a specific cells and then save that data.
I hop I have explained this correctly!!
Code below:
VBA Code:
Private Sub UserForm_Initialize()
cmbDeclarationType.AddItem ""
cmbDeclarationType.AddItem "H1"
cmbDeclarationType.AddItem "H2"
cmbDeclarationType.AddItem "H3"
cmbDeclarationType.AddItem "H4"
cmbDeclarationType.AddItem "H5"
cmbDeclarationType.AddItem "H6"
cmbDeclarationType.AddItem "H7"
cmbExamType.AddItem ""
cmbExamType.AddItem "AIS"
cmbExamType.AddItem "Local Profile"
cmbExamType.AddItem "Front Counter"
cmbCaseStatus.AddItem ""
cmbCaseStatus.AddItem "For Exam"
cmbCaseStatus.AddItem "Released after exam"
cmbCaseStatus.AddItem "POP Requested"
cmbCaseStatus.AddItem "POP Received"
cmbCaseStatus.AddItem "Detained"
cmbCaseStatus.AddItem "Seized"
cmbCaseStatus.AddItem "Referred to other area"
cmbCaseStatus.AddItem "Admin Penalty"
cmbCaseStatus.AddItem "Overtime Goods"
cmbCaseStatus.AddItem "RTO"
cmbCaseStatus.AddItem "LOE"
cmbCaseStatus.AddItem "Shortshipped"
cmbReferredTo.AddItem ""
cmbReferredTo.AddItem "HPRA"
cmbReferredTo.AddItem "DAFM"
cmbReferredTo.AddItem "IPR Support Unit"
cmbReferredTo.AddItem "IRAP"
cmbReferredTo.AddItem "IPD"
cmbReferredTo.AddItem "Justice"
cmbReferredTo.AddItem "P&R Unit"
cmbReferredTo.AddItem "CCPC"
cmbReferredTo.AddItem "HSE"
cmbReferredTo.AddItem "ComReg"
End Sub
Private Sub cmdAddNew_Click()
Dim wks As Worksheet
Dim AddNew As Range
Set wks = Sheet3
Set AddNew = wks.Range("A3000").End(xlUp).Offset(1, 0)
AddNew.Offset(0, 0).Value = txtDate.Text
AddNew.Offset(0, 1).Value = txtAirwayBill.Text
AddNew.Offset(0, 2).Value = cmbDeclarationType.Text
AddNew.Offset(0, 3).Value = txtMRNNumber.Text
AddNew.Offset(0, 4).Value = cmbExamType.Text
AddNew.Offset(0, 5).Value = txtCarrier.Text
AddNew.Offset(0, 6).Value = txtOrigin.Text
AddNew.Offset(0, 7).Value = txtConsignee.Text
AddNew.Offset(0, 8).Value = txtContents.Text
AddNew.Offset(0, 9).Value = cmbCaseStatus.Text
AddNew.Offset(0, 10).Value = cmbReferredTo.Text
AddNew.Offset(0, 11).Value = txtLocation.Text
AddNew.Offset(0, 12).Value = txtOldValue.Text
AddNew.Offset(0, 13).Value = txtNewValue.Text
AddNew.Offset(0, 14).Value = txtDuty.Text
AddNew.Offset(0, 15).Value = txtVAT.Text
AddNew.Offset(0, 16).Value = txtComments.Text
AddNew.Offset(0, 17).Value = txtOfficer.Text
lstListBox.ColumnCount = 35
lstListBox.RowSource = "A1:R3000"
lstListBox.ColumnWidths = "50,70,25,100,60,40,30,100,150,90,70,70,50,50,40,40,200,70"
End Sub
Private Sub cmdExit_Click()
Dim iExit As VbMsgBoxResult
iExit = MsgBox("Confirm you want to exit", vbQuestion + vbYesNo, "Search System")
If iExit = vbYes Then
Unload Me
End If
End Sub
Private Sub cmdReset_Click()
Dim txt
For Each txt In Frame2.Controls
If TypeOf txt Is MSForms.TextBox Then
txt.Text = ""
End If
Next txt
txtSearch.Text = ""
cmbDeclarationType.Text = ""
cmbExamType.Text = ""
cmbCaseStatus.Text = ""
cmbReferredTo.Text = ""
End Sub
Private Sub cmdSearch_Click()
Dim iSearch As Long, i As Long
iSearch = Worksheets("Sheet3").Range("B1").CurrentRegion.Rows.Count
For i = 10 To iSearch
If Trim(Sheet3.Cells(i, 2)) <> Trim(txtSearch.Text) And i = iSearch Then
MsgBox ("There is no data that matches your query")
txtSearch.Text = ""
txtSearch.SetFocus
End If
If Trim(Sheet3.Cells(i, 2)) = Trim(txtSearch.Text) Then
txtDate.Text = Sheet3.Cells(i, 1)
txtAirwayBill.Text = Sheet3.Cells(i, 2)
cmbDeclarationType.Text = Sheet3.Cells(i, 3)
txtMRNNumber.Text = Sheet3.Cells(i, 4)
cmbExamType.Text = Sheet3.Cells(i, 5)
txtCarrier.Text = Sheet3.Cells(i, 6)
txtOrigin.Text = Sheet3.Cells(i, 7)
txtConsignee.Text = Sheet3.Cells(i, 8)
txtContents.Text = Sheet3.Cells(i, 9)
cmbCaseStatus.Text = Sheet3.Cells(i, 10)
cmbReferredTo.Text = Sheet3.Cells(i, 11)
txtLocation.Text = Sheet3.Cells(i, 12)
txtOldValue.Text = Sheet3.Cells(i, 13)
txtNewValue.Text = Sheet3.Cells(i, 14)
txtDuty.Text = Sheet3.Cells(i, 15)
txtVAT.Text = Sheet3.Cells(i, 16)
txtComments.Text = Sheet3.Cells(i, 17)
txtOfficer.Text = Sheet3.Cells(i, 18)
Exit For
End If
Next i
End Sub
Private Sub cmdSearch2_Click()
Dim iSearch As Long, i As Long
iSearch = Worksheets("Sheet3").Range("D1").CurrentRegion.Rows.Count
For i = 10 To iSearch
If Trim(Sheet3.Cells(i, 4)) <> Trim(txtSearch2.Text) And i = iSearch Then
MsgBox ("There is no data that matches your query")
txtSearch.Text = ""
txtSearch.SetFocus
End If
If Trim(Sheet3.Cells(i, 4)) = Trim(txtSearch2.Text) Then
txtDate.Text = Sheet3.Cells(i, 1)
txtAirwayBill.Text = Sheet3.Cells(i, 2)
cmbDeclarationType.Text = Sheet3.Cells(i, 3)
txtMRNNumber.Text = Sheet3.Cells(i, 4)
cmbExamType.Text = Sheet3.Cells(i, 5)
txtCarrier.Text = Sheet3.Cells(i, 6)
txtOrigin.Text = Sheet3.Cells(i, 7)
txtConsignee.Text = Sheet3.Cells(i, 8)
txtContents.Text = Sheet3.Cells(i, 9)
cmbCaseStatus.Text = Sheet3.Cells(i, 10)
cmbReferredTo.Text = Sheet3.Cells(i, 11)
txtLocation.Text = Sheet3.Cells(i, 12)
txtOldValue.Text = Sheet3.Cells(i, 13)
txtNewValue.Text = Sheet3.Cells(i, 14)
txtDuty.Text = Sheet3.Cells(i, 15)
txtVAT.Text = Sheet3.Cells(i, 16)
txtComments.Text = Sheet3.Cells(i, 17)
txtOfficer.Text = Sheet3.Cells(i, 18)
Exit For
End If
Next i
End Sub
Private Sub Frame2_Click()
Dim iRow As Long
iRow = [CountA(Database!A1:R3000)]
With UserForm2
.txtDate.Value = ""
.txtAirwayBill.Value = ""
.cmbDeclarationType.Value = ""
.txtMRNNumber.Value = ""
.cmbExamType.Clear
.cmbExamType.AddItem "AIS"
.cmbExamType.AddItem "Local Profile"
.cmbExamType.AddItem "Front Counter"
.cmbExamType.AddItem "Off-Site"
.txtCarrier.Value = ""
.txtOrigin.Value = ""
.txtConsignee.Value = ""
.cmbCaseStatus.Clear
.cmbCaseStatus.AddItem "For Exam"
.cmbCaseStatus.AddItem "Released after exam"
.cmbCaseStatus.AddItem "POP Requested"
.cmbCaseStatus.AddItem "POP Received"
.cmbCaseStatus.AddItem "Detained"
.cmbCaseStatus.AddItem "Seized"
.cmbCaseStatus.AddItem "Referred To"
.cmbCaseStatus.AddItem "Admin Penalty Case"
.cmbCaseStatus.AddItem "RTO"
.cmbCaseStatus.AddItem "Shortshipped"
.cmbCaseStatus.AddItem "Overtime Goods"
.cmbCaseStatus.AddItem "Admin Penalty Case"
.cmbCaseStatus.AddItem "Delivered in error"
.cmbReferredTo.Clear
.cmbReferredTo.AddItem "HPRA"
.cmbReferredTo.AddItem "DAFM"
.cmbReferredTo.AddItem "CCPC"
.cmbReferredTo.AddItem "IRAP"
.cmbReferredTo.AddItem "COMREG"
.cmbReferredTo.AddItem "IPD"
.cmbReferredTo.AddItem "HSE"
.cmbReferredTo.AddItem "Justice"
.cmbReferredTo.AddItem "IPR Support Unit"
.cmbReferredTo.AddItem "P&R Unit"
.txtOldValue = ""
.txtNewValue = ""
.txtDuty.Value = ""
.txtVAT.Value = ""
.txtComments.Value = ""
.txtOfficer.Value = ""
.lstListBox.RowSource = "A1:R3000"
.lstListBox.ColumnHeads = True
If iRow > 1 Then
.lstListBox.RowSource = "Database!A1:R3000" & iRow
Else
.lstListBox.RowSource = "Database!A1:R3000"
End If
End With
End Sub