Blank Spaces on ComboBox

[Filipe Oliveira]

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 D322 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 a
"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 th
email.

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

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

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

Your assistance on this will be highly appreciated.

Best regards,
Filipe Oliveir

+-------------------------------------------------------------------
|Filename: Project1.jpg
|Download: http://www.excelbanter.com/attachment.php?attachmentid=791
+-------------------------------------------------------------------

 

[GS]

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

 

[Gizzmo]

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 D322 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 a
"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 th
email.

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

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

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

Your assistance on this will be highly appreciated.

Best regards,
Filipe Oliveira

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,
Gizzm

+-------------------------------------------------------------------
|Filename: ExcelBanterFilipeOliveira.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=793
+-------------------------------------------------------------------