Selecting color number to use in vba code.

  • Thread starter Thread starter Roger on Excel
  • Start date Start date
R

Roger on Excel

I use code to fills in cells for the color red - the number for red is 3.

Thus the selected line in the code reads :

..Interior.ColorIndex = 3

I would like the user to be able to define the color themselves in the
spreadheet with a dropdown list.

Is there a way to transpose the code number for the chosen color to the vba
code macro?

Can anyone help?
Thanks,

Roger
 
give this a try. create a userform with 1 command button on it. name the
userform Colors, instead of userform1 or whatever the default name is.
(doesn't matter how big the userform is or where the commandbutton is, as long
as they are named Colors and CommandButton1)
then paste this code in the userform code module.

put some text in sheet1 a2:a19 and run the userform. (this sheet and range can
be changed in the code)

hopefully it won't wrap when it's posted.

Option Explicit
Dim i As Long
Dim z As Long
Dim arr As Variant, arr2 As Variant
Dim newButton As MSForms.Control
Dim newButton2 As MSForms.Control
Dim wks1 As Worksheet
Dim rng As Range

Private Sub UserForm_Activate()
Application.ScreenUpdating = False
arr = Array("C0C0C0", "FFCCCC", "CCFFCC", "99FFFF", "FFCC99", _
"CC99FF", "CCCC33", "00CCFF", "996666")
arr2 = Array("000000", "663300", "808000", "000000", "800000", _
"003399", "000000", "000000", "FFFFFF")

z = 20
For i = 1 To 9
Set newButton2 = Me.Controls.Add("Forms.textbox.1")
newButton2.Value = ""
With newButton2
.Left = 10
.Top = 5 + z
.Height = 20
.Visible = True
.FontSize = 9
.Font.Name = "Arial"
.Font.Bold = True
.Name = "textbox" & i
.BackStyle = 1
.ForeColor = "&H" & arr2(i - 1)
.BackColor = "&H" & arr(i - 1)
.Enabled = True
.Width = 85
.Value = "Sample Text"
.TextAlign = fmTextAlignCenter
'.AutoSize = True
End With

Set newButton = Me.Controls.Add("Forms.optionbutton.1")

With newButton
.Left = 110
.Top = 5 + z
.Height = 20
.Width = 15
.Visible = True
.Name = "optionbutton" & i
End With
z = z + 20
Next

With Me
.Caption = "Select Colors"
.Height = 300
.Width = 240
With .CommandButton1
.Left = 150
.Top = 210
.Height = 20
.Width = 65
.Visible = True
.Caption = "OK"
End With
End With
Application.ScreenUpdating = True
End Sub
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False

Set wks1 = Worksheets("sheet1")
Set rng = wks1.Range("A2:A10")
wks1.Unprotect
Select Case True

Case Colors!OptionButton1 = True
rng.Interior.ColorIndex = 15
rng.Font.ColorIndex = 1
Case Colors!OptionButton2 = True
rng.Interior.ColorIndex = 24
rng.Font.ColorIndex = 49
Case Colors!OptionButton3 = True
rng.Interior.ColorIndex = 35
rng.Font.ColorIndex = 31
Case Colors!OptionButton4 = True
rng.Interior.ColorIndex = 36
rng.Font.ColorIndex = 1
Case Colors!OptionButton5 = True
rng.Interior.ColorIndex = 37
rng.Font.ColorIndex = 11
Case Colors!OptionButton6 = True
rng.Interior.ColorIndex = 38
rng.Font.ColorIndex = 53
Case Colors!OptionButton7 = True
rng.Interior.ColorIndex = 42
rng.Font.ColorIndex = 1
Case Colors!OptionButton8 = True
rng.Interior.ColorIndex = 44
rng.Font.ColorIndex = 1
Case Colors!OptionButton9 = True
rng.Interior.ColorIndex = 47
rng.Font.ColorIndex = 2
End Select

Application.ScreenUpdating = True
End Sub
 
Hi Gary,

This is a little beyond what i was looking for, but i would like to thank
you for your advice.

All the best,

Roger
 
Gary,

I tried the code - very very nice - i love it !!!

May i ask how one would just use the form to paste the color and the color
index of the chosen color into cell a2 only?

All the best,

Roger
 
try changing this line and it should set the color in A2.

Set rng = wks1.Range("A2:A10")

to

Set rng = wks1.Range("A2")
 
Thanks Gary,

How would one paste the color number into the same cell - eg 3 for red?

Thanks,

Roger
 
forms use hex numbers for fore and back colors, not decimals. that's what's in
the 2 arrays.
 
i misunderstood, try replacing the selection code with this:

Select Case True

Case Colors!OptionButton1 = True
rng.Interior.ColorIndex = 15
rng.Font.ColorIndex = 1
rng.Value = rng.Interior.ColorIndex
Case Colors!OptionButton2 = True
rng.Interior.ColorIndex = 24
rng.Font.ColorIndex = 49
rng.Value = rng.Interior.ColorIndex
Case Colors!OptionButton3 = True
rng.Interior.ColorIndex = 35
rng.Font.ColorIndex = 31
rng.Value = rng.Interior.ColorIndex
Case Colors!OptionButton4 = True
rng.Interior.ColorIndex = 36
rng.Font.ColorIndex = 1
rng.Value = rng.Interior.ColorIndex
Case Colors!OptionButton5 = True
rng.Interior.ColorIndex = 37
rng.Font.ColorIndex = 11
rng.Value = rng.Interior.ColorIndex
Case Colors!OptionButton6 = True
rng.Interior.ColorIndex = 38
rng.Font.ColorIndex = 53
rng.Value = rng.Interior.ColorIndex
Case Colors!OptionButton7 = True
rng.Interior.ColorIndex = 42
rng.Font.ColorIndex = 1
rng.Value = rng.Interior.ColorIndex
Case Colors!OptionButton8 = True
rng.Interior.ColorIndex = 44
rng.Font.ColorIndex = 1
rng.Value = rng.Interior.ColorIndex
Case Colors!OptionButton9 = True
rng.Interior.ColorIndex = 47
rng.Font.ColorIndex = 2
rng.Value = rng.Interior.ColorIndex
End Select
 
Thanks Gary,

I updated the code and now have an excellent little add-in for my sheet.

The user-form is excellent and I can now adapt the code to other colors and
maybe more selections. I am thinking of making the userform for all 56 main
colors.

Would you have any advice on the code for doing this in an efficient maner?

Best regards and thanks for helping.

Roger
 
not really, that was something i just threw together. not all color combinations
are very good for either background or text, i just made up some examples. hope
you get some use out of it.
 
Thanks for all your help.

Ive been editing and using the code you gave me and have learned alot about
forms.

Best regards,

Roger
 
Back
Top