Updating database using userform dependent combobox selection (VBA)

thus77

New Member
Joined
Dec 7, 2017
Messages
1
Hi all,

I am new to post on this forum, but I have referred to this site on various occasions.

Am also a VBA illiterate but I have some how made my Userform work.

So here's my question.

How can I update the database using the form which has dependent combo box selection?

- I have created a userform which looks up data from a sheet based on:
1. unique customer name (column T) list : upon selection of which returns a list of the second combo box "First name"
2. "first name" selection populates the form

This works perfectly well upto here. But when I try to change the "customer name" selection, the "First name" remains the list of the first choice.

Also if i try to update the form, the first row of the filtered data gets updated, not the corresponding row...


I need help:
- to make first name list change based on the "Customer Name" selection
- to be able to update the right row on the database using the form

have just pasted the partial code...

Code:
Sub CmbCustomer_DropButt*******() button click


If CmbCustomer.ListCount = 0 Then
Worksheets("Data").Activate
Dim r As Range
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For Each r In Range("T2", Range("T" & Rows.Count).End(xlUp))
        If Not IsEmpty(r) And Not .exists(r.Value) Then .Add r.Value, Nothing
    Next
    Me.CmbCustomer.List = .keys
End With
End If
End Sub


Private Sub CmbCustomer_Change()
Worksheets("Data").Activate
    With ActiveSheet
        .AutoFilterMode = False
        With .Range("T1", Range("a" & Rows.Count).End(xlUp))
            .AutoFilter
            If Len(Me.CmbCustomer.Value) > 0 Then
                .AutoFilter Field:=20, Criteria1:=Me.CmbCustomer.Value
            End If
End With
End With


End Sub


    Sub CmbFirstName_DropButt*******() button click
    If CmbCustomer = "" Then
    MsgBox "Select Customer Name"
    Else:
    If CmbFirstName.ListCount = 0 Then
       
       Worksheets("Data").Activate
               Dim rCell As Range, rVisibles As Range
    
               With Sheets("Data")
                   Set rVisibles = .Range("x2", .Cells(Rows.Count, "x").End(xlUp)).SpecialCells(xlCellTypeVisible)
               End With
               For Each rCell In rVisibles
                   Me.CmbFirstName.AddItem rCell.Value
                    
               Next rCell
       End If


        End If
    End Sub


