Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color cell validation?
Hi: Thanks for taking the time to read my question. I want to configure a cell in Excel for color validation and I don't know how to do it. What I want is to configure a cell with a drop-down list of colors only, so the user can select from a set of predefined colors. I know how to create a data validation list by placing characters in a verticle list of cells, naming the grouping, then use data validation using a list to provide user selection. I also know how to conditionally format the cell based on the value chosen from the list, e.g. if 'R' is chosen from the list, the cell color changes to red, and the 'R' from the named list is placed in the cell. What I want to do is choose from a list of colors and only place the color in the cell. I tried using non-printing ASCII characters in the list but Excel does not accept them. I tried to color the cells that comprise the drop down list, but the colors do not show up in the drop down list, nor when a member of the list is chosen. All I want to do is have the user select from a drop down list of colors only. Can anyone suggest how to do this? Thanks for your help -J -- jjh ------------------------------------------------------------------------ jjh's Profile: http://www.excelforum.com/member.php...o&userid=36089 View this thread: http://www.excelforum.com/showthread...hreadid=559608 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color cell validation?
jjh wrote:
Hi: Thanks for taking the time to read my question. I want to configure a cell in Excel for color validation and I don't know how to do it. What I want is to configure a cell with a drop-down list of colors only, so the user can select from a set of predefined colors. I know how to create a data validation list by placing characters in a verticle list of cells, naming the grouping, then use data validation using a list to provide user selection. I also know how to conditionally format the cell based on the value chosen from the list, e.g. if 'R' is chosen from the list, the cell color changes to red, and the 'R' from the named list is placed in the cell. What I want to do is choose from a list of colors and only place the color in the cell. I tried using non-printing ASCII characters in the list but Excel does not accept them. I tried to color the cells that comprise the drop down list, but the colors do not show up in the drop down list, nor when a member of the list is chosen. All I want to do is have the user select from a drop down list of colors only. Can anyone suggest how to do this? Thanks for your help -J AFAIK there is no way to make such a "color cell validation"... -- Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color cell validation?
Here is a way that adds an option to the right-click menu, with a sleection
of colours to choose from Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.CommandBars("Cell").Controls("Get Colour").Delete On Error GoTo 0 End Sub Private Sub Workbook_Open() On Error Resume Next Application.CommandBars("Cell").Controls("Get Colour").Delete On Error GoTo 0 With Application.CommandBars("Cell") With .Controls.Add(Type:=msoControlPopup, temporary:=True) .BeginGroup = True .Caption = "Get Colour" With .Controls.Add(Type:=msoControlButton, temporary:=True) .BeginGroup = True .Caption = "Red" .Parameter = "Red" .OnAction = "GetColour" End With With .Controls.Add(Type:=msoControlButton, temporary:=True) .Caption = "Blue" .Parameter = "Blue" .OnAction = "GetColour" End With 'etc. End With End With End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code and in a standard code module, add '-----------------------------*------------------------------*-------------- -- Function GetColour() As Long '-----------------------------*------------------------------*-------------- -- With Application.CommandBars.ActionControl Select Case .Parameter Case "Red": ActiveCell.Interior.ColorIndex = 3 Case "Blue": ActiveCell.Interior.ColorIndex = 5 'etc. End Select End With End Function -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "jjh" wrote in message ... Hi: Thanks for taking the time to read my question. I want to configure a cell in Excel for color validation and I don't know how to do it. What I want is to configure a cell with a drop-down list of colors only, so the user can select from a set of predefined colors. I know how to create a data validation list by placing characters in a verticle list of cells, naming the grouping, then use data validation using a list to provide user selection. I also know how to conditionally format the cell based on the value chosen from the list, e.g. if 'R' is chosen from the list, the cell color changes to red, and the 'R' from the named list is placed in the cell. What I want to do is choose from a list of colors and only place the color in the cell. I tried using non-printing ASCII characters in the list but Excel does not accept them. I tried to color the cells that comprise the drop down list, but the colors do not show up in the drop down list, nor when a member of the list is chosen. All I want to do is have the user select from a drop down list of colors only. Can anyone suggest how to do this? Thanks for your help -J -- jjh ------------------------------------------------------------------------ jjh's Profile: http://www.excelforum.com/member.php...o&userid=36089 View this thread: http://www.excelforum.com/showthread...hreadid=559608 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color cell validation?
well i no its not quite what ur looking for, but maby u can use this
http://pmexcelent.dk/PicColor.xls |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color cell validation?
Here's my shot at it. With the drop down in F1.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 6 Then Exit Sub If Target.Row < 1 Then Exit Sub Target.Interior.ColorIndex = xlNone If Range("F1").Value = "red" Then With Target.Interior .ColorIndex = 3 .Pattern = xlSolid End With Target.Font.ColorIndex = 3 ElseIf Range("F1").Value = "blue" Then With Target.Interior .ColorIndex = 41 .Pattern = xlSolid End With Target.Font.ColorIndex = 41 ElseIf Range("F1").Value = "green" Then With Target.Interior .ColorIndex = 4 .Pattern = xlSolid End With Target.Font.ColorIndex = 4 End If End Sub HTH Regards, Howard "jjh" wrote in message ... Hi: Thanks for taking the time to read my question. I want to configure a cell in Excel for color validation and I don't know how to do it. What I want is to configure a cell with a drop-down list of colors only, so the user can select from a set of predefined colors. I know how to create a data validation list by placing characters in a verticle list of cells, naming the grouping, then use data validation using a list to provide user selection. I also know how to conditionally format the cell based on the value chosen from the list, e.g. if 'R' is chosen from the list, the cell color changes to red, and the 'R' from the named list is placed in the cell. What I want to do is choose from a list of colors and only place the color in the cell. I tried using non-printing ASCII characters in the list but Excel does not accept them. I tried to color the cells that comprise the drop down list, but the colors do not show up in the drop down list, nor when a member of the list is chosen. All I want to do is have the user select from a drop down list of colors only. Can anyone suggest how to do this? Thanks for your help -J -- jjh ------------------------------------------------------------------------ jjh's Profile: http://www.excelforum.com/member.php...o&userid=36089 View this thread: http://www.excelforum.com/showthread...hreadid=559608 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data validation with hyperlinks | Excel Worksheet Functions | |||
validation list--list depends on the selection of first list | New Users to Excel | |||
data validation lists | Excel Discussion (Misc queries) | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Data Validation Window? | Excel Discussion (Misc queries) |