Assign a value to a VBA Combobox choice Solved

Question

Hello,

I am seeking your help as I am currently stuck; I need to assign a value based on the selection from the ComboBox.

A1
Plywood €4
Solid Wood €2

I need that when I select one of the options in my ComboBox (which takes values from column A1), the corresponding price is considered during the calculation.

I am looking to get the price based on the choice in the ComboBox.

I could manually add the price, but I want the program to be modifiable (to change actions and prices, or add new ones).

Currently, I have this for the initialization of my program:

VarDerLigne = Worksheets("parame").Range("A65536").End(xlUp).Row
VarPlage = Worksheets("parame").Range("A9:A" & VarDerLigne).Address
ComboBox1.RowSource = "parame!" & VarPlage
ComboBox1.ColumnHeads = False
ComboBox1.ListIndex = 0

This code allows me to obtain all the values present in column A into my ComboBox.
In my calculations, I seek to have the cost of a surface area, length=Textbox1, width=Textbox2, price per m²=ComboBox1

priceSurface = Textbox1 * Textbox2 * ComboBox1 (but I want to get the price, not the text)

Thank you for reading, and I hope you can solve my problem.

ThauTheme · Answer

Hello everyone, Is the price in column A or in column B? If you attach your file, you'll get more tailored and quicker responses... See you, ThauTheme

vils29 · Answer

Hello ThauTheme and thank you for your response,

The price is in column B and the text is in column A

I don't know how to send an attachment, I posted a mediafire link but my post was immediately deleted.

ThauTheme · Answer

Hi, Check out https://www.cjoint.com/ (for example)... -- See you, ThauTheme

vils29 · Answer

Re, Here is the file, (it's messy but oh well..) http://www.cjoint.com/c/FLwqF7ZBYJa

vils29 · Answer

Je suis désolé, mais je ne peux pas vous aider sans un texte à traduire.

ThauTheme · Answer

Hi, I'm working on it but I'm not sure you'll have an answer today... -- Talk soon, ThauTheme

vils29 · Answer

Re, I found a solution by doing it differently. Thank you for your contribution ThauTheme.

ThauTheme · Answer

Re, By assigning a value to the [Tag] property of each ComboBox. A value that corresponds to the column where it is written (Ex.: ComboBox1.Tag = 1, ComboBox2.Tag = 3, etc.) we would obtain simplified code like: Private O As Worksheet Private COL As Byte Private LI As Integer Sub userform_initialize() Dim CTRL As Control Set O = Sheets("parame") For Each CTRL In Me.Controls If CTRL.Tag <> "" Then COL = CByte(CTRL.Tag) LI = O.Cells(Application.Rows.Count, COL).End(xlUp).Row CTRL.List = O.Range(O.Cells(9, COL), O.Cells(LI, COL)).Value If CTRL.Tag = 11 Then CTRL.List = IIf(CTRL.Name = "ComboBox5", O.Range("K9:K11").Value, O.Range("K12:K14").Value) CTRL.ListIndex = 0 End If Next CTRL ComboBox1.Visible = OptionButton5.Value ComboBox2.Visible = OptionButton6.Value ComboBox3.Visible = OptionButton7.Value OptionButton6.Value = True End Sub Private Sub CommandButton1_Click() Dim longeur As Double, largeur As Double, surface As Double, epaisseur As Double, volume As Double For I = 2 To 4 If Me.Controls("TextBox" & I).Value = "" Then MsgBox "You must fill in this field!": GoTo suite If Not IsNumeric(Me.Controls("TextBox" & I).Value) Then MsgBox "You must enter a numeric value!" Else If CDbl(Me.Controls("TextBox" & I).Value) <= 0 Then MsgBox "You must enter a positive value!" End If suite: With Me.Controls("TextBox" & I) .SetFocus .SelStart = 0 .SelLength = .Value End With Exit Sub Next I For I = 1 To 3 If Me.Controls("ComboBox" & I).Visible = True Then LI = Me.Controls("ComboBox" & I).ListIndex + 9 COL = CByte(Me.Controls("ComboBox" & I).Tag) Exit For End If Next I material = CDbl(O.Cells(LI, COL + 1)) longeur = CDbl(TextBox2.Value) largeur = CDbl(TextBox3.Value) epaisseur = CDbl(TextBox4.Value) 'Inserting variables Workbooks.Add 'Display a new workbook ActiveSheet.Name = ("devis") 'The sheet of the new workbook is renamed to "devis" Range("G3").Select ActiveCell.FormulaR1C1 = TextBox1 'Formatting the title of the quote Range("G3:L3").Select With Selection.Font .ThemeColor = xlThemeColorAccent1 End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .ReadingOrder = xlContext End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Color = -16777024 .TintAndShade = 0 .Weight = xlMedium End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Color = -16777024 .TintAndShade = 0 .Weight = xlMedium End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Color = -16777024 .TintAndShade = 0 .Weight = xlMedium End With With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Color = -16777024 .TintAndShade = 0 .Weight = xlMedium End With surface = longeur * largeur 'Calculations, then entries into cells priceSurface = surface * material Worksheets("devis").Range("F10").Value = CStr(priceSurface) volume = longeur * largeur * epaisseur price = surface End Sub -- See you later, ThauTheme

vils29 · Answer

Thank you, it's working! I managed to continue the rest based on your code. You are saving me, thank you. Resolved

Assign a value to a VBA Combobox choice

9 answers

Phone number bug

Driving at 110 km/h on the highway: fuel economy and safety gains

Cannot connect to wi-fi

Ps project: €0.10/gb tax to fund the networks

Microsoft extended windows 10 support until the end of 2027

How to share your location via sms in case of emergency

No internet connection possible

Cc how to unlock the encrypted channels on my tnt tv

Pairing a smartwatch

Mouse, fullscreen mode