ComboBox Select changing values in textboxes

C

celnenorocos

New Member
Joined
Feb 24, 2015
Messages
11
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]

2nhqqlv.jpg



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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
TRY IN A COPY

Adjust Sheets("to your lookup sheetname") and specify your range in Range(" ")
I used A1.currentregion thou my guesses are that your lookuptable starts with your fruit in A1. No need to change if you use A1.

Code: 
Private Sub ComboBox1_Change()

Dim myRange As Range
Set myRange = ThisWorkbook.Sheets("Blad1").Range("A1").CurrentRegion

Me.ComboBox2.Clear
Me.ComboBox3.Clear

With Me.ComboBox2
    .AddItem Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, myRange, 2, False)
    .ListIndex = 0
End With
With Me.ComboBox3
    .AddItem Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, myRange, 3, False)
    .ListIndex = 0
End With

End Sub
 
Last edited:
Upvote 0
Error ... when i press in the combobox1 the first letter


Code: 
Private Sub ComboBox1_Change()

Dim myRange As Range
Set myRange = ThisWorkbook.Sheets("intrari").Range("B20").CurrentRegion


Me.ComboBox2.Clear
Me.ComboBox3.Clear


With Me.ComboBox2
    .AddItem Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, myRange, 4, False)
    .ListIndex = 0
End With
With Me.ComboBox3
    .AddItem Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, myRange, 6, False)
    .ListIndex = 0
End With


End Sub

I modified by my needs ... and is givme error : at
Code: 
.AddItem Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, myRange, 4, False)

the error is :
2430zvc.jpg




this is my sheet :
ieqr9l.jpg




and this is my entire code :
Code: 
Private Sub ComboBox1_Change()

Dim myRange As Range
Set myRange = ThisWorkbook.Sheets("intrari").Range("B20").CurrentRegion


Me.ComboBox2.Clear
Me.ComboBox3.Clear


With Me.ComboBox2
    .AddItem Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, myRange, 4, False)
    .ListIndex = 0
End With
With Me.ComboBox3
    .AddItem Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, myRange, 6, False)
    .ListIndex = 0
End With


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.Text
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


'----------------------------------------------------------------------------------------------------------




    '------------------------------------------------------------------------------------------------------
    If ws.Cells(iRow, 1).Value = ws.Cells(iRow - 1, 1).Value Then
        ws.Cells(iRow, 14).Value = ws.Cells(iRow, 5).Value + ws.Cells(iRow - 1, 14).Value
        ws.Cells(iRow - 1, 14).Value = ""
        ws.Cells(iRow - 1, 14).Borders(xlEdgeTop).LineStyle = xlNone
        ws.Cells(iRow - 1, 13).Borders(xlEdgeTop).LineStyle = xlNone
        ElseIf ws.Cells(iRow, 1).Value = ws.Cells(iRow - 2, 1).Value Then
            ws.Cells(iRow, 14).Value = ws.Cells(iRow, 5).Value + ws.Cells(iRow - 2, 14).Value
            ws.Cells(iRow - 2, 14).Value = ""
            ws.Cells(iRow - 2, 14).Borders(xlEdgeTop).LineStyle = xlNone
            ws.Cells(iRow - 1, 13).Borders(xlEdgeTop).LineStyle = xlNone
        Else
        ws.Cells(iRow, 14).Value = ws.Cells(iRow, 5).Value
        ws.Cells(iRow - 1, 13).Borders(xlEdgeTop).LineStyle = xlNone
    End If
        
    '-----------------------------------------------------------------------------------------------------
    If ws.Cells(iRow, 13).Value = ws.Cells(iRow - 1, 15).Value Then
        ws.Cells(iRow, 10).Formula = "=" & ws.Cells(iRow, 5).Address & "+" & ws.Cells(iRow - 1, 16).Address
        ws.Cells(iRow, 16).Formula = "=" & ws.Cells(iRow, 5).Address & "+" & ws.Cells(iRow - 1, 16).Address
        ws.Cells(iRow, 17).Formula = "=" & ws.Cells(iRow, 7).Address & "-" & ws.Cells(iRow, 5).Address & "+" & ws.Cells(iRow - 1, 17).Address
        ws.Cells(iRow, 12).Formula = "=" & ws.Cells(iRow, 7).Address & "-" & ws.Cells(iRow, 5).Address & "+" & ws.Cells(iRow - 1, 17).Address
        ws.Cells(iRow, 18).Formula = "=" & ws.Cells(iRow, 7).Address & "+" & ws.Cells(iRow - 1, 18).Address
        ws.Cells(iRow, 11).Formula = "=" & ws.Cells(iRow, 3).Address & "*" & ws.Cells(iRow, 6).Address & "+" & ws.Cells(iRow - 1, 18).Address
        ws.Cells(iRow, 19).Formula = "=" & ws.Cells(iRow - 1, 19) & "+1"
        ws.Cells(iRow, 20).Formula = "=month(" & ws.Cells(iRow, 1).Address & ")"
        ws.Cells(iRow - 1, 10).Borders(xlEdgeTop).LineStyle = xlNone
        ws.Cells(iRow - 1, 11).Borders(xlEdgeTop).LineStyle = xlNone
        ws.Cells(iRow - 1, 12).Borders(xlEdgeTop).LineStyle = xlNone
        ws.Cells(iRow - 1, 10).ClearContents
        ws.Cells(iRow - 1, 12).ClearContents
        ws.Cells(iRow - 1, 11).ClearContents
        ws.Cells(iRow - 1, 13).ClearContents
        
        Else
        
        ws.Cells(iRow, 10).FormulaR1C1 = "=RC3*RC4"
        ws.Cells(iRow, 16).FormulaR1C1 = "=RC3*RC4"
        ws.Cells(iRow, 17).Formula = "=" & ws.Cells(iRow, 7).Address & "-" & ws.Cells(iRow, 5).Address
        ws.Cells(iRow, 11).Formula = "=" & ws.Cells(iRow, 3).Address & "*" & ws.Cells(iRow, 6).Address
        ws.Cells(iRow, 12).Formula = "=" & ws.Cells(iRow, 7).Address & "-" & ws.Cells(iRow, 5).Address
        ws.Cells(iRow, 18).Formula = "=" & ws.Cells(iRow, 3).Address & "*" & ws.Cells(iRow, 6).Address
        ws.Cells(iRow, 19).Value = 1
        ws.Cells(iRow, 20).Formula = "=month(" & ws.Cells(iRow, 1).Address & ")"
        End If
    '------------------------------------------------------------------------------------------------------
    
