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?
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?