Excel VBA - Change current color in color picker

user_8783311 0
Viewed 615

How do i change the current color in Excel's color picker? I've googled and have absolutely no idea how to do this. I have seen this Application.Dialogs(xlDialogEditColor) but it's not what I want.

enter image description here

Basically, when i run my macro it will set the colors in the color picker (font & fill color pickers) to a specified color. For example, if the specified color is red then this is what will happen

enter image description here

any assistance will be appreciated, thanks

vba excel
1 Answers

This article by The Spreadsheet Guru can help you to achieve this. The article is written in case you want to add multiple colors to the recent colors section of the palette, but you can easily use that method for a single color (by editing the array in the code below to include just one RGB color code) and that will result in that color being the one selected for the user.

The suggested code in the article is the following:

'Declare Sleep() API
  #If VBA7 Then ' Excel 2010 or later
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
  #Else ' Excel 2007 or earlier
    Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
  #End If

Sub LoadRecentColors()
'PURPOSE: Use A List Of RGB Codes To Load Colors Into Recent Colors Section of Color Palette
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim ColorList As Variant
Dim CurrentFill As Variant

'Array List of RGB Color Codes to Add To Recent Colors Section (Max 10)
  ColorList = Array("066,174,093", "184,055,038", "046,062,081", "056,160,133")

'Store ActiveCell's Fill Color (if applicable)
  If ActiveCell.Interior.ColorIndex <> xlNone Then CurrentFill = ActiveCell.Interior.Color

'Optimize Code
  Application.ScreenUpdating = False

'Loop Through List Of RGB Codes And Add To Recent Colors
  For x = LBound(ColorList) To UBound(ColorList)
    ActiveCell.Interior.Color = RGB(Left(ColorList(x), 3), Mid(ColorList(x), 5, 3), Right(ColorList(x), 3))
    DoEvents
    SendKeys "%h"
    Sleep 500 'Pause half-second (units in milliseconds)
    SendKeys "h"
    Sleep 500 'Pause half-second (units in milliseconds)
    SendKeys "m"
    Sleep 500 'Pause half-second (units in milliseconds)
    SendKeys "~"
    Sleep 500 'Pause half-second (units in milliseconds)
    DoEvents
  Next x

'Return ActiveCell Original Fill Color
  If CurrentFill = Empty Then
    ActiveCell.Interior.ColorIndex = xlNone
  Else
    ActiveCell.Interior.Color = currentColor
  End If

End Sub

However, I would suggest to always set the wait argument of the SendKeys method to true in order to let VBA know it has to wait for the key press to be processed before continuing (e.g. SendKeys "%h", True). It will increase the probability that your key press will be registered and executed properly.

I'm not sure, but since this whole procedure is running within Excel and there is no other application involved, it might not even be necessary to use the Sleep function after each key press, but it's never a bad idea to be overly cautious with the SendKeys method.

So, that will work for the filling tool. Now, for the font color, the shortcut should be Alt+H,FC,M

user_59588420
Related