ComboBox1.Text = ""
ComboBox2.Text = ""
ComboBox3.Text = ""
ComboBox4.Text = ""


ComboBox1.SetFocus
End Sub




Private Sub CommandButton3_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


If ws.Cells(iRow - 1, 3) > 0 Then
ws.Cells(iRow, 1).Value = ""
For a = 1 To 19
ws.Cells(iRow, a).Value = 0
ws.Cells(iRow, a).Interior.ColorIndex = 1
Next a
Rows(iRow).RowHeight = 5
Calcul_factura.Label5.Caption = ws.Cells(iRow - 1, 10).Value
Calcul_factura.Label8.Caption = ws.Cells(iRow - 1, 11).Value
Calcul_factura.Label11.Caption = ws.Cells(iRow - 1, 12).Value
Calcul_factura.Label14.Caption = ws.Cells(iRow - 1, 14).Value
Calcul_factura.Label17.Caption = ws.Cells(iRow - 1, 13).Value
Calcul_factura.Label12.Caption = ws.Cells(iRow - 1, 19).Value
Calcul_factura.Label19.Caption = ws.Cells(iRow - 1, 1).Value
Calcul_factura.Show
Else
Calcul_factura.Label5.Caption = ws.Cells(iRow - 2, 10).Value
Calcul_factura.Label8.Caption = ws.Cells(iRow - 2, 11).Value
Calcul_factura.Label11.Caption = ws.Cells(iRow - 2, 12).Value
Calcul_factura.Label14.Caption = ws.Cells(iRow - 2, 14).Value
Calcul_factura.Label17.Caption = ws.Cells(iRow - 2, 13).Value
Calcul_factura.Label12.Caption = ws.Cells(iRow - 2, 19).Value
Calcul_factura.Label19.Caption = ws.Cells(iRow - 2, 1).Value
Calcul_factura.Show
End If
End Sub






Private Sub UserForm_Click()


End Sub
 
Last edited:
Upvote 0
You have to define your lookuparea different.
Do you have anything else under your lookuptable?
 
Upvote 0
is populated with RowSource : sheet1!:B20:B50000 .

No, i dont have, i have only that menu and nothing else in lookuptable.
 
Upvote 0
Ok, try in a copy

Code: 
[COLOR=#333333]Private Sub ComboBox1_Change()[/COLOR]
Dim myRange As Range
Set myRange = ThisWorkbook.Sheets("intrari").Range("B20:G50000")


Me.ComboBox2.Clear
Me.ComboBox3.Clear

on error resume next
With Me.ComboBox2
    .AddItem Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, myRange, 4, False)
    .ListIndex = 0
End With
With Me.ComboBox3
    .AddItem Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, myRange, 6, False)
    .ListIndex = 0
End With
on error goto 0

 [COLOR=#333333]End Sub[/COLOR]
 
Upvote 0
the ugly thing is ...is populating the combox 2 and 3 with the first values found it ... i need to be last ones .Is possible?
if i have :
Apple 2 3
Apple 2 4

is populating with 2 and 3 ..not second row 2 and 4 ... :(

this is the right code for the table i have.

Code: 
Private Sub ComboBox1_Change()Dim myRange As Range
Set myRange = ThisWorkbook.Sheets("intrari").Range("B20:F50000")




Me.ComboBox3.Clear
Me.ComboBox4.Clear


On Error Resume Next
With Me.ComboBox3
    .AddItem Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, myRange, 3, False)
    .ListIndex = 0
End With
With Me.ComboBox4
    .AddItem Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, myRange, 5, False)
    .ListIndex = 0
End With
On Error GoTo 0


 End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Populating combobox from table based on the header selected in another combobox (some data on different sheets)
Replies
7
Views
584
BWL388
B
A
  • Question Question
expand code to search on userform more than column by selection from combobox
Replies
1
Views
651
abdelfattah
A
K
  • Solved
Change ComboBox list values depending on the selected OptionButton
Replies
2
Views
884
Kakaska
K
O
  • Solved
VBA to Split Duplicate rows and change values
Replies
1
Views
454
HongRu
HongRu
M
  • Solved
populate values in textbox based on optionbutton doesn't work
2
Replies
13
Views
836
dmt32
D

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

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