Solved

Combo Box in userform

Posted on 2007-11-29
7
4,325 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:roos01
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:
roos01 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now