combobox values with validation from adjacent cell

[kampog]

Need help. very new with excel vba. I am populating a combobox from a range but need to check if the adjacent cell is empty or not. The table is being updated dynamically so I need to exclude some values in the list once the table is updated. my code is something like below, so i need to check if C2:C100 has value or empty, if empty put B2 in the combobox list and loop.. thanks in advance guys.


Private Sub List_Prizes()
Dim myrng As Range
Dim data As Range


Set ws = ThisWorkbook.Worksheets("Prizes")
Set myrng = ws.Range("B2:B100")

With Me.cb_PrizeList
.Clear
For Each data In myrng.Cells

If data.Value <> "" Then
.AddItem data.Value
End If
Next data
End With
End Sub

 

[abdelfattah]

if I understand correctly you want to populate items in combobox1(cb_PrizeList)based on column B if the adjacant cell in columns C is empty, right?
if it's so then change event on user form module (just delete your code and copy mine)

Private Sub UserForm_Initialize()
Dim myrng As Range
Dim data As Range
Dim lr As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Prizes")
lr = ws.Range("B" & Rows.Count).End(xlUp).Row
Set myrng = ws.Range("B2:B" & lr)
With cb_PrizeList
.Clear
For Each data In myrng.Cells
If data.Offset(, 1) = "" Then
.AddItem data.Value
End If
Next data
End With
End Sub

 

[kampog]

if I understand correctly you want to populate items in combobox1(cb_PrizeList)based on column B if the adjacant cell in columns C is empty, right?
if it's so then change event on user form module (just delete your code and copy mine)

Private Sub UserForm_Initialize()
Dim myrng As Range
Dim data As Range
Dim lr As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Prizes")
lr = ws.Range("B" & Rows.Count).End(xlUp).Row
Set myrng = ws.Range("B2:B" & lr)
With cb_PrizeList
.Clear
For Each data In myrng.Cells
If data.Offset(, 1) = "" Then
.AddItem data.Value
End If
Next data
End With
End Sub

Hello Abdelfattah, thank you very much. That did the trick.

But the issue now is the listindex property of the combobox value is gone. I am using it to reference the row where to update the table, once the save button is click. Duplicate values may occur in the table.

 

[abdelfattah]

Now I use phone I'm away from my home,sorry
I don't understand what 's your problem?
I would from you explain clearly and if by picture will be better .
Maybe I see later if I have time.

 

[kampog]

all good now, thanks again for the big help. cheers!