Solved

Combo Box in userform

Posted on 2007-11-29
7
4,328 Views
Last Modified: 2012-06-27
I am new to VBA and have been trying to develop a form in excel to do some calculations.  I want to use a combo box with text options that will be assigned to a variable in the form of an interger.  I want ALL of my options and variable contained within the code.  I do not want to reference lists or values from the spreadsheet for my list...I want to have my form ask for multiple conditions and calculate my answer and return a single decimal number back to the spreadsheet...I have the form i have the list for the combo box defined and it works. However i can't figure out how to pass these values into a variable and them manipulate them..
0
Comment
Question by:ToddHawley4984
  • 4
  • 2
7 Comments
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20376667
Hello ToddHawley4984,

here an example piece of code to show you how to load a combobox based on the content of a variable. In this example I used an array called arr.

this arr contains values.
I place the code in Userform_Initialize macro so the combobox will be filled when userform is opened.
Place this code behind the userform code pane.

I hope this works for you
regards,
Jeroen
Private Sub UserForm_Initialize()
Dim arr As Variant
arr = Array("Value1", "Value2", "Value3", "Value4")
ComboBox1.List = arr
End Sub

Open in new window

0
 

Author Comment

by:ToddHawley4984
ID: 20376870
Actually this code was an alternate way of doing what i had already done...In the example i have posted i have the following options in my combobox...They show up like they are suppose to...What i can't figure out is how do i for example assign what i pick in the drop down box to the STRING variable "SF1" that i have declared?  Each string will have a corresponding Decimal coefficient in a hydrology calculation i am working on. So my plan was to assign a value of 1-11 to each text string.then use an if statement to determine what coefficient should be passed into the equations i'm using...I'm basically just not makinig the link between what i pick in the box and assigning it to a variable that i can then reference from other section of the code. Also if a variable is defined what determines if it is a global variable or a variable only used in the current Sub?  I'm just learning this today for the first time...I'm quite fluent in excel but not VBA/VBE
'Userform1 code
Sub InitializeMEComboSF1()
Dim SF1 As String
ComboSF1.AddItem "Woods Light Underbrush"
ComboSF1.AddItem "Woods Medium Underbrush"
ComboSF1.AddItem "Woods Heavy Underbrush"
ComboSF1.AddItem "Grass - Short Grass Prairie"
ComboSF1.AddItem "Grass - Dense Grasses"
ComboSF1.AddItem "Grass - Bermuda Grass"
ComboSF1.AddItem "Natural Range"
ComboSF1.AddItem "Smooth Surfaces (concrete,asphalt,bare soil)"
ComboSF1.AddItem "Fallow (no residue)"
ComboSF1.AddItem "Cultivated Soils <or= 20% Residue Cover"
ComboSF1.AddItem "Cultivated Soils > 20% Residue Cover"
ComboSF1.Value = "Select A Land Condition For First Segment"
 SF1 = ComboSF1.Value
 

Open in new window

0
 
LVL 33

Accepted Solution

by:
Jeroen Rosink earned 50 total points
ID: 20376930
If you want the value to be returned in a variable like
"Woods Medium Underbrush" would be the second item in the list and the variable should be filled with: 2

then use this:
SF1 = ComboSF1.Listindex +1

(you have to add 1 because comboboxes starts counting at 0.

In my example code it would be something like
Private Sub UserForm_Initialize()
'Sub InitializeMEComboSF1()
Dim arr As Variant
Dim SF1 As String
arr = Array("Woods Light Underbrush", "Woods Medium Underbrush", _
"Woods Heavy Underbrus", "Grass - Short Grass Prairie", "Grass - Dense Grasses", _
"Natural Range", "Smooth Surfaces (concrete,asphalt,bare soil)", "Fallow (no residue)", _
"Cultivated Soils <or= 20% Residue Cover", "Select A Land Condition For First Segment")
ComboSF1.List = arr
 
 SF1 = ComboSF1.ListIndex
 MsgBox SF1
 
 
End Sub

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 20377073
Well u can use the code that you want to use...all u need to do is this...

ComboSF1.Style = fmStyleDropDownList

and remove the

ComboSF1.Value = "Select A Land Condition For First Segment"
 SF1 = ComboSF1.Value

this will do the trick for u...
0
 

Author Comment

by:ToddHawley4984
ID: 20377388
I think this is what i'm looking for...however Im a little confused about the difference between the "Private Sub" and "Sub" I assume this has to do with public or private variables...but i'm not sure.  I has the following code in a module. I have in my spreadsheet a button which executes this macro...However if my initialized combo boxes are "private Sub" then this module can't find them...

Sub ShowUserform1()
UserForm1.InitializeMeComboSF1
UserForm1.InitializeMeComboSF2
 
UserForm1.Show
End Sub

Open in new window

0
 

Author Comment

by:ToddHawley4984
ID: 20377474
Since i'm trying to pass values within the code and not back to excel til the end, should i be using functions instead of sub procedures?
0
 

Author Closing Comment

by:ToddHawley4984
ID: 31411761
The effort was excellent....However I do not feel i was able to ask the appropriate questions.  And there for the answers were partially what i was looking for
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question