I have here a listbox that is working fine with the code below.
ListBox1
Updated Whole Code:
Option Explicit
Dim bInit As Boolean ' ** module-scoped variable
Private Sub UserForm_Initialize()
bInit = True ' ** set UserForm_Initialize mode **from Taller
clearAll
Me.cmbName.Value = "Nory"
Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
Dim i As Long
Dim arr: arr = ws.Range("B1").CurrentRegion.Value
Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(arr)
dict(arr(i, 2)) = arr(i, 1)
Next
' ***
If dict.exists(Me.cmbName.Value) Then
Me.cmbTeam.Value = dict(Me.cmbName.Value)
If Not cmbTeam.Value = "" And Not cmbDate.Value = "" And Not cmbName.Value = "" Then
Team
forListBoxUpdate 'calling forListBoxUpdate
forDateCombobox 'filling in date dropdowns
forNamesCombobox 'filling in names combobox as long as 3 comboboxes are not blank
Else
Team 'calling Team dropdowns in case team is blank or default cmbName value has no team
cmbDate.Value = ""
cmbName.Value = ""
End If
Else
Team 'calling Team dropdowns in case team is blank or default cmbName value has no team
cmbDate.Value = ""
cmbName.Value = ""
End If
bInit = False ' ** reset **from Taller
End Sub
Private Sub cmbDate_Change()
If Not cmbTeam = "" And Not cmbName = "" Then
forListBoxUpdate 'calling to show info if team and name not blank
forNamesCombobox 'filling in cmbname dropdowns
Else
cmbDate.Clear 'if cmbteam and cmbname blank, cmdate should also be blank
End If
End Sub
Private Sub cmbName_Change()
forListBoxUpdate
End Sub
Private Sub cmbTeam_Change()
cmbDate.Value = ""
'cmbName.Value = "" 'issue, during initialize, default cmbname is removed.
clearAll 'used this instead
forDateCombobox 'fills in date dropdowns
End Sub
Sub forListBoxUpdate() 'to show info from sheets and to be called after the 3 comboboxes are filled
Dim ws As Worksheet, colList As Collection
Dim arrData, arrList, i As Long, j As Long
Set colList = New Collection
Set ws = Worksheets("Sheet2")
arrData = ws.Range("A1:C" & ws.Cells(ws.Rows.count, "A").End(xlUp).Row)
For i = 2 To UBound(arrData)
If Format(arrData(i, 1), "mmmm yyyy") = Me.cmbDate.Value And arrData(i, 3) = Me.cmbName.Value Then
colList.Add i, CStr(i)
End If
Next
ReDim arrList(1 To colList.count + 1, 1 To UBound(arrData))
For j = 1 To 3
arrList(1, j) = arrData(1, j) ' header
For i = 1 To colList.count
arrList(i + 1, j) = arrData(colList(i), j)
Next
Next
With Me.ListBox1
.Clear
.ColumnCount = UBound(arrData, 2)
.list = arrList
End With
labelCount.Caption = ListBox1.ListCount - 1
End Sub
Sub clearAll() 'to clear comboboxes except teams
If Not bInit Then cmbName.Value = "" ' ** doesn't run when calling from UserForm_Initialize **from Taller
cmbDate.Clear
cmbName.Clear
'cmbName.Value = ""
ListBox1.Clear
End Sub
Sub Team() 'for adding the teams dropdown in cmbTeam
clearAll
Dim ws As Worksheet, _
Dic As Object, _
rCell As Range, _
Key
Set ws = Worksheets("Sheet1")
Set Dic = CreateObject("Scripting.Dictionary")
For Each rCell In ws.Range("A2", ws.Cells(Rows.count, "A").End(xlUp))
If Not Dic.exists(rCell.Value) And Not rCell = "" Then
Dic.Add rCell.Value, Nothing
End If
Next rCell
For Each Key In Dic
cmbTeam.AddItem Key
Next
End Sub
Sub forNamesCombobox() 'for adding the names dropdown in cmbName
Dim ws As Worksheet, _
Dic As Object, _
rCell As Range, _
Key
Set ws = Worksheets("Sheet1")
Set Dic = CreateObject("Scripting.Dictionary")
For Each rCell In ws.Range("B2", ws.Cells(Rows.count, "B").End(xlUp))
If Not Dic.exists(rCell.Value) And rCell.Offset(0, -1) = cmbTeam.Value Then
Dic.Add rCell.Value, Nothing
End If
Next rCell
For Each Key In Dic
cmbName.AddItem Key
Next
End Sub
Sub forDateCombobox() 'for adding the date dropdown in cmbDate
Dim date1 As Variant
Dim date2 As Variant
date1 = Format(Now, "mmmm yyyy")
date2 = Format(DateAdd("m", -1, CDate(date1)), "mmmm yyyy")
With cmbDate
.Clear
.AddItem Format(date2, "mmmm yyyy")
.AddItem Format(date1, "mmmm yyyy")
.Value = Format(date1, "mmmm yyyy")
End With
End Sub
Sheet1
A | B |
---|---|
Team | Names |
Lory | Lina |
George | Nory |
George | Max |
Jack | Dan |
Sheet2
A | B | C |
---|---|---|
Date | ID | Names |
3/25/2025 | 1101 | Lina |
4/25/2025 | 1102 | Lina |
3/25/2025 | 1103 | Nory |
4/25/2025 | 1104 | Nory |
3/25/2025 | 1105 | Dan |
4/25/2025 | 1106 | Dan |
Now during change event for teams combobox, I would like to have names combobox get cleared ("Nory" or any value for cmbName
should be removed and gone).
From the above code, teams change event snippet is:
Private Sub cmbTeam_Change()
cmbDate.Value = ""
'cmbName.Value = "" 'issue, during initialize, default cmbname is removed.
clearAll 'used this instead
forDateCombobox 'fills in date dropdowns
End Sub
Sub clearAll() 'to clear comboboxes except teams
If Not bInit Then cmbName.Value = "" ' ** doesn't run when calling from UserForm_Initialize **from Taller
cmbDate.Clear
cmbName.Clear
'cmbName.Value = ""
ListBox1.Clear
End Sub
Even if I will insert cmbName.Value = ""
in sub clearAll
, during initialize, "Nory"
is removed which I don't want it removed at initialize.
How to fix code above where during initialize, "Nory" will stay as well as Team and Date and when there's a change of Team, Date and Name comboboxes will be blank.
Your help is greatly appreciated.
Introducing a module-scoped variable prevents cmbName from being cleared when the Initialize event code runs.
If the script doesn't behave as expected, please update your original post to clarify the logic for clearing the combobox value and/or its dropdown list.
Option Explicit
Dim bInit As Boolean ' ** module-scoped variable
Private Sub UserForm_Initialize()
bInit = True ' ** set UserForm_Initialize mode
clearAll
Me.cmbName.Value = "Nory"
Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
Dim i As Long
Dim arr: arr = ws.Range("B1").CurrentRegion.Value
Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(arr)
dict(arr(i, 2)) = arr(i, 1)
Next
' ***
If dict.exists(Me.cmbName.Value) Then
Me.cmbTeam.Value = dict(Me.cmbName.Value)
If Not cmbTeam.Value = "" And Not cmbName.Value = "" Then
Team
forListBoxUpdate
forDateCombobox
forNamesCombobox
Else
Team
End If
Else
Team
End If
bInit = False ' ** reset
End Sub
Sub clearAll()
cmbDate.Clear
cmbName.Clear
If Not bInit Then cmbName.Value = "" ' ** doesn't clear cmbName when called by UserForm_Initialize
ListBox1.Clear
End Sub