Blank Spaces on ComboBox
 

Hi There,

I hope you are all well.

I have a table that countains 3 columns as following:

- from B3:B22 we have a reference number
- from C3:C22 we have Employee name
- from D3:D22 we have availability (two options: "Yes" or "No").

The Reference column is empty with an "IF" formula and the criteria is:
e.g. IF cell "D3" is "Yes", then the cell "B3" shows a reference such as "001" and so on.

I have created a UserForm to send an email to employees that have "Yes" on column "D".

For that, I have created a ComboBox to choose which employee to send the email.

The problem is, I can see the ones that shows the reference, but I can also see blank cells on the ComboBox.

Is there a way to hide/delete the empty spaces from the ComboBox so I can only see and choose the ones that are visible?

As I am not able to attach the current file, I have attached a print screen so you can see it.

Your assistance on this will be highly appreciated.

Best regards,
Filipe Oliveira

Blank Spaces on ComboBox
 

If you read the employee list into an array then you can use AddItem to
populate the combobox via a For...Next loop, *if* the array element
isn't empty.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Folipe,

Add this code to your form initialize event:

Private Sub UserForm_Initialize()
'


'Assumes you have named the reference as "Reference"
For Each cell In Range("Reference")
If cell.Value < "" Then
ComboBox1.AddItem cell.Value
End If
Next cell

End Sub

Regards,
Gizzmo