[RESOLVED] Code each item of ComboBox

[Cesar.romero]

Hey! Beginner in visual basic here. I''ve added a ComboBox Macro to display 4 options in my excel, i want each option to do a different equation but i don't know how to declare the "If" of every option. Here's my code so far (the names "Cedencia, Plástico and Transición are 3 of the options in my list) (the "combobox3.selecteditem = " is what I really don't know what to put)

Sub DropDown3_Change()
'ActiveSheet.Range("C8").Select
ActiveSheet.Range("C8").Select
Yp = ActiveCell
ActiveSheet.Range("C15").Select
RE = ActiveCell
ActiveSheet.Range("F21").Select
A = ActiveCell
ActiveSheet.Range("G21").Select
B = ActiveCell
ActiveSheet.Range("H21").Select
C = ActiveCell
ActiveSheet.Range("I21").Select
F = ActiveCell
ActiveSheet.Range("J21").Select
G = ActiveCell
If combobox3.selecteditem = "Cedencia" Then
Pc = 2 * Yp * ((RE - 1) / (RE ^ 2))
ActiveSheet.Range("D23") = Pc
ElseIf combobox3.selecteditem = "Plástico" Then
Pc = (Yp * ((A / RE) - B)) - C
ActiveSheet.Range("D23") = Pc
ElseIf combobox3.selecteditem = "Transición" Then
Pc = (Yp * ((F / RE) - G))
ActiveSheet.Range("D23") = Pc
Else
Pc = (46.95 * (10 ^ 6)) / (RE * ((RE - 1) ^ 2))
ActiveSheet.Range("D23") = Pc
End If
End Sub

[jmcilhinney]

Firstly, don't post photos. Post code as text, formatted as code and, if appropriate, post screenshots of the UI.

Secondly, images of any sort should be an adjunct to an explanation, not a replacement. ALWAYS provide a FULL and CLEAR explanation of the problem. Don't post as few words as possible and then expect us to work everything else out for ourselves. For instance, this appears to be a macro in Excel but did you bother to mention that anywhere?

Finally, as this is a macro in Excel, it has absolutely nothing to do with VB.NET. Please post your questions in the forum that most closely corresponds to the subject, not the first one in the list. This site has a forum dedicated to Office Development so that's where your Excel/VBA question belongs. I have asked the mods to to move this thread, so please don't double post in the meantime. Do post again to this thread to provide all the missing information, code, etc, though.

[si_the_geek]

Welcome to VBForums

I have moved this thread from the 'VB.Net' forum to the 'Office Development/VBA' forum.

[Cesar.romero]

Thanks for the tips I've corrected all the post. Can you solve my doubt?

[westconn1]

this may be how i would write the procedure, using a select case rather than many else ifs and avoiding selecting many cells to assign values
you could avoid using 2 lines for each case just by assigning the equation values directly to the cell, though there may be some advantage, the way it is, when debugging

Sub DropDown3_Change()
With ActiveSheet
    Yp = .Range("C8")
    RE = .Range("C15")
    a = .Range("F21")
    B = .Range("G21")
    c = .Range("H21")
    F = .Range("I21")
    G = .Range("J21")
    Select Case combobox3.SelectedItem
        Case "Cedencia"
            Pc = 2 * Yp * ((RE - 1) / (RE ^ 2))
            .Range("D23") = Pc
        Case "Plástico"
            Pc = (Yp * ((a / RE) - B)) - c
            .Range("D23") = Pc
        Case "Transición"
            Pc = (Yp * ((F / RE) - G))
            .Range("D23") = Pc
        Case Else
            Pc = (46.95 * (10 ^ 6)) / (RE * ((RE - 1) ^ 2))
            .Range("D23") = Pc
    End Select
End With
End Sub

i have not tested or considered if the equations are valid, just the organization of the code
if you have multiple items selected in the combobox, only the first will be processed using a select case or elseif
if you only want to process a single item from the combobox, possibly use instead

select case combobox3.text

when an item in the combo is clicked it is placed into the text area of the combo, the code may be better placed into the click event of the combobox so that the code runs whenever an item is clicked in the combo

[Cesar.romero]

Thanks a lot, it was really useful! I replaced the line of "ComboBox3.SelectedItem" for the line you gave me below (combobox3.text) and it worked exactly as I wanted. Using the first option (ComboBox3.SelectedItem) appeared a message telling me that the object doesn't support the method.
Also, thanks for the tip of value assignation.