VBA ComboBox Problem? 3

[bdickens]

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.

 

[ab123456]

In your combobox change the additems to "Steel" etc

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

 

[msmith]

As an alternative, you can modify your ComboBox to have 2 columns (use the Properties window in the VBE and change the ColumnCount property to 2). Use the following modified code to populate the Combo:

Private Sub UserForm_Initialize()
   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

 

[johnwm]

Maybe tidier to store the info in an array and load the combobox from that. Then you can reference the List property of the Combo to get the real value (into A1 in this example).

Private Sub UserForm_Initialize()
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:

http://www.essexsteam.co.uk