ComboBox Select changing values in textboxes

[celnenorocos]

I have this for exemple :

[TABLE="width: 500"]
<tbody>[TR]
[TD]Product name[/TD]
[TD]Price buy[/TD]
[TD]Price Sell[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]1[/TD]
[TD]1.3[/TD]
[/TR]
[TR]
[TD]Potato[/TD]
[TD]0.3[/TD]
[TD]0.5
[/TD]
[/TR]
[TR]
[TD]grapes[/TD]
[TD]3[/TD]
[TD]3.5[/TD]
[/TR]
[TR]
[TD]pears[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

So all i need , if is possible in VBA when I m puting in up combox the product Apples, or Pears, to search in my table the product , and changing un other bottom comboboxes values from the table . Comboboxes : upper one is 1 and other to down 2 and 3.

So combox 1 : Pears | => Combobox2 value 2, combobox3 value 4
combox 1 : Apple | => Combobox2 value 1, combobox3 value 1.3
Sry for tha language of the menu, is romanian .... like i said i m not so good on english.

Tnk s in advance.

 

[Stridhan]

give this a try in a copy

Private Sub ComboBox1_Change()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("intrari")
lr = ThisWorkbook.Sheets("intrari").Cells(Rows.Count, 2).End(xlUp).Row

Me.ComboBox3.Clear
Me.ComboBox4.Clear

For x = lr To 2 Step -1
    If ws.Cells(x, 2).Value = Me.ComboBox1.Value Then
        With Me.ComboBox3
            .AddItem ws.Cells(x, 3).Value
            .ListIndex = 0
        End With
        With Me.ComboBox4
            .AddItem ws.Cells(x, 5).Value
            .ListIndex = 0
        End With
            Exit Sub
    End If
Next x

End Sub

 

[celnenorocos]

ok, is changing the value of combobox 3 and 4 the last value found it .... the problem is when i wass tryng to modify a value , or even if i dont change a value from 3 and 4 in this code ....

Private Sub ComboBox1_Change()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("intrari")
lr = ThisWorkbook.Sheets("intrari").Cells(Rows.Count, 2).End(xlUp).Row


Me.ComboBox3.Clear
Me.ComboBox4.Clear


For x = lr To 2 Step -1
    If ws.Cells(x, 2).Value = Me.ComboBox1.Value Then
        With Me.ComboBox3
            .AddItem ws.Cells(x, 4).Value
            .ListIndex = 0
        End With
        With Me.ComboBox4
            .AddItem ws.Cells(x, 6).Value
            .ListIndex = 0
        End With
            Exit Sub
    End If
Next x


End Sub


Private Sub CommandButton1_Click()
Unload Userform1
End Sub


Private Sub CommandButton2_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("intrari")
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
For b = 1 To 14
    ws.Cells(iRow, b).Borders.LineStyle = xlContinuous
    Next b
    
ws.Cells(iRow, 1).Value = TextBox1.Text
ws.Cells(iRow, 2).Value = ComboBox1.Text
ws.Cells(iRow, 3).Value = ComboBox2.Value
ws.Cells(iRow, 4).Value = ComboBox3.Text
ws.Cells(iRow, 5).FormulaR1C1 = "=RC3*RC4"
ws.Cells(iRow, 6).Value = ComboBox4.Text
ws.Cells(iRow, 7).FormulaR1C1 = "=RC3*RC6"
ws.Cells(iRow, 8).Value = ComboBox5.Text
ws.Cells(iRow, 9).FormulaR1C1 = "=(RC7-RC5)/RC5"
ws.Cells(iRow, 13).Value = ComboBox6.Text
ws.Cells(iRow, 15).Value = ComboBox6.Text

when i press the button CommandButton2_Click is not changing the value of the cells : ws.Cells(iRow, 4).Value = ComboBox3.Text ,
or ws.Cells(iRow, 6).Value = ComboBox4.Text. The cells 4 and 6 are empty ...

I tryit to changing the Combobox3.Text to Value, is not working ...

Sry if i m a tedios ..

 

[celnenorocos]

I wass tryng to use a buffer cell :

Private Sub ComboBox1_Change()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("intrari")
lr = ThisWorkbook.Sheets("intrari").Cells(Rows.Count, 2).End(xlUp).Row


Me.ComboBox3.Clear
Me.ComboBox4.Clear


For x = lr To 2 Step -1
    If ws.Cells(x, 2).Value = Me.ComboBox1.Value Then
        With Me.ComboBox3
            .AddItem ws.Cells(x, 4).Value
            .ListIndex = 0
            End With
        With Me.ComboBox4
            .AddItem ws.Cells(x, 6).Value
            .ListIndex = 0
        End With
            Exit Sub
    End If
Next x


End Sub












Private Sub ComboBox3_Change()
Set ws = Worksheets("intrari")
Cells(1, 3) = Me.ComboBox3.Text
End Sub


Private Sub ComboBox4_Change()
Set ws = Worksheets("intrari")
Cells(1, 4) = Me.ComboBox4.Text
End Sub


Private Sub CommandButton2_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("intrari")
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
For b = 1 To 14
    ws.Cells(iRow, b).Borders.LineStyle = xlContinuous
    Next b
    
ws.Cells(iRow, 1).Value = TextBox1.Text
ws.Cells(iRow, 2).Value = ComboBox1.Text
ws.Cells(iRow, 3).Value = ComboBox2.Value
ws.Cells(iRow, 4).Value = Cells(1.3)
ws.Cells(iRow, 5).FormulaR1C1 = "=RC3*RC4"
ws.Cells(iRow, 6).Value = Cells(1, 4)

is putin in cell 1,3 and 1,4 the values , but when i press the button 2 , in irow,4 is putin a strainge value : 42065, and in irow, 6 nothing , and the value for 1,3 and 1,4 are not anymore present.

 

[celnenorocos]

I made a file simplified file with what i m tryng to do...

i dont understand where i m doing wrong , but if i change the value of textboxes after they wass changed in the found values ...in the sheet are inserted blank value cells .. or sometimes same values wass found , even if i changed them.

https://www.dropbox.com/s/ypc9xexgeljcy59/TRY.xlsm?dl=0

To be specific , in combobox i write a product , after inserted in the textbox i need the last values found in colum B and C, maybe i need to change price1 or price 2 , i change them, and when i press the Insert button to in the sheet1 put at the bottom of list the all 3 values.