Changing colour of cells using command button (VBA Excel)

Question

I currently have a userform that loads up with three command buttons (primary, secondary and tertiary). The idea here is that a user will select their own colour scheme using these three buttons.

I have cells in my workbook that represent each colour and then a macro which sets all chart objects to these colours (using the cells) when it is run.

I have managed to initialise the userform so that the three command buttons interior colour is determined by the three cells in my workbook:

Private Sub UserForm_Initialize()

Dim cs As Worksheet
Set cs = Sheets("ColourScheme")

TextBox1.SetFocus '' Shift focus away from primary

Primary.BackColor = cs.Range("B1").Interior.color
Secondary.BackColor = cs.Range("B2").Interior.color
Tertiary.BackColor = cs.Range("B3").Interior.color

End Sub

What I want to do now is that when each button is clicked, the colour palette loads up, the user selects a colour either using the wheel or an RGB figure and then finally the cells in my workbook and the command buttons interior colour change based on the user's choice.

I don't know if this can be done but so far I'm having no luck with anything I've tried to load the palette up:

Private Sub Primary_Click()

Application.Dialogs.Item(xlDialogColorPalette).Show

End Sub

Sorry @ShaiRado I haven't tried it. Managed to work out my own solution before I checked yours. Thanks though. — James Baker
– James Baker, Commented Aug 31, 2016 at 14:30

Shai Rado · Accepted Answer · 2016-08-31 12:03:45Z

Try the function which I use on several Applications, it's slightly converted to fit your original minimalized code, let me know if it works for you...

Private Sub Primary_Click()

Const ColorIndexLast                As Long = 32       'index of last custom color in palette
Dim PickNewColor                    As Double          'color that was picked in the dialogue
Dim myOrgColor                      As Double          'original color of color index 32

'save original palette color,modify Range according to your needs
myOrgColor = Range("A1").Interior.Color

'call the color picker dialogue
If Application.Dialogs(xlDialogEditColor).Show(ColorIndexLast) = True Then

    ' "OK" was pressed, read the new color from the palette
    PickNewColor = ActiveWorkbook.Colors(ColorIndexLast)
    ActiveWorkbook.Colors(ColorIndexLast) = myOrgColor  ' reset palette color to its original value
Else
    ' "Cancel" was pressed, palette wasn't changed >> return old color (or xlNone if no color was passed to the function)
    PickNewColor = myOrgColor
End If

' update Colors in relevant Cell
Range("A1").Interior.Color = PickNewColor

End Sub

James Baker · Accepted Answer · 2016-08-31 14:27:50Z

0

This is the solution I arrived at:

Private Sub Primary_Click()

Dim cs As Worksheet
Set cs = Sheets("ColourScheme")

Dim newColour As Long

Application.Dialogs(xlDialogEditColor).Show (1)

newColour = ThisWorkbook.Colors(1)

cs.Range("B1").Interior.color = newColour

Primary.BackColor = newColour

End Sub

answered Aug 31, 2016 at 14:27

James Baker

3333 gold badges9 silver badges27 bronze badges

Collectives™ on Stack Overflow

Changing colour of cells using command button (VBA Excel)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related