Validation List advise....

[Guest]

If I create a validation list, with an option Yes, the text "Yes" appears in
the relevant cell.How do I create an option in the validation list to return
it to a blank cell with no text.
1. Can you program a full validation list instead of using Data | Validation
List?
2. Is there a way to increase the width of the Validation List dropdown to
accomodate longer words?

Thanks

 

[Guest]

Dermot,
To get a blank, simply put a blank cell in your validation
list i.e. if validation list is cell c1:c10, make c1 =blank or c10 =blank (or
any other!)

Yes, you can program a validation process but details are required if you
want sample code.

Width of validation list = column width so increase column width

HTH

 

[Guest]

Thanks for the reply Toppers.
I am using the following code to conditional format a range of cells in each
individual row, determined upon the validation option selected from the
validation list in column K.
So if I select Yes in cell K6 the range B6:M6 change to Yellow with the text
"Yes" in the cell.
I would like the option "None" in the list to reverse this formatting, with
no text.
I can get the colour to revert to blank, but the text remains in the cell. I
tried an apostrophe in the list but, I don't think this looks good as an
option in the list.
Any further advise would be appreciated.
The code is below
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_Handler

If Not Intersect(Target, Range("B6:M10000")) Is Nothing Then
Application.EnableEvents = False
i = Target.Row
Select Case Target.Value
Case "Yes"
Range("B" & i & ":M" & i).Interior.ColorIndex = 6
Case "No"
Range("B" & i & ":M" & i).Interior.ColorIndex = 3
Case "PQRSTUVWX"
Range("B" & i & ":M" & i).Interior.ColorIndex = 28
Case "Undo"
Range("B" & i & ":M" & i).Interior.ColorIndex = Null

Case "Y"
Range("B" & i & ":M" & i).Interior.ColorIndex = 26
Case "Z"
Range("B" & i & ":M" & i).Interior.ColorIndex = 30
Case Else
Range("B" & i & ":M" & i).Interior.ColorIndex = xlNone
End Select
End If

Err_Handler:
Application.EnableEvents = True
End Sub


Thanks

 

[Bob Phillips]

If I create a validation list, with an option Yes, the text "Yes" appears in
the relevant cell.How do I create an option in the validation list to return
it to a blank cell with no text.

As long as you do not uncheck 'Ignore Blanks', then you can clear any value
down and it will allow blank

1. Can you program a full validation list instead of using Data | Validation
List?

Yes, you create a list of values and use say =M1:M10 in the List textbox

2. Is there a way to increase the width of the Validation List dropdown to
accomodate longer words?

You can increase the column width, or you can 'frig it' as Debra shows at
http://www.contextures.com/xlDataVal08.html#Wider

 

[Guest]

Thanks Bob
For the link and explanation regarding creating a validatation list from
list in a range of cells and contextures link.

I have the "Ignore Blanks" check box selected, but when I click on my undo
option to remove the formating the Text "Undo" remains in the cell.
Do I have to manually delete this text ?
Is there a way to achieve this deletion automatically when the colour
formatting
is remove when "Undo" option clicked in the validation list?

 

[Bob Phillips]

Try changing your Case Else code to


Case Else
Range("B" & i & ":M" & i).Interior.ColorIndex = xlNone
Target.Value = ""

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Hi Bob
I tried changing the Else statement, it takes away the colour formatting,
but the text "Blank" (the option I have in the validation list to remove all
formatting within the selected row range) remains.
I was determined to try and do this myself have dried may different things
but I anly manage to revove the colur not the text contents.
Any other suggestions would be appreciated.

 

[Guest]

Hi Bob,
I thought I'd just have another shot of this before calling it a day (more
like month!!)
I decided to record a macro of deleting the text "Blank" in the cell, and
then looking at the code it produced. I then entered the following code
(second line) to the "Blank Case" to give me the result I was seeking.

Case "Blank"
Range("B" & i & ":M" & i).Interior.ColorIndex = Null
ActiveCell.FormulaR1C1 = ""

Thanks
Dermot