werafa
03-18-2017, 03:26 AM
Hi all,
I've got a userform with a combobox that I set the list items via VBA.
question is:
how do I make the validation list disappear after the code has finished?
I currently have to click the list once to make it go away, and then reopen the list and set the combobox value
tis kinda annoying.
the relevant code is the section following 'myarray = getfittingtype(myString)'
Thanks
Private Sub cbxLampType_AfterUpdate()
'create lookup values for fitting type and balast type
Dim myCbox As ComboBox
Dim myArray() As String
Dim myRow As Long
Dim myString As String
Dim myFlag As Boolean
If enableFormEvents = False Then Exit Sub
myString = frmAssessment.cbxLampType.Value
Set myCbox = frmAssessment.cbxFittingType
myFlag = False
If myString = "" Then Exit Sub 'quit if no lamp type is selected
myArray = GetFittingType(myString)
myCbox.Clear
For myRow = 1 To UBound(myArray) 'set fitting type validation
myCbox.AddItem myArray(myRow)
If myCbox.Value = myArray(myRow) Then myFlag = True
Next myRow
If myFlag = False Then myCbox.Value = ""
Erase myArray()
Set myCbox = frmAssessment.cbxControlGear
myFlag = False
myArray = GetControlGear(myString)
For myRow = 1 To UBound(myArray) 'set control gear validation
myCbox.AddItem myArray(myRow)
If myCbox.Value = myArray(myRow) Then
myFlag = True 'a valid value exists
End If
Next myRow
If myFlag = False Then myCbox.Value = GetDefaultBalast(myString)
End Sub
I've got a userform with a combobox that I set the list items via VBA.
question is:
how do I make the validation list disappear after the code has finished?
I currently have to click the list once to make it go away, and then reopen the list and set the combobox value
tis kinda annoying.
the relevant code is the section following 'myarray = getfittingtype(myString)'
Thanks
Private Sub cbxLampType_AfterUpdate()
'create lookup values for fitting type and balast type
Dim myCbox As ComboBox
Dim myArray() As String
Dim myRow As Long
Dim myString As String
Dim myFlag As Boolean
If enableFormEvents = False Then Exit Sub
myString = frmAssessment.cbxLampType.Value
Set myCbox = frmAssessment.cbxFittingType
myFlag = False
If myString = "" Then Exit Sub 'quit if no lamp type is selected
myArray = GetFittingType(myString)
myCbox.Clear
For myRow = 1 To UBound(myArray) 'set fitting type validation
myCbox.AddItem myArray(myRow)
If myCbox.Value = myArray(myRow) Then myFlag = True
Next myRow
If myFlag = False Then myCbox.Value = ""
Erase myArray()
Set myCbox = frmAssessment.cbxControlGear
myFlag = False
myArray = GetControlGear(myString)
For myRow = 1 To UBound(myArray) 'set control gear validation
myCbox.AddItem myArray(myRow)
If myCbox.Value = myArray(myRow) Then
myFlag = True 'a valid value exists
End If
Next myRow
If myFlag = False Then myCbox.Value = GetDefaultBalast(myString)
End Sub