Update information from a combobox :? PLZ help

[Rayyan91]

Hello everyone


I would like some help on a project that i am working on using excel listbox userforms.
The point of the userform is to edit/update the data on the master sheet.
As you can you see below just by clicking on any of the rows the data will show in the text box and combo box.

[/URL][/IMG]

The problem is:


When i edit the data through textbox it will update but not the combobox where it will keep the same information
i entered the first time and won't update.
For example: if i update the project # from X2 to XX it will work. However if I want to select customer 5 instead of customer 2
from the combobox it will keep customer 2 every time i click on the update button.

Something to note that I created Userform_Initialize to call the values from different sheet "LISTS"
to create the rowsource for the comboboxes.


I have the whole code i developed below to help.

Private Sub CommandButton1_Click()
Dim row As Integer
Dim x As Integer


Dim WS As Worksheet
Set WS = Worksheets("Master")


row = Application.WorksheetFunction.CountA(WS.Range("A:A"))


For x = 10 To row
If WS.Cells(x, "A").Value = Me.txtIndex.Text Then


WS.Cells(x, "B").Value = Me.txt1.Text
'WS.Cells(x, "F").Value = Me.txt1.Text
WS.Cells(x, "G").Value = Me.txt4.Text
WS.Cells(x, "I").Value = Me.txt5.Text
WS.Cells(x, "J").Value = Me.txt3.Text
'WS.Cells(x, "B").Value = Me.txt1.Text
'WS.Cells(x, "B").Value = Me.txt1.Text
WS.Cells(x, "D").Value = Me.cbo1.Text


End If


Next


End Sub




Private Sub ListBox1_Click()


txtIndex.Text = Me.ListBox1.List(ListBox1.ListIndex, 0)
txt1.Text = Me.ListBox1.List(ListBox1.ListIndex, 1) 'Proj#
txt2.Text = Me.ListBox1.List(ListBox1.ListIndex, 7) 'Model
txt3.Text = Me.ListBox1.List(ListBox1.ListIndex, 9) 'EngDrw
txt4.Text = Me.ListBox1.List(ListBox1.ListIndex, 6) 'Proj.Des.
txt5.Text = Me.ListBox1.List(ListBox1.ListIndex, 8) 
txt6.Text = Me.ListBox1.List(ListBox1.ListIndex, 10) 
txt7.Text = Me.ListBox1.List(ListBox1.ListIndex, 18)


cbo1.Text = Me.ListBox1.List(ListBox1.ListIndex, 3)
cbo2.Value = Me.ListBox1.List(ListBox1.ListIndex, 4)




End Sub




Private Sub UserForm_Initialize()


Dim cCust As Range
Dim cProdType As Range
Dim cTopOp As Range ' define top option
Dim cBottomOp As Range
Dim cFloating As Range
Dim cVacum As Range
Dim cStFace As Range
Dim cDW As Range








Dim WS As Worksheet
Set WS = Worksheets("LISTS")


For Each cCust In WS.Range("Customers")
    With Me.cbo1
    .AddItem cCust.Value
    .List(.ListCount - 1, 1) = cCust.Offset(4, 5).Value
    End With
Next cCust
For Each cProdType In WS.Range("Prod_Type")
   With Me.cbo2
   .AddItem cProdType.Value
   End With
Next cProdType




End Sub

I hope my explanation to the problem was clear Lol

Thank you in advance.

 

[dmt32]

Hi,
You are using RowSource to populate your comboboxes & need to disconnect this in your code before you perform the update.

example

Private Sub CommandButton1_Click()
    Dim row As Integer
    Dim x As Integer
    
    Dim WS As Worksheet
    Set WS = Worksheets("Master")
    
'disconnect control
    Me.cbo1.RowSource = ""
    

'Perform Update
    row = Application.WorksheetFunction.CountA(WS.Range("A:A"))
    
    For x = 10 To row
        If WS.Cells(x, "A").Value = Me.txtIndex.Text Then
    
        WS.Cells(x, "B").Value = Me.txt1.Text
        'WS.Cells(x, "F").Value = Me.txt1.Text
        WS.Cells(x, "G").Value = Me.txt4.Text
        WS.Cells(x, "I").Value = Me.txt5.Text
        WS.Cells(x, "J").Value = Me.txt3.Text
        'WS.Cells(x, "B").Value = Me.txt1.Text
        'WS.Cells(x, "B").Value = Me.txt1.Text
        WS.Cells(x, "D").Value = Me.cbo1.Text
    
    End If
    
    Next x
    
're-connect control
    Me.cbo1.RowSource = "Customers"


End Sub

Dave