View Full Version : populate combo box with conditions
av8tordude
04-14-2020, 12:07 AM
This code populate the combo box with my list in column A. In column F has a status that is either "open" or "Closed" I will like to list only the names that are open. can someone offer assistance in revising my code. Thank you
Dim Rng As Range, LRow As Long
LRow = Range("A" & Rows.Count).End(xlUp).Row
cbName.Clear
For Each Rng In Range("A7:A" & LRow)
If Rng.Value <> "" Then cbName.AddItem Rng.Value
Next
Avoid .additem to populate a combobox/listbox.
Use .List instead. See https://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html
Sub M_snb()
sn = Application.Transpose(Columns(1).SpecialCells(2))
sp = Columns(6).SpecialCells(2)
For j = 1 To UBound(sn)
If j < 7 Or sp(j, 1) = "closed" Then sn(j) = "|"
Next
cb.name.list = Filter(sn, "|", 0)
End Sub
paulked
04-14-2020, 06:56 AM
Or
Dim LRow As Long
cbName.Clear
For LRow = 7 To Range("A" & Rows.Count).End(xlUp).Row
If Cells(LRow, 1) <> "" And Cells(LRow, 6) = "open" Then cbName.AddItem Cells(LRow, 1)
Next
If you want to ignore the case of 'open', eg 'OPEN' or 'Open' then put
Option Compare Text
at the top of the module
@snb I had't read the text with your post until after writing mine. Very interesting.
paulked
04-14-2020, 07:14 AM
snb, is this an acceptable way?
Private Sub UserForm_Activate()
Dim LRow As Long, str As String
cbName.Clear
For LRow = 7 To Range("A" & Rows.Count).End(xlUp).Row
If Cells(LRow, 1) <> "" And Cells(LRow, 6) = "open" Then str = str & Cells(LRow, 1) & ","
Next
cbName.List = Split(str, ",")
End Sub
Obviously there will be a blank row at the end, but that's easily removed if it's a problem.
Yes, but.....
- Using arrays prevents interaction with the workbook and is therefore far faster than checking cell by cell like you do.
- cbname.clear is redundant when you use the .List property.
- and Range("A" & Rows.Count).End(xlUp).Row has to be calculated too many times
- the list will contain an empty value at the end
So:
Private Sub UserForm_Activate()
sn=cells(1).currentregion.resize(,6)
For j = 7 To ubound(sn)
if sn(j,1)<>"" and sn(j,6) = "open" Then c00 = c00 & chr(0) & sn(j,1)
Next
cbName.List = Split(mid(c00,2), chr(0))
End Sub
paulked
04-14-2020, 11:47 AM
Understood.
Thanks for the lesson :thumb
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.