×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VBA ComboBox Problem?
3

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.

RE: VBA ComboBox Problem?

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

RE: VBA ComboBox Problem?

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:

CODE

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

RE: VBA ComboBox Problem?

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

CODE

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: www.essexsteam.co.uk

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources