Clearing List Boxes

K

Kevin Willhoit

I am working on a project in which one list box will populate the nex
through vlookups. The only problem is that if you change one of th
boxes previous to another it does not clear them therefore they contai
false values. Any ideas on how to make the boxes clear when selectin
any of the previous boxes
 
D

Dave Peterson

I wasn't sure how you were using the listboxes and how you were using the
=vlookup()'s.

But I put 3 listboxes on a userform (listbox1, listbox2, and listbox3).

Listbox1 controls listbox2 which controls listbox3.

This is the code I had behind the userform:

Option Explicit
Dim blkProc As Boolean
Private Sub ListBox1_Change()
Dim iCtr As Long
Dim myChar As String
blkProc = True
Me.ListBox2.Clear
Me.ListBox3.Clear
blkProc = False
If Me.ListBox1.Value = "a1" Then
myChar = "x"
Else
myChar = "y"
End If
For iCtr = 1 To 10
Me.ListBox2.AddItem myChar & iCtr
Next iCtr
End Sub

Private Sub ListBox2_Change()
Dim iCtr As Long
Dim myChar As String

If blkProc = True Then Exit Sub

blkProc = True
Me.ListBox3.Clear
blkProc = False
If Me.ListBox2.Value = "x1" Then
myChar = "z"
Else
myChar = "w"
End If
For iCtr = 1 To 10
Me.ListBox3.AddItem myChar & iCtr
Next iCtr
End Sub

Private Sub ListBox3_Change()
If blkProc = True Then Exit Sub
MsgBox "hi"
End Sub

Private Sub UserForm_Initialize()
Dim iCtr As Long
For iCtr = 1 To 10
Me.ListBox1.AddItem "a" & iCtr
Next iCtr
End Sub

So if you go an change a previous listbox, then the "trailing" listboxes are
reset or reinitialized.

Maybe it'll help?
 
Top