angietate1
New Member
- Joined
- Feb 14, 2016
- Messages
- 1
My comboboxes keep clearing. I'm a new user to comboboxes and VBA. The three drop downs are cascading and they clear to soon or they won't stay selected. Help! Is there a way to attach the document?
Private Sub Combobox21_change()
Dim wsData As Worksheet
Dim listOfValues As String 'To store list of values already added
Dim ValueToAdd As String 'To store new value to add
listOfValues = ""
Set wsData = ThisWorkbook.Sheets("DATA")
MyVal = Me.ComboBox21.Value
'loop thru col a
lr = ThisWorkbook.Sheets("data").Cells(Rows.Count, 1).End(xlUp).Row
'clear combobox 22
Me.ComboBox22.Clear
'loop thru
For x = 2 To lr
If MyVal = wsData.Cells(x, 1) Then
'add to combobox
ValueToAdd = wsData.Cells(x, 2) 'Get value from worksheet
If InStr(listOfValues, ValueToAdd) = 0 Then
'Check to see if the value has already been added
'If not, add to values added and add the item to the combobox.
listOfValues = listOfValues & ValueToAdd
Me.ComboBox22.AddItem ValueToAdd
End If
End If
Next x
End Sub
Private Sub ComboBox22_Change()
Dim wsData As Worksheet
Dim listOfValues As String 'To store list of values already added
Dim ValueToAdd As String 'To store new value to add
listOfValues = ""
Set wsData = ThisWorkbook.Sheets("DATA")
MyVal = Me.ComboBox22.Value
'loop thru col b
lr = ThisWorkbook.Sheets("data").Cells(Rows.Count, 1).End(xlUp).Row
'clear combobox 23
Me.ComboBox23.Clear
'loop thru
For x = 2 To lr
If MyVal = wsData.Cells(x, 2) Then
'add to combobox
ValueToAdd = wsData.Cells(x, 3) 'Get value from worksheet
If InStr(listOfValues, ValueToAdd) = 0 Then
'Check to see if the value has already been added
'If not, add to values added and add the item to the combobox.
listOfValues = listOfValues & ValueToAdd
Me.ComboBox23.AddItem ValueToAdd
End If
End If
Next x
End Sub
Private Sub Combobox21_change()
Dim wsData As Worksheet
Dim listOfValues As String 'To store list of values already added
Dim ValueToAdd As String 'To store new value to add
listOfValues = ""
Set wsData = ThisWorkbook.Sheets("DATA")
MyVal = Me.ComboBox21.Value
'loop thru col a
lr = ThisWorkbook.Sheets("data").Cells(Rows.Count, 1).End(xlUp).Row
'clear combobox 22
Me.ComboBox22.Clear
'loop thru
For x = 2 To lr
If MyVal = wsData.Cells(x, 1) Then
'add to combobox
ValueToAdd = wsData.Cells(x, 2) 'Get value from worksheet
If InStr(listOfValues, ValueToAdd) = 0 Then
'Check to see if the value has already been added
'If not, add to values added and add the item to the combobox.
listOfValues = listOfValues & ValueToAdd
Me.ComboBox22.AddItem ValueToAdd
End If
End If
Next x
End Sub
Private Sub ComboBox22_Change()
Dim wsData As Worksheet
Dim listOfValues As String 'To store list of values already added
Dim ValueToAdd As String 'To store new value to add
listOfValues = ""
Set wsData = ThisWorkbook.Sheets("DATA")
MyVal = Me.ComboBox22.Value
'loop thru col b
lr = ThisWorkbook.Sheets("data").Cells(Rows.Count, 1).End(xlUp).Row
'clear combobox 23
Me.ComboBox23.Clear
'loop thru
For x = 2 To lr
If MyVal = wsData.Cells(x, 2) Then
'add to combobox
ValueToAdd = wsData.Cells(x, 3) 'Get value from worksheet
If InStr(listOfValues, ValueToAdd) = 0 Then
'Check to see if the value has already been added
'If not, add to values added and add the item to the combobox.
listOfValues = listOfValues & ValueToAdd
Me.ComboBox23.AddItem ValueToAdd
End If
End If
Next x
End Sub