Assigning values to ComboBox items?

[faeter87]

Hi

I'm trying to assign values to the different items in my combobox.
The idea is that the 3 input cells combined with a fourth value from the combobox can be used to calculate the "result".

This is what I've come up with so far, but obviously it's incorrect.
Can anyone please give me some pointers?

Sub Material()

With Range("C8")
Set Comb = ActiveSheet.DropDowns.Add(.Left, .Top, .Width, .Height)
End With

With Comb
.AddItem "material1"
.AddItem "material2"
.AddItem "material3"
End With

material1 = 210000
material2 = 27000
material3 = 3000

q = Range("C6").Value
L = Range("C4").Value
I = Range("C5").Value
w = ((5 * q * L) / (384 * I))

Select Case Comb
Case "material1"
Range("C10").Value = (w / material1)

Case "material2"
Range("C10").Value = (w / material2)

Case "material3"
Range("C10").Value = (w / material3)

End Select

End Sub

[Rob342]

Can you post a copy w/book with some data
are you using a form or dropdowm combo box?

[faeter87]

Can you post a copy w/book with some data
are you using a form or dropdowm combo box?

Hi, and thanks for your reply! Im using a dropdown combo box.

[Rob342]

faeter87

have posted a copy w/book back look at sheet 2
You can this without VBA.


Rob

[faeter87]

faeter87

have posted a copy w/book back look at sheet 2
You can this without VBA.


Rob

Thanks for your efforts. It looks great, and I agree it's much easier that way, but I really need to learn how to do this with VBA.

[Rob342]

faeter87

Good way to start is to buy some good vba books and read some of the posts on here, another good website is www.contextures.com (http://www.contextures.com) and download what you want & play around with it. It's a big learning curve.
I could do this project for you but you wouldn't learn anything by me doing that ,there are so many ways to do the same thing and everybody here does it a different way but with the same result. Tip, Don't re-invent the wheel when somebody has already done it

Rob

[faeter87]

faeter87

Good way to start is to buy some good vba books and read some of the posts on here, another good website is www.contextures.com (http://www.contextures.com) and download what you want & play around with it. It's a big learning curve.
I could do this project for you but you wouldn't learn anything by me doing that ,there are so many ways to do the same thing and everybody here does it a different way but with the same result. Tip, Don't re-invent the wheel when somebody has already done it

Rob

Thanks for the links Rob. I already got a book so I guess I'll have more reading to do!
...And I wasn't asking if someone could do this for me. I would just like some pointers on what's horrible wrong and if I should try to code it in another way, such as multiple procedures instead of a single one.

Cheers!:beerchug:

[Rob342]

faeter

A copy of a userform with combo box controls, This will get you started, if you want to get into userforms & vba.
Tip if you highlight any of the vba words the press F1 this will give you a breakdown of the meanings & use etc.

Rob