Excel color palette on user form?

[Shane Henderson]

Hello all,

I am working on a model where the users can select from multiple data sets;
there are twelve different types of inputs. There are several different
data set providers (i.e. research organisation 1, research org 2), and they
may provide from 1 to 12 of the inputs. The purpose of the model is to allow
us to combine different inputs to see the effects on the output.

On the main output sheet, the names of the data sets are displayed and I
would like to color code the names of the inputs so that all inputs from
each organisation are the same color. I think I have worked out a way to
do this in the VBA procedure which I use to run the model using some useful
suggestions and code on the Internet (i.e. www.cpearson.com )

However, I would also like to add a user form to the model which allows the
addtion of data sets. I would like to enable users to choose a color code
for the data set from the list (ideally visually).

Does anyone have any suggestions about how this could be achieved?

Thanks,
Shane

[Robin Hammond]

Shane,

You can do it easily with the commondialog controls. They are packaged with
vb but can be used in vba. You need to add them to your toolbox using the
additional controls command, then look for Microsoft Common Dialog Control,
or browse for comdlg32.ocx in sys 32. Once it's on the toolbox, draw it onto
a form, then you can do something like this:

Private Function GetColour() as long
CommonDialog1.ShowColor
GetColour = CommonDialog1.Color
End Function

Robin Hammond
www.enhanceddatasystems.com

"Shane Henderson" <shane.henderson.nospam@bigpond.com> wrote in message
news:8e1d418917238c7c551c0a5c5bd@msnews.microsoft.com...
> Hello all,
>
> I am working on a model where the users can select from multiple data
> sets; there are twelve different types of inputs. There are several
> different data set providers (i.e. research organisation 1, research org
> 2), and they may provide from 1 to 12 of the inputs. The purpose of the
> model is to allow us to combine different inputs to see the effects on the
> output.
>
> On the main output sheet, the names of the data sets are displayed and I
> would like to color code the names of the inputs so that all inputs from
> each organisation are the same color. I think I have worked out a way to
> do this in the VBA procedure which I use to run the model using some
> useful suggestions and code on the Internet (i.e. www.cpearson.com )
>
> However, I would also like to add a user form to the model which allows
> the addtion of data sets. I would like to enable users to choose a color
> code for the data set from the list (ideally visually).
>
> Does anyone have any suggestions about how this could be achieved?
>
> Thanks,
> Shane
>
>

[Greg Wilson]

Adapted from John Walkenbach's book Excel 2000 Power Programming with VBA
pages 418 to 421. I use labels instead of command buttons and create the
effect of depressing a button by toggling between sunken special effect and
flat special effect with a sleep period between.

I include a macro that will create a simple UF that includes a check box and
the Color Palette. This is highly simplified and intended for illustrative
purposes only. The check box demos how to enable/disable the color palette.
You will need to create a Class Module.

*****************************
Step No. 1-

i) Create a Class Module.
ii) Rename the Class Module "ColorBtnClass" instead of "Class1"
iii) Paste the following to the code module:

Public WithEvents ColorBtn As MSForms.Label
Private Sub ColorBtn_Click()
ColorBtn.SpecialEffect = fmSpecialEffectSunken
DoEvents
Sleep 50
ColorBtn.SpecialEffect = fmSpecialEffectFlat
Range("A1:D20").Interior.Color = ColorBtn.BackColor
End Sub

*********************
Step No. 2 -

In a standard module paste the following. You need only run the larger macro
once to create the UF:

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub MakeColorPallette()
Dim i, ii, iii
Dim UFrm As Object
Dim Frm As Control
Dim CBox As Control

Set UFrm = ThisWorkbook.VBProject.VBComponents.Add(3)
With UFrm
.Properties("Height") = 180
.Properties("Width") = 110
.Properties("Caption") = "Color Palette Test"
Set CBox = .Designer.Controls.Add("Forms.CheckBox.1")
With CBox
.Caption = "Show Color Palette"
.ForeColor = RGB(20, 20, 100)
.Top = 10
.Left = 10
.Width = 90
.Height = 20
End With
Set Frm = .Designer.Controls.Add("Forms.Frame.1")
With Frm
.Caption = "Color Palette"
.ForeColor = RGB(20, 20, 100)
.Enabled = False
.Top = 35
.Left = 10
.Width = 87
.Height = 115
End With
End With
For i = 0 To 7
For ii = 0 To 6
iii = iii + 1
Set Ctrl = Frm.Controls.Add("Forms.Label.1")
With Ctrl
.Left = ii * 12
.Top = 5 + i * 13
.Height = 13
.Width = 12
.BackColor = UFrm.Designer.BackColor
.Caption = ""
.ControlTipText = iii
.BorderStyle = 0
.Enabled = False
End With
Next
Next
End Sub

Sub UFShow
UserForm1.Show
End Sub

*****************************
Step No. 3 -

i) Run the above macro MakeColorPalette to create a simple demo version of a
UF with color palette.
ii) Call the UF with the macro UFShow
iii) Click the buttons.

Regards,
Greg

[Greg Wilson]

Here I go again. As usual I forgot to include something. Paste the following
to the UF's code module:

Dim ColorBtnGroup(1 To 56) As New ColorBtnClass

