Color Picker in Excel

[Shuvro Basu]

Hi All,

I will appreciate if someone can guide me to do a color picker using
VBA in excel. I need to display the 57 colors of the palette and get
the color index returned to put that value in a cell.
A simple box with colors to do this using userform on xl's inbuilt
dialog will be fine.

Looking forward to all your help !

Thanx and regds
Shuvro

 

[Bob Phillips]

'-----------------------------­------------------------------­--------------
--
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

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Shuvro Basu]

Hi Bob,

Thanks for your response. However I wanted to know whether we can use
this in a userform instead of a worksheet. I'm trying to select a color
(using the inbuilt dialog for palette) however not able to get the
index value returned. Hence my question.

Regds

 

[Bob Phillips]

You can use that in a userform. Just create a coomandbutton and do this in
its click event and see

Msgbox GetColorindex()

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Shuvro Basu]

Hi Bob,

Thank you once again for your response. Ok. Here is the issue. What I
want to do is change the color of a selected chart series (one data
point which could be a column, line or bar) to the color I have
selected in the dialog with the color I have selected (from the
function).

Hope I have been more clear this time to explain my question

Regds

 

[Bob Phillips]

I would suggest you record a macro that changes the data point in the chart
series, and then change the hardcoded colorindex value with the value
returned from that function.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Shuvro Basu]

Hi Bob,

Here is the code that I'm using.. and it is not working :-(.

I use this code in a commandbutton click event:

On Error Resume Next
op = GetColorindex()

With Selection.Border
.Weight = xlHairline
.LineStyle = xlNone
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
Selection.Fill.OneColorGradient Style:=msoGradientVertical,
Variant:=4, _
Degree:=0.231372549019608

ActiveChart.SeriesCollection.Select
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = op
End With

Please let me know what I need to do to get this corrected. Any help
will be highly appreciated.

regds

 

[Shuvro Basu]

Addendum:

Further, when I select the column and try to run the macro I get an
error in the following line :
Set rngCurr = Selection in the ColorIndex() function. Though the data
points are selected, it seems that it is not treated as a valid
selection. Since I'm not using this on a cell in a worksheet rather on
a chart, I had removed the reference to the sheet in line :
Range("IV1").Select and put ActiveSheet.ActiveChart.Select and tried..
obviously this also failed. Moreover it is not easy to determine which
data point (i.e. series in the chart) will be selected by the user,
hence I cannot use : ActiveChart.SeriesCollection(1).Select.

Seems that I have landed in more trouble than I expected !

Note: (All code reference above refer to the ColorIndex() function)

Regds

 

[Bob Phillips]

Maybe try this version

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

Application.ScreenUpdating = False
Set oThis = ActiveSheet
Worksheets(1).Activate
Set rngCurr = Selection
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
oThis.Activate
Application.ScreenUpdating = True
End Function


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Shuvro Basu]

Hi Bob,

Cool ! This is working.. Really appreciate your help from the bottom of
my heart...

With best wishes and regards
Shuvro

 

[Bob Phillips]

Shame about the background when it throws up the dialog, but I'm glad it is
working for you.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)