Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
|
|||
|
|||
Quote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing Blank spaces? | Excel Programming | |||
ranking with some blank spaces | Excel Worksheet Functions | |||
Blank Spaces on Sort | Excel Discussion (Misc queries) | |||
Blank spaces | Excel Discussion (Misc queries) | |||
Blank spaces in a listbox | Excel Discussion (Misc queries) |