Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation List advise....
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation List advise....
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation List advise....
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation List advise....
"Dermot" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation List advise....
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation List advise....
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" wrote in message ... 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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation List advise....
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" wrote in message ... 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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation List advise....
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation List Advise Please | New Users to Excel | |||
Validation (Drop down list vs simple text length validation) | Excel Programming | |||
Validation (Drop down list vs simple text length validation) | Excel Programming | |||
Validation (Drop down list vs simple text length validation) | Excel Programming | |||
AutoComplete? Drop Down List? Please Advise | Excel Programming |