PDA

View Full Version : Blank or null string in drop-down



shankar
10-30-2009, 08:44 AM
I am having trouble making a blank or null string value as one of the choices of a drop-down in a cell.

How do I incorporate that? Thanks.

Shankar

lucas
10-30-2009, 08:46 AM
What kind of drop down? An active X combo box, a combo box from the forms toolbar or a validation drop down?

shankar
10-30-2009, 08:53 AM
A validation dropdown.

lucas
10-30-2009, 09:28 AM
If there is a blank in your list it should show.....

How are you getting the list for the drop down?

shankar
10-30-2009, 09:35 AM
Thanks, but I am building the list in VBA, not from a range.
Something like

for i= 1 to NObjects
ValidationString= ValidationString & "," & ObjectStr[i]
next


I need the very first element to be a blank. However, if I try setting ValidationString initially to " " or VbNullString or """ """, it doesn't work.

lucas
10-30-2009, 09:55 AM
try:
.ClearContents

shankar
10-30-2009, 10:06 AM
Sorry, but didn't quite get it. To my knowledge clearcontents clears the value in a cell, but I don't understand how is it going to give a blank entry in a drop-down.

lucas
10-30-2009, 10:56 AM
I don't see a way to do this. Maybe you could post an example workbook so we can see how you are building the validation.

shankar
10-30-2009, 08:28 PM
Please load attachment, it creates a dropdown in the A1 cell of the first sheet. Thanks.

rbrhodes
10-31-2009, 01:25 AM
Hi Shankar,

I couldn't manage to get a blank into a data validation list so that mean's it's not possible <g>. However I found some code on the web to display a combobox based on a cell having data validation, Better yet have the combobox put on the sheet and displayed on Selection change. You can enter blank lines in a Combobox ( and a whole lot of other things too!)

shankar
10-31-2009, 08:05 AM
I guess the only way so far is to copy the values to a range and use the range as in the first attachment by lucas.

rbrhodes
10-31-2009, 07:04 PM
Yes. or use a ComboBox which allows greater flexiblility in formmating, etc

mikerickson
10-31-2009, 07:47 PM
In the attached workbook, the Open event will set up a validation list in Sheet1!A1, the first item in that list is Chr(6).
A change event on sheet1's code sheet will remove Chr(6) from any string entered in A1.

This seems like a 3-sides-around-the-barn alternative to pressing the Delete Key.

shankar
10-31-2009, 09:16 PM
I think I found the answer- use VbCr for the blanks.
For example,

Formula1="Apple," & VbCr & ",Orange"

This introduces a blank between Apple and Orange

mikerickson
10-31-2009, 09:53 PM
Selecting that empty space puts a carriage return into the cell. On Mac's, that will generates a line feed and makes the cell taller.

Is the purpose to put spaces in between items of the list, to make the list look nice?
Or is the purpose to give the user the option of selecting "" from the list?

shankar
10-31-2009, 11:29 PM
I need to have a blank or no value as the first option in the drop-down. This to signify that none is selected. On my machine (running Office 2007 on Vista), it doesn't make the cell taller.
VbLf works for me as well.

mdmackillop
11-02-2009, 06:48 AM
DropdownList = ",' ,Red, Blue, Green, Yellow, Others"

mikerickson
11-02-2009, 06:48 PM
:clap: :thumb: