PDA

View Full Version : [SOLVED] Using a combo box as an input box



AJS
10-10-2004, 04:38 PM
Hi,

I have set up a small userform that I want to use as a generic input box, such that I can grab a range of cells on my worksheet to use as a drop-down list of possible outputs. I have called this userform "GeneralCombo", and it contains the label Label1, the combobox ComboBox1 and the button OKButton. The problem is I need to get the code to pause while waiting for output from the userform. I have set up my code as such:

I have attached the following code to the GeneralCombo userform:


Public GeneralComboLabel As String
Public GeneralComboResult As Variant

Private Sub OKButton_Click()
GeneralComboResult = GeneralCombo.ComboBox1.Value
Unload Me
End Sub

As an example, I would call up the userform with the following sub:


Sub PopAbbrev()
' Populate the list of abbreviations on a combo box
Sheet1.Protect UserInterfaceOnly:=True
ActiveWorkbook.Protect Structure:=True
For i = 1 To Sheet1.Range("C8").Value
GeneralCombo.Controls("ComboBox1").AddItem Sheet1.Range("F" & 7 + i)
Next i
GeneralCombo.Label1.Caption = "Enter the abbreviation of the dimension you wish to " & GeneralComboLabel & "."
GeneralCombo.ComboBox1.ListIndex = 0
GeneralCombo.ComboBox1.SetFocus
GeneralCombo.Show vbModeless
End Sub

and would call the sub via the following at the appropriate point in my code:


GeneralComboLabel = "change"
GeneralComboResult = ""
PopAbbrev
Do Until GeneralComboResult <> ""
Loop


The idea being that GeneralComboResult is the output from selecting an item in the combobox and pressing the OKButton. Problem is, the code freezes with a blank userform when I use the above Do Until...Loop, and keeps on running ahead without user input and eventually crashes without such a loop. I'm missing something pretty basic here - any suggestions?

Jacob Hilderbrand
10-10-2004, 05:37 PM
The code will automatically stop running when a UserForm loads and will not continue until the UserForm is unloaded or hidden. Make sure you declare your public variables in a Module (NOT UserForm) Code Section.


Public GeneralComboResult As Variant

Then call the UserForm


GeneralComboResult = ""
PopAbbrev
MsgBox GeneralComboResult

You will not get the message box until the UserForm is dismissed.

AJS
10-10-2004, 06:22 PM
Thanks for that,

Apart from the location of the public variables, I found another hole in the code - I used the vbModeless option when calling the userform, which allowed the rest of the code to run in background. It's all working fine now!

Jacob Hilderbrand
10-11-2004, 03:36 AM
Glad you got it working.

Take Care