How to show Theme color window with Excel VBA?

In VBA, I can show color window as below. After selecting a color from this color window, I can use the selected color for changing Font color, Cell background color, etc.

Application.Dialogs(xlDialogEditColor).Show(10)

Capture.PNG

But I want to show below window. Please kindly advise what will be the code and how I can implement in VBA. Let's say I want to color a font with selected color.

Untitled.png

By default, the ExecuteMso method is used to "press" the selected icon, e.g.

Application.CommandBars.ExecuteMso ("FontColorPicker")

But in the case of the gallery, the above code will give the font in the active cell the color last selected in the gallery, without showing the color picker.
Another method to "press" an icon is to use a keyboard shortcut. When you press the left Alt key, you will get prompts as to what keys you need to press sequentially to trigger the icon. In the Polish language version, to display the color picker for fonts, I will use Left Alt, G, F, 1. To color the background of the cells, I will use Left Alt, G, H, 1. In the code below, adjust the key sequence according to your language version.

Option Explicit

Private Declare Sub keybd_event Lib "User32" _
                                 (ByVal bVk As Byte, _
                                  ByVal bScan As Byte, _
                                  ByVal dwFlags As Long, _
                                  ByVal dwExtraInfo As Long)


 
Private Const VK_LMENU = &HA4
Private Const KEYEVENTF_KEYUP = &H2


Sub Test()
  Application.OnTime Now, "TestKbd"
End Sub


Sub TestKbd()
  Call SequenceKeys(VK_LMENU, "G", "F", "1")
End Sub


Sub SequenceKeys(ParamArray vArr() As Variant)
  Dim i As Long
 
  For i = LBound(vArr) To UBound(vArr)
    If TypeName(vArr(i)) = "Integer" Then
      keybd_event vArr(i), 0, 0, 0
      keybd_event vArr(i), 0, KEYEVENTF_KEYUP, 0
    ElseIf TypeName(vArr(i)) = "String" Then
      keybd_event Asc(vArr(i)), 0, 0, 0
      keybd_event Asc(vArr(i)), 0, KEYEVENTF_KEYUP, 0
    End If
  Next i
End Sub

If you will be running the macro with a button, you can assign the TestKbd macro to it. If you will be running from the Macro window (keyboard shortcut Alt+F8), then you must call the Test macro. If you will be running this macro with a keyboard shortcut, then the Test macro should at least have this form:

Sub Test()
  Application.OnTime Now + TimeSerial(0, 0, 1), "TestKbd"
End Sub

and all the keys of the applied shortcut must be released before one second expires.

In theory, you could use the SendKeys method instead of winAPi keybd_event. However, the use of SendKeys on many computers causes a problem (most often the numeric keypad "freaks out") so for many years, however, I have recommended winAPI, which is more stable in execution.

Artik

In my VBA userform, I have command button. I want when user will click in that command button, then below color picker will appear. And when user will select a color then a label in that userform will change the background color.
Attachment 816307

With your below code is not showing above color window, even showing nothing. Sorry may be i could not apply in right way. Please help.

Application.CommandBars.ExecuteMso ("FontColorPicker")

Currently I am using below code.

Private Sub CellColorSelLbl_Click()
On Error Resume Next
Dim lcolor As Long
If Application.Dialogs(xlDialogEditColor).Show(10) = True Then
  'user pressed OK
  lcolor = ActiveWorkbook.Colors(10)
 
  CellColorSelLbl.BackColor = lcolor
Else
  'user pressed Cancel
End If

End Sub