Updating a list box with extra data

R

reelpix

New Member
Joined
Dec 21, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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:






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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
You must log in or register to reply here.

Similar threads

K
  • Question Question
Select the Name in the Data Validation list on a cell value then the same name run the sub macro
Replies
9
Views
461
kpmsivaprakasam2003
K
P
  • Question Question
How to VBA code to drop data from UserForm to multiple worksheets
2
Replies
17
Views
623
dmt32
D
J
  • Solved
VBA 3 Column Table and Display each column separately in 3 list boxes.
Replies
2
Views
270
JulianvO
J
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
238
krunal123
K
R
  • Question Question
Excel Data Entry Form
Replies
1
Views
836
Radiant_Greedy
R

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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