Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
3 Conditions for Data Validation for a List
Hi All, I'm using xl2003 and have 5 source values for a data validation list. Each source data cell has text, a different fill color (red, yellow, green etc) and some change between black and white text. I use the list like any other, in hundreds of other cells. I need the conditional formatting to expand past 3 conditions. I've seen some posts on here for doing this, just not in via a list. My values are alphabetical for this part and to maintain consistency with how the other users who populate the sheet with data enter it, I want to use the list function. Thanks! (from the newb) -- I''ve encountered an error and I need to close for the weekend :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
3 Conditions for Data Validation for a List
I don't think I understand this completely. I've used conditional formatting, so I understand why needing to go beyond three conditions is a problem; that's why you want to do it in VBA instead, I expect. But I'm not sure what you mean when you want to do it "via a list", and that you want to use "the list function". Maybe you're saying you want your program to go down a list of cells and for each one a) validate the value (ie it must be one of five values) and b) set the cell's text and background colors; sure, I get that. Is that all you mean, or is there more to it? --- "Anders" wrote: I'm using xl2003 and have 5 source values for a data validation list. Each source data cell has text, a different fill color (red, yellow, green etc) and some change between black and white text. I use the list like any other, in hundreds of other cells. I need the conditional formatting to expand past 3 conditions. I've seen some posts on here for doing this, just not in via a list. My values are alphabetical for this part and to maintain consistency with how the other users who populate the sheet with data enter it, I want to use the list function. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
3 Conditions for Data Validation for a List
Bob - you're right with a+b. I want the user to click the destination cell, see a dropdown list containing 5 (alphanumeric) values, and select one (no other values can be entered into the cell). Once selected, the font/fill will change based on the item selected. -- I''''ve encountered an error and I need to close for the weekend :) "Bob Bridges" wrote: I don't think I understand this completely. I've used conditional formatting, so I understand why needing to go beyond three conditions is a problem; that's why you want to do it in VBA instead, I expect. But I'm not sure what you mean when you want to do it "via a list", and that you want to use "the list function". Maybe you're saying you want your program to go down a list of cells and for each one a) validate the value (ie it must be one of five values) and b) set the cell's text and background colors; sure, I get that. Is that all you mean, or is there more to it? --- "Anders" wrote: I'm using xl2003 and have 5 source values for a data validation list. Each source data cell has text, a different fill color (red, yellow, green etc) and some change between black and white text. I use the list like any other, in hundreds of other cells. I need the conditional formatting to expand past 3 conditions. I've seen some posts on here for doing this, just not in via a list. My values are alphabetical for this part and to maintain consistency with how the other users who populate the sheet with data enter it, I want to use the list function. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
3 Conditions for Data Validation for a List
I've never fooled with drop-down lists in Excel - the occasional command button, is all - but presumably you know how to do that part. You're just asking asking how to .... Come to think of it, you haven't asked anything yet. What have you tried, and what isn't working? And HOW isn't it working? --- "Anders" wrote: ...you're right with a+b. I want the user to click the destination cell, see a dropdown list containing 5 (alphanumeric) values, and select one (no other values can be entered into the cell). Once selected, the font/fill will change based on the item selected. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
3 Conditions for Data Validation for a List
drop-down - If you have to define the values a user can input into a cell, one where they can see what the values they can choose from are, how would you do it? I've found the easiest for me is a data-validation-allow list-define range etc. I'm open to other ways if they're better. **Note - I have people not too comfortable with excel using the sheets to deliver status updates to me so ease of use is key. what have I tried. I've gotten something to work since my last post - 1. This code is what I've modified, (found in the MS help docs): it successfully changes the fill color Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 8 Then Select Case .Value Case "Not Started": .Interior.ColorIndex = 2 'White Case "Completed": .Interior.ColorIndex = 5 'Blue Case "Manageable Issues": .Interior.ColorIndex = 6 'Yellow Case "Significant Issues": .Interior.ColorIndex = 3 'Red Case "On Track": .Interior.ColorIndex = 10 ' Green End Select End If End With ws_exit: Application.EnableEvents = True End Sub 3. I need to change the font color as well for readability. So can I add that to this code or do i need to write a different one? I tried writing a different one (below, doesn't work) Private Sub FontColorChange(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 8 Then Select Case .Value Case "Not Started": .Font.ColorIndex = 1 ' Black Case "Completed": .Font.ColorIndex = 2 'White Case "Manageable Issues": .Font.ColorIndex = 1 'Black Case "Significant Issues": .Font.ColorIndex = 2 'White Case "On Track": .Font.ColorIndex = 2 'White End Select End If End With ws_exit: Application.EnableEvents = True End Sub **What i'm getting with this is the font just is never changing color. If the code is right, maybe I'm setting it up wrong in the editor - such as where I'm placing it??? Thanks. -- I''''ve encountered an error and I need to close for the weekend :) "Bob Bridges" wrote: I've never fooled with drop-down lists in Excel - the occasional command button, is all - but presumably you know how to do that part. You're just asking asking how to .... Come to think of it, you haven't asked anything yet. What have you tried, and what isn't working? And HOW isn't it working? --- "Anders" wrote: ...you're right with a+b. I want the user to click the destination cell, see a dropdown list containing 5 (alphanumeric) values, and select one (no other values can be entered into the cell). Once selected, the font/fill will change based on the item selected. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
3 Conditions for Data Validation for a List
Just move the code changing font color part in the FontColorChange into your first Worksheet_Change Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 8 Then Select Case .Value Case "Not Started": .Interior.ColorIndex = 2 'White .Font.ColorIndex = 1 Case "Completed": .Interior.ColorIndex = 5 'Blue .Font.ColorIndex = 2 Case "Manageable Issues": .Interior.ColorIndex = 6 'Yellow .Font.ColorIndex = 1 Case "Significant Issues": .Interior.ColorIndex = 3 'Red .Font.ColorIndex = 2 Case "On Track": .Interior.ColorIndex = 10 ' Green .Font.ColorIndex = 2 End Select End If End With ws_exit: Application.EnableEvents = True End Sub Keiji Anders wrote: drop-down - If you have to define the values a user can input into a cell, one where they can see what the values they can choose from are, how would you do it? I've found the easiest for me is a data-validation-allow list-define range etc. I'm open to other ways if they're better. **Note - I have people not too comfortable with excel using the sheets to deliver status updates to me so ease of use is key. what have I tried. I've gotten something to work since my last post - 1. This code is what I've modified, (found in the MS help docs): it successfully changes the fill color Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 8 Then Select Case .Value Case "Not Started": .Interior.ColorIndex = 2 'White Case "Completed": .Interior.ColorIndex = 5 'Blue Case "Manageable Issues": .Interior.ColorIndex = 6 'Yellow Case "Significant Issues": .Interior.ColorIndex = 3 'Red Case "On Track": .Interior.ColorIndex = 10 ' Green End Select End If End With ws_exit: Application.EnableEvents = True End Sub 3. I need to change the font color as well for readability. So can I add that to this code or do i need to write a different one? I tried writing a different one (below, doesn't work) Private Sub FontColorChange(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 8 Then Select Case .Value Case "Not Started": .Font.ColorIndex = 1 ' Black Case "Completed": .Font.ColorIndex = 2 'White Case "Manageable Issues": .Font.ColorIndex = 1 'Black Case "Significant Issues": .Font.ColorIndex = 2 'White Case "On Track": .Font.ColorIndex = 2 'White End Select End If End With ws_exit: Application.EnableEvents = True End Sub **What i'm getting with this is the font just is never changing color. If the code is right, maybe I'm setting it up wrong in the editor - such as where I'm placing it??? Thanks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
3 Conditions for Data Validation for a List
Thanks Keiji! Works brilliantly! -- I''''ve encountered an error and I need to close for the weekend :) "keiji kounoike" <"kounoike A | T Pikara" wrote: Just move the code changing font color part in the FontColorChange into your first Worksheet_Change Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 8 Then Select Case .Value Case "Not Started": .Interior.ColorIndex = 2 'White .Font.ColorIndex = 1 Case "Completed": .Interior.ColorIndex = 5 'Blue .Font.ColorIndex = 2 Case "Manageable Issues": .Interior.ColorIndex = 6 'Yellow .Font.ColorIndex = 1 Case "Significant Issues": .Interior.ColorIndex = 3 'Red .Font.ColorIndex = 2 Case "On Track": .Interior.ColorIndex = 10 ' Green .Font.ColorIndex = 2 End Select End If End With ws_exit: Application.EnableEvents = True End Sub Keiji Anders wrote: drop-down - If you have to define the values a user can input into a cell, one where they can see what the values they can choose from are, how would you do it? I've found the easiest for me is a data-validation-allow list-define range etc. I'm open to other ways if they're better. **Note - I have people not too comfortable with excel using the sheets to deliver status updates to me so ease of use is key. what have I tried. I've gotten something to work since my last post - 1. This code is what I've modified, (found in the MS help docs): it successfully changes the fill color Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 8 Then Select Case .Value Case "Not Started": .Interior.ColorIndex = 2 'White Case "Completed": .Interior.ColorIndex = 5 'Blue Case "Manageable Issues": .Interior.ColorIndex = 6 'Yellow Case "Significant Issues": .Interior.ColorIndex = 3 'Red Case "On Track": .Interior.ColorIndex = 10 ' Green End Select End If End With ws_exit: Application.EnableEvents = True End Sub 3. I need to change the font color as well for readability. So can I add that to this code or do i need to write a different one? I tried writing a different one (below, doesn't work) Private Sub FontColorChange(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 8 Then Select Case .Value Case "Not Started": .Font.ColorIndex = 1 ' Black Case "Completed": .Font.ColorIndex = 2 'White Case "Manageable Issues": .Font.ColorIndex = 1 'Black Case "Significant Issues": .Font.ColorIndex = 2 'White Case "On Track": .Font.ColorIndex = 2 'White End Select End If End With ws_exit: Application.EnableEvents = True End Sub **What i'm getting with this is the font just is never changing color. If the code is right, maybe I'm setting it up wrong in the editor - such as where I'm placing it??? Thanks. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
3 Conditions for Data Validation for a List
You're welcome. Keiji Anders wrote: Thanks Keiji! Works brilliantly! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use VBA to reset data validation (=list) value to first value in that list (list is a named range) | Excel Programming | |||
Crazy Data Validation ... List Validation Not Working | Excel Programming | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Programming | |||
Combining conditions for data entry validation | Excel Worksheet Functions |