Dynamically changing List lengths for Userform

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)
 
D

Dave Peterson

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
 
Top