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]

F7 - AJ30 is the calendar!

 

[Yongle]

I have been delayed ...
My wife is at the southern tip of South America about to board a ship heading to the Antarctic and what is probably the last phone call for 3 weeks took priority!
I am back in VBA mode and will post within the hour

 

[sarahjanebaird]

I have been delayed ...
My wife is at the southern tip of South America about to board a ship heading to the Antarctic and what is probably the last phone call for 3 weeks took priority!
I am back in VBA mode and will post within the hour

Oh my gosh, that is pretty amazing, is she a scientist?

Also I am at work doing this and will be leaving in 2 hours so I won't be able to respond until tomorrow anyway once I leave! Again - I REALLY appreciate this!

 

[Yongle]

No - she is accompanying her Great Aunt (who is a scientist) on a trip of a lifetime.
It is all play and no work!

 

[sarahjanebaird]

No - she is accompanying her Great Aunt (who is a scientist) on a trip of a lifetime.
It is all play and no work!

Even better! I wish her well, that will be a dream.

 

[Yongle]

The code is triggered when user right-clicks in a cell in the calendar range
That moves the listbox into position
User selects a value
The change in value triggers the cell to be coloured
It should ignore cells that contain no value or cells that contain text inside the calendar range
The listbox is then rendered invisible and moved (out of the way) to cell BA1

Test it to see if it does what you want - we may be able to modify it to behave differently if you tell me what you want etc
(but not every wish is possible!!)

The code MUST be placed in the SHEET module and you do that like this ....
right-click sheet tab \ View Code \ paste code below into that window \ go back to Excel with {ALT}{F11}
everything from Option Explicit down to (the second) End Sub should be copy\pasted

Option Explicit

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim Calendar As Range
    Set Calendar = Range("F7:AJ30")
    If Selection.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Calendar) Is Nothing Then
        If Target < 1 Then Exit Sub
        If Target > 31 Then Exit Sub
        Cancel = True               'disables normal double-click mode
        With ListBox1
            .Left = Target.Left
            .Top = Target.Top
            .Visible = True
        End With
    End If
End Sub

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
End Sub

NOTE : You must save the workbook as macro enabled henceforth

 

[sarahjanebaird]

IT WORKSSSS!!! OH MY GOSH. THANK YOU THANK YOU THANK YOU THANK YOU!!!!

 

[sarahjanebaird]

Everyone here thinks that is so cool. I can't say thank you enough!!!

 

[Yongle]

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

 

[sarahjanebaird]

I am sure they will find a way to mess it up, haha they always do, I am playing with it now and I just think it's amazing. Definitely opens my eyes to how much more I can be doing with Excel!! Today was the first time I have ever heard "VBA" before and I am so excited to try and learn more. YAY!