-
Aug 16th, 2021, 12:25 AM
#1
Thread Starter
New Member
[RESOLVED] Code each item of ComboBox
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)
Code:
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
Last edited by Cesar.romero; Aug 17th, 2021 at 12:36 AM.
-
Aug 16th, 2021, 12:48 AM
#2
Re: Code each item of ComboBox
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.
-
Aug 16th, 2021, 07:53 AM
#3
Re: Code each item of ComboBox
Welcome to VBForums
I have moved this thread from the 'VB.Net' forum to the 'Office Development/VBA' forum.
-
Aug 17th, 2021, 12:37 AM
#4
Thread Starter
New Member
Re: Code each item of ComboBox
Thanks for the tips I've corrected all the post. Can you solve my doubt?
-
Aug 17th, 2021, 05:52 AM
#5
Re: Code each item of ComboBox
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
Code:
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
Code:
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
Last edited by westconn1; Aug 17th, 2021 at 06:00 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Aug 17th, 2021, 07:11 PM
#6
Thread Starter
New Member
Re: Code each item of ComboBox
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|