How to display Color Palette with vba code

[Guest]

I am trying to pop up the Color Palette to allow a user to pick a color, then
use that color as a background for selected cells. In the past, I have used
the Common Dialog Control to allow users to pick a color. It seems that the
control has disappeared from my list of available controls. Is it not
available in VBA for Office 2003?

I also tried using the XlBuiltInDialog.xlDialogColorPalette but can't seem
to get it to work. Any code samples would be greatly appreciated.

 

[Bob Phillips]

Keith,

Here is a function that can use the built-in colour dialog

'-----------------------------­------------------------------­--------------
--
Function GetColorindex(Optional Text As Boolean = False) As Long
'-----------------------------­------------------------------­--------------
--
Dim rngCurr As Range

Set rngCurr = Selection
Application.ScreenUpdating = False
Range("IV1").Select
Application.Dialogs(xlDialogPatterns).Show
GetColorindex = ActiveCell.Interior.ColorIndex
If GetColorindex = xlColorIndexAutomatic And Not Text Then
GetColorindex = xlColorIndexNone
End If
ActiveCell.Interior.ColorIndex = xlColorIndexAutomatic
rngCurr.Select
Set rngCurr = ActiveSheet.UsedRange
Application.ScreenUpdating = True
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)