Private Sub UserForm_Initialize()
Dim i As Integer
For i = 1 To 56
Set ColorBtnGroup(i).ColorBtn = Controls("Label" & i)
Next
End Sub

Private Sub CheckBox1_Change()
Dim i As Integer
Frame1.Enabled = CheckBox1
If CheckBox1 Then
For i = 1 To 56
With Controls("Label" & i)
.Enabled = True
.BackColor = ActiveWorkbook.Colors(.ControlTipText)
End With
Next
Else
For i = 1 To 56
With Controls("Label" & i)
.Enabled = False
.BackColor = Me.BackColor
End With
Next
End If
End Sub

Greg



"Greg Wilson" wrote:

> Adapted from John Walkenbach's book Excel 2000 Power Programming with VBA
> pages 418 to 421. I use labels instead of command buttons and create the
> effect of depressing a button by toggling between sunken special effect and
> flat special effect with a sleep period between.
>
> I include a macro that will create a simple UF that includes a check box and
> the Color Palette. This is highly simplified and intended for illustrative
> purposes only. The check box demos how to enable/disable the color palette.
> You will need to create a Class Module.
>
> *****************************
> Step No. 1-
>
> i) Create a Class Module.
> ii) Rename the Class Module "ColorBtnClass" instead of "Class1"
> iii) Paste the following to the code module:
>
> Public WithEvents ColorBtn As MSForms.Label
> Private Sub ColorBtn_Click()
> ColorBtn.SpecialEffect = fmSpecialEffectSunken
> DoEvents
> Sleep 50
> ColorBtn.SpecialEffect = fmSpecialEffectFlat
> Range("A1:D20").Interior.Color = ColorBtn.BackColor
> End Sub
>
> *********************
> Step No. 2 -
>
> In a standard module paste the following. You need only run the larger macro
> once to create the UF:
>
> Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
>
> Sub MakeColorPallette()
> Dim i, ii, iii
> Dim UFrm As Object
> Dim Frm As Control
> Dim CBox As Control
>
> Set UFrm = ThisWorkbook.VBProject.VBComponents.Add(3)
> With UFrm
> .Properties("Height") = 180
> .Properties("Width") = 110
> .Properties("Caption") = "Color Palette Test"
> Set CBox = .Designer.Controls.Add("Forms.CheckBox.1")
> With CBox
> .Caption = "Show Color Palette"
> .ForeColor = RGB(20, 20, 100)
> .Top = 10
> .Left = 10
> .Width = 90
> .Height = 20
> End With
> Set Frm = .Designer.Controls.Add("Forms.Frame.1")
> With Frm
> .Caption = "Color Palette"
> .ForeColor = RGB(20, 20, 100)
> .Enabled = False
> .Top = 35
> .Left = 10
> .Width = 87
> .Height = 115
> End With
> End With
> For i = 0 To 7
> For ii = 0 To 6
> iii = iii + 1
> Set Ctrl = Frm.Controls.Add("Forms.Label.1")
> With Ctrl
> .Left = ii * 12
> .Top = 5 + i * 13
> .Height = 13
> .Width = 12
> .BackColor = UFrm.Designer.BackColor
> .Caption = ""
> .ControlTipText = iii
> .BorderStyle = 0
> .Enabled = False
> End With
> Next
> Next
> End Sub
>
> Sub UFShow
> UserForm1.Show
> End Sub
>
> *****************************
> Step No. 3 -
>
> i) Run the above macro MakeColorPalette to create a simple demo version of a
> UF with color palette.
> ii) Call the UF with the macro UFShow
> iii) Click the buttons.
>
> Regards,
> Greg
>
>
>
>

[Shane Henderson]

Thanks very much Robin,
I'll give this a try!

SH


> Shane,
>
> You can do it easily with the commondialog controls. They are packaged
> with vb but can be used in vba. You need to add them to your toolbox
> using the additional controls command, then look for Microsoft Common
> Dialog Control, or browse for comdlg32.ocx in sys 32. Once it's on the
> toolbox, draw it onto a form, then you can do something like this:
>
> Private Function GetColour() as long
> CommonDialog1.ShowColor
> GetColour = CommonDialog1.Color
> End Function
> Robin Hammond
> www.enhanceddatasystems.com
> "Shane Henderson" <shane.henderson.nospam@bigpond.com> wrote in
> message news:8e1d418917238c7c551c0a5c5bd@msnews.microsoft.com...
>
>> Hello all,
>>
>> I am working on a model where the users can select from multiple data
>> sets; there are twelve different types of inputs. There are several
>> different data set providers (i.e. research organisation 1, research
>> org 2), and they may provide from 1 to 12 of the inputs. The purpose
>> of the model is to allow us to combine different inputs to see the
>> effects on the output.
>>
>> On the main output sheet, the names of the data sets are displayed
>> and I would like to color code the names of the inputs so that all
>> inputs from each organisation are the same color. I think I have
>> worked out a way to do this in the VBA procedure which I use to run
>> the model using some useful suggestions and code on the Internet
>> (i.e. www.cpearson.com )
>>
>> However, I would also like to add a user form to the model which
>> allows the addtion of data sets. I would like to enable users to
>> choose a color code for the data set from the list (ideally
>> visually).
>>
>> Does anyone have any suggestions about how this could be achieved?
>>
>> Thanks,
>> Shane