VBA ComboBox Problem?
VBA ComboBox Problem?
(OP)
Hi,
I am a beginner to VBA and have started to write a simple weight calculation program, it goes like this:
Private Sub ComboBox1_Change()
End Sub
Private Sub UserForm_Initialize()
ComboBox1.AddItem "7.85"
ComboBox1.AddItem "2.60"
ComboBox1.AddItem "8.46"
ComboBox1.AddItem "7.22"
ComboBox1.AddItem "0.85"
ComboBox1.AddItem "1.00"
End Sub
Private Sub CommandButton1_Click()
If TextBox3.Text = "" Then
TextBox4.Text = TextBox1.Text * TextBox2.Text
TextBox5.Text = TextBox4.Text / 1000000
TextBox6.Text = TextBox4.Text * ComboBox1.Text * 0.000001
Else
TextBox4.Text = TextBox1.Text * TextBox3.Text * 6.283185
TextBox5.Text = TextBox4.Text / 1000000
TextBox6.Text = TextBox4.Text * ComboBox1.Text * 0.000001
End If
End Sub
Private Sub CommandButton2_Click()
End
End Sub
The problem I would like to resolve is that my CombBox1 has
6 values that I use for multiplying to find a value in one of my TextBox's
What I would like to do is to name the values in my ComboBox i.e Steel, Aluminium, Bronze, Cast-Iron, Oil, Water but still have them hold the multiplication values.
Is there a way of naming them and still letting them retain the values.
I am a beginner to VBA and have started to write a simple weight calculation program, it goes like this:
Private Sub ComboBox1_Change()
End Sub
Private Sub UserForm_Initialize()
ComboBox1.AddItem "7.85"
ComboBox1.AddItem "2.60"
ComboBox1.AddItem "8.46"
ComboBox1.AddItem "7.22"
ComboBox1.AddItem "0.85"
ComboBox1.AddItem "1.00"
End Sub
Private Sub CommandButton1_Click()
If TextBox3.Text = "" Then
TextBox4.Text = TextBox1.Text * TextBox2.Text
TextBox5.Text = TextBox4.Text / 1000000
TextBox6.Text = TextBox4.Text * ComboBox1.Text * 0.000001
Else
TextBox4.Text = TextBox1.Text * TextBox3.Text * 6.283185
TextBox5.Text = TextBox4.Text / 1000000
TextBox6.Text = TextBox4.Text * ComboBox1.Text * 0.000001
End If
End Sub
Private Sub CommandButton2_Click()
End
End Sub
The problem I would like to resolve is that my CombBox1 has
6 values that I use for multiplying to find a value in one of my TextBox's
What I would like to do is to name the values in my ComboBox i.e Steel, Aluminium, Bronze, Cast-Iron, Oil, Water but still have them hold the multiplication values.
Is there a way of naming them and still letting them retain the values.





RE: VBA ComboBox Problem?
Then in the commandbutton1_click procedure use the select...case statement to set your value
Select Case ComboBox1.Value
Case "Steel"
numerical_value = 7.85
Case "Aluminium"
numerical_value = "2.60"
Case "Brass"
numercial_Value = "8.46"
... etc
... etc
... etc
... etc
End Select **Make sure you put this end select statement at the end of your list***
You can then use numerical_value in your calculations rather than combobox1.value
RE: VBA ComboBox Problem?
CODE
With ComboBox1
.AddItem "Steel"
.List(.ListCount-1,1) = "7.85"
.AddItem "Aluminum"
.List(.ListCount-1,1) = "2.60"
.AddItem "Bronze"
.List(.ListCount-1,1) = "8.46"
.AddItem "Cast-Iron"
.List(.ListCount-1,1) = "7.22"
.AddItem "Oil"
.List(.ListCount-1,1) = "0.85"
.AddItem "Water"
.List(.ListCount-1,1) = "1.00"
End With
End Sub
You will also need to change the BoundColumn property of the ComboBox to 2 (again, use the Properties window), which will set the Value of the Combo to the second column entry. You will also need to change the code in the CommandButton1_Click event procedure to use ComboBox1.Value rather than its Text property (since this will be the descriptive entries, not values).
If you want the user to see only the "Steel", "Aluminum", etc. entries and not the numeric values, then set the ColumnWidths property to something like ;0pt
The semicolon above is simply a placeholder for column 1 (in this case) signifying that there is no explicit width for that column. The 0pt makes the second column width zero, effectively hiding it.
Hope this helps,
Mike
RE: VBA ComboBox Problem?
CODE
myarray(0, 0) = "Steel"
myarray(0, 1) = 7.85
myarray(1, 0) = "Aluminium"
myarray(1, 1) = 2.6
myarray(2, 0) = "Brass"
myarray(2, 1) = 8.46
ComboBox1.List = myarray
End Sub
Private Sub ComboBox1_Change()
Range("A1") = (ComboBox1.List(ComboBox1.ListIndex, 1))
End Sub
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
Steam Engine enthusiasts: www.essexsteam.co.uk