none
Drop down selection RRS feed

  • Question

  • Hi

    I am looking for some help on creating a dropdown selection for cells in excel so when you select the cell the dropdown box appears and you can select an option of four colours to fill the cell.

    The cells are already refilled with numbers, I do not want this to change, I only want to select the fill colour with all the other data being protected, so nobody can edit the data on the worksheet on the cell fill colour with four options.

    Any help would be very much appreciated.

    Kind regards

    Monday, August 20, 2018 12:07 PM

All replies

  • The standard data validation dropdown can only be used to change the value of a cell, not its fill colour.

    And combo boxes display text values, not colours.

    An alternative would be to display a userform when the user double-clicks a cell in a specific range.

    Code in the worksheet module:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        If Not Intersect(Range("A1:A5"), Target) Is Nothing Then
            Cancel = True
            frmColour.Show
        End If
    End Sub

    Code in the userform module:

    Private Sub cmdCyan_Click()
        ActiveSheet.Unprotect 'Password:="secret"
        ActiveCell.Interior.Color = vbCyan
        ActiveSheet.Protect 'Password:="secret"
        Unload Me
    End Sub

    Private Sub cmdGreen_Click()
        ActiveSheet.Unprotect 'Password:="secret"
        ActiveCell.Interior.Color = vbGreen
        ActiveSheet.Protect 'Password:="secret"
        Unload Me
    End Sub

    Private Sub cmdRed_Click()
        ActiveSheet.Unprotect 'Password:="secret"
        ActiveCell.Interior.Color = vbRed
        ActiveSheet.Protect 'Password:="secret"
        Unload Me
    End Sub

    Private Sub cmdYellow_Click()
        ActiveSheet.Unprotect 'Password:="secret"
        ActiveCell.Interior.Color = vbYellow
        ActiveSheet.Protect 'Password:="secret"
        Unload Me
    End Sub

    See the sample workbook on DropBox: https://www.dropbox.com/s/arlqvqytbkigbe1/Colours.xlsm?dl=1


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Simon Wu-MSFT Monday, September 3, 2018 10:14 AM
    Monday, August 20, 2018 2:27 PM