Validation List advise....

[Dermot]

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

[Toppers]

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

"Dermot" wrote:

> 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

[Dermot]

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

"Toppers" wrote:

> 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
>
> "Dermot" wrote:
>
> > 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

[Bob Phillips]

"Dermot" <[email protected]> wrote in message
news:[email protected]...
> 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

[Dermot]

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" wrote:

>
> "Dermot" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[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)

"Dermot" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> >
> > "Dermot" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

[Dermot]

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.

"Bob Phillips" wrote:

> 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)
>
> "Dermot" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> > >
> > > "Dermot" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>

[Dermot]

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

"Dermot" wrote:

> 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.
>
> "Bob Phillips" wrote:
>
> > 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)
> >
> > "Dermot" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" wrote:
> > >
> > > >
> > > > "Dermot" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >