List box that changes fill color

[sarahjanebaird]

Hello,

I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain color fill the date but I still need to be able to see the date in the cell. I want to use a drop down box because the people who will be using this cannot be fully trusted to use the chosen colors if they have to go through the fill list (I saw their last year list and it is a mess of colors)

Please help! I've uploaded an image hopefully showing better what I mean!

 

[sarahjanebaird]

I'm new to forums but is there something I can do that like, ups your score or something? Hahaha I have no idea what I am talking about but if there is anything I can do to make you more reputable on here just let me know!

 

[sarahjanebaird]

Glad it is going down well with everyone.

I tried to keep things very simple
- and I have not tested it like your users will when they do something I did not anticipate (and they will)
- so it may misbehave in one way or another when used in anger

Come back here if you have any questions

What the code is doing should be fairly obvious although you may be puzzled by a couple of things
eg
Why Case 1 rather than Case 100% ?
- it is because VBA interpreted 100% as the number 1 (which of course it is) unlike the other values which were interpreted as text

Debug.Print ListBox1.Text ?
- line left in in error
- it allows me to print output to VBA immediate window to see what is going on "under the bonnet"
- that is how I realised Case "100%" needed amending to Case 1
- so you can delete line Debug.Print ListBox1.Text

'disables normal double-click mode ?
- should read disables normal right-click mode
- I amended the trigger and forgot to amend the comment
- please amend it in your code

I found one thing! When I have an unfilled cell clicked and then I save and exit - when I open it again that cell is now filled with the first color option or the last color I filled with. I tried to be smart and figure out how to fix that but I'm in over my head haha.

 

[Yongle]

The obvious thing to try is to move the cursor away from the calendar range at the end of the code

Try adding the last line as illustrated below

    Range("E2").Activate

Let me know if that fixes that issue

Private Sub ListBox1_Change()
    Dim C As String
    Debug.Print ListBox1.Text
    Select Case ListBox1.Text
        Case "< 40 %":      C = "F2"
        Case "40 - 49 %":   C = "J2"
        Case "50 - 59 %":   C = "N2"
        Case "60 - 69 %":   C = "R2"
        Case "70 - 79 %":   C = "V2"
        Case "80 - 89 %":   C = "Z2"
        Case "90 - 99 %":   C = "AD2"
        Case 1:             C = "AH2"
        Case Else:          C = "E2"
    End Select
    If Not C = "" Then ActiveCell.Interior.Color = Range(C).Interior.Color
    With ListBox1
        .Left = Range("BA1").Left
        .Top = 1
        .Visible = False
    End With
    Range("E2").Activate
End Sub

 

[sarahjanebaird]

The obvious thing to try is to move the cursor away from the calendar range at the end of the code

Try adding the last line as illustrated below

    Range("E2").Activate

Let me know if that fixes that issue

Private Sub ListBox1_Change()
    Dim C As String
    Debug.Print ListBox1.Text
    Select Case ListBox1.Text
        Case "< 40 %":      C = "F2"
        Case "40 - 49 %":   C = "J2"
        Case "50 - 59 %":   C = "N2"
        Case "60 - 69 %":   C = "R2"
        Case "70 - 79 %":   C = "V2"
        Case "80 - 89 %":   C = "Z2"
        Case "90 - 99 %":   C = "AD2"
        Case 1:             C = "AH2"
        Case Else:          C = "E2"
    End Select
    If Not C = "" Then ActiveCell.Interior.Color = Range(C).Interior.Color
    With ListBox1
        .Left = Range("BA1").Left
        .Top = 1
        .Visible = False
    End With
    Range("E2").Activate
End Sub

My gosh it worked, THANK YOU!!! Did you go to school for all of this?? SPOKE TOO SOON, it worked on the first save but I tried again and it did the same thing. Woops!

 

[Yongle]

Did you go to school for all of this??

No - a lot of self-induced pain

it worked on the first save but I tried again and it did the same thing

You will be adding in the same line of code
... but it needs to go in a different place so that workbook save triggers cell E2 being selected

1. Paste everything inside the code window below into ThisWorkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Range("E2").Activate
End Sub

2. And this is how you do that ...

3.
{ALT}{F11} to enter VBA editor
Double-click on ThisWorkbook in pane on LEFT (opens up an empty window on the right)

4. Paste the code into that window, so that it looks like this

5.
{ALT}{F11} to go back to Excel

Let me know how you get on

 

[sarahjanebaird]

No - a lot of self-induced pain




You will be adding in the same line of code
... but it needs to go in a different place so that workbook save triggers cell E2 being selected

1. Paste everything inside the code window below into ThisWorkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Range("E2").Activate
End Sub

2. And this is how you do that ...

3.
{ALT}{F11} to enter VBA editor
Double-click on ThisWorkbook in pane on LEFT (opens up an empty window on the right)

View attachment 3622

4. Paste the code into that window, so that it looks like this

View attachment 3623

5.
{ALT}{F11} to go back to Excel

Let me know how you get on

That is perfect!!! It fills in E2 with color but I just put a little white shape over it and they'll neeever notice haha! I know you're doing all of the hard work but THIS IS FUN! THANK YOU AGAIN!!!!! I literally can't say it enough.

 

[Yongle]

THIS IS FUN!

hence so much self-induced pain
Glad you are now "good to go"