Dynamically changing List lengths for Userform

  • Thread starter Thread starter universal
  • Start date Start date
U

universal

Hello All,

Ive got a User form that I would like to restrict response to a list
from the worksheet.

I would also like to change / remove some of the options available
depending on the response to the previous ComboBox.

Any ideas?

Thanks

Eddie(Universal)
 
I'm not quite sure what you want to check to restrict values, but you might find
an idea in here:

Option Explicit
Dim stopProc As Boolean
Private Sub ComboBox1_Change()

Dim iCtr As Long
Dim myStr As String

If Me.ComboBox1.Value <> "" Then
stopProc = True
Me.ComboBox2.Clear
stopProc = False
End If

myStr = LCase(Left(Me.ComboBox1.Value, 1))

For iCtr = 1 To 10
With ThisWorkbook.Worksheets("sheet1").Cells(iCtr, "B")
If Left(.Value, 1) = myStr Then
Me.ComboBox2.AddItem .Value
End If
End With
Next iCtr

End Sub

Private Sub ComboBox2_Change()
If stopProc Then Exit Sub
MsgBox Me.ComboBox2.Value
End Sub

Private Sub UserForm_Initialize()
Me.ComboBox1.List = ThisWorkbook.Worksheets("Sheet1").Range("a1:a10").Value
Me.ComboBox2.AddItem "Choose from combo1 first"
End Sub
 
Back
Top