Private Sub cmbfirstname_change()


    Worksheets("Data").Activate
    Dim lngDataRow As Long
    lngDataRow = Application.Match(Me.CmbFirstName.Value, Worksheets("Data").Range("X:X"), 0)
    
        If Worksheets("Data").Range("A" & lngDataRow).Value = 1 Then Me.cboCustStat.Value = "Active"
        If Worksheets("Data").Range("A" & lngDataRow).Value = 2 Then Me.cboCustStat.Value = "Prospect"
        If Worksheets("Data").Range("A" & lngDataRow).Value = 0 Then Me.cboCustStat.Value = "Closed"
        
        If Worksheets("Data").Range("B" & lngDataRow).Value = 1 Then Me.cmbPriceList.Value = "Yes"
        If Worksheets("Data").Range("B" & lngDataRow).Value = "" Then Me.cmbPriceList.Value = "No"
        
        If Worksheets("Data").Range("C" & lngDataRow).Value = 1 Then Me.cmbMailingEvent.Value = "Yes"
        If Worksheets("Data").Range("C" & lngDataRow).Value = "" Then Me.cmbMailingEvent.Value = "No"
        
        If Worksheets("Data").Range("D" & lngDataRow).Value = 1 Then Me.CmbBauma.Value = "Yes"
        If Worksheets("Data").Range("D" & lngDataRow).Value = "" Then Me.CmbBauma.Value = "No"
        
        If Worksheets("Data").Range("E" & lngDataRow).Value = 1 Then Me.cboGforce.Value = "Yes"
        If Worksheets("Data").Range("E" & lngDataRow).Value = "" Then Me.cboGforce.Value = "No"
        
        If Worksheets("Data").Range("G" & lngDataRow).Value = 1 Then Me.cboMail.Value = "Yes"
        If Worksheets("Data").Range("G" & lngDataRow).Value = "" Then Me.cboMail.Value = "No"
       
        If Worksheets("Data").Range("J" & lngDataRow).Value = 1 Then Me.cmbMailingParts.Value = "Yes"
        If Worksheets("Data").Range("J" & lngDataRow).Value = "" Then Me.cmbMailingParts.Value = "No"
        
        If Worksheets("Data").Range("L" & lngDataRow).Value = 1 Then Me.CmbMailHigh.Value = "Yes"
        If Worksheets("Data").Range("L" & lngDataRow).Value = "" Then Me.CmbMailHigh.Value = "No"
        
        If Worksheets("Data").Range("M" & lngDataRow).Value = 1 Then Me.CmbMail30.Value = "Yes"
        If Worksheets("Data").Range("M" & lngDataRow).Value = "" Then Me.CmbMail30.Value = "No"
        
        If Worksheets("Data").Range("N" & lngDataRow).Value = 1 Then Me.cmbTurnover.Value = "Yes"
        If Worksheets("Data").Range("N" & lngDataRow).Value = "" Then Me.cmbTurnover.Value = "No"
        
        If Worksheets("Data").Range("Q" & lngDataRow).Value = 1 Then Me.CmbDieCast.Value = "Yes"
        If Worksheets("Data").Range("Q" & lngDataRow).Value = "" Then Me.CmbDieCast.Value = "No"
        
        Me.txtAKA.Value = Worksheets("Data").Range("U" & lngDataRow).Value
        Me.cboTitle.Value = Worksheets("Data").Range("W" & lngDataRow).Value
        Me.CmbSurname.Value = Worksheets("Data").Range("Y" & lngDataRow).Value
        Me.txtDesignation.Value = Worksheets("Data").Range("Z" & lngDataRow).Value
        Me.txtEmail1.Value = Worksheets("Data").Range("AA" & lngDataRow).Value
        Me.txtDirectPhone.Value = Worksheets("Data").Range("AB" & lngDataRow).Value
        Me.txtStdPhone.Value = Worksheets("Data").Range("AC" & lngDataRow).Value
        Me.txtMobile.Value = Worksheets("Data").Range("AD" & lngDataRow).Value
        Me.txtFax.Value = Worksheets("Data").Range("AE" & lngDataRow).Value
        Me.txtWebsite.Value = Worksheets("Data").Range("AF" & lngDataRow).Value
        Me.txtAddress.Value = Worksheets("Data").Range("AG" & lngDataRow).Value
        Me.txtAddress2.Value = Worksheets("Data").Range("AH" & lngDataRow).Value
        Me.TxtAddress3.Value = Worksheets("Data").Range("AI" & lngDataRow).Value
        Me.txtCity.Value = Worksheets("Data").Range("AJ" & lngDataRow).Value
        Me.txtState.Value = Worksheets("Data").Range("AK" & lngDataRow).Value
        Me.txtPostcode.Value = Worksheets("Data").Range("AL" & lngDataRow).Value
        Me.cboCountry = Worksheets("Data").Range("AM" & lngDataRow).Value
        Me.cmbLang.Value = Worksheets("Data").Range("AN" & lngDataRow).Value
        Me.cboAssignedTo.Value = Worksheets("Data").Range("AO" & lngDataRow).Value
        Me.cmbEmear.Value = Worksheets("Data").Range("AP" & lngDataRow).Value
        Me.cboCat.Value = Worksheets("Data").Range("AQ" & lngDataRow).Value
        Me.cboPrinSeg.Value = Worksheets("Data").Range("AR" & lngDataRow).Value
        Me.cboOption.Value = Worksheets("Data").Range("As" & lngDataRow).Value
        Me.CboRent.Value = Worksheets("Data").Range("At" & lngDataRow).Value
        Me.txtDlrNme.Value = Worksheets("Data").Range("AU" & lngDataRow).Value
        Me.txtActivity.Value = Worksheets("Data").Range("AV" & lngDataRow).Value
        Me.txtTMS.Value = Worksheets("Data").Range("AW" & lngDataRow).Value
        Me.cmbTMS.Value = Worksheets("Data").Range("AX" & lngDataRow).Value
        Me.CmbSteel.Value = Worksheets("Data").Range("AY" & lngDataRow).Value
        Me.cmbGTH.Value = Worksheets("Data").Range("AZ" & lngDataRow).Value
        Me.txtParts.Value = Worksheets("Data").Range("BA" & lngDataRow).Value
        Me.chkAlum.Value = Worksheets("Data").Range("BB" & lngDataRow).Value
        Me.ChkBoom.Value = Worksheets("Data").Range("BC" & lngDataRow).Value
        Me.ChkScissor.Value = Worksheets("Data").Range("BD" & lngDataRow).Value
        Me.ChkGTH.Value = Worksheets("Data").Range("BE" & lngDataRow).Value
        Me.ChkOBrands.Value = Worksheets("Data").Range("BF" & lngDataRow).Value
        Me.ChkParts.Value = Worksheets("Data").Range("BG" & lngDataRow).Value
        Me.ChkService.Value = Worksheets("Data").Range("BH" & lngDataRow).Value
        Me.ChkTraining.Value = Worksheets("Data").Range("BI" & lngDataRow).Value
        Me.ChkUsed.Value = Worksheets("Data").Range("BJ" & lngDataRow).Value
        Me.TxtLastOb.Value = Worksheets("Data").Range("BK" & lngDataRow).Value
        Worksheets("Data").Range("BL" & lngDataRow).Value = Me.txtLastAns.Value
        Worksheets("Data").Range("BM" & lngDataRow).Value = Me.txtNote.Value
        Worksheets("Data").Range("BN" & lngDataRow).Value = Me.txtListing.Value
        Me.txtCustomer2 = Me.CmbCustomer.Text
        Me.txtFirstName2 = Me.CmbFirstName.Text
        Me.TxtSurname2 = Me.CmbSurname.Text
        Me.txttitle2 = Me.txtDesignation.Text
      
    End Sub

:(
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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