Selecting color number to use in vba code.

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
 
G

Gary Keramidas

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
 
R

Roger on Excel

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
 
R

Roger on Excel

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
 
G

Gary Keramidas

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

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

to

Set rng = wks1.Range("A2")
 
R

Roger on Excel

Thanks Gary,

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

Thanks,

Roger
 
G

Gary Keramidas

forms use hex numbers for fore and back colors, not decimals. that's what's in
the 2 arrays.
 
G

Gary Keramidas

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
 
R

Roger on Excel

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
 
G

Gary Keramidas

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.
 
R

Roger on Excel

Thanks for all your help.

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

Best regards,

Roger
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can Vba workout if a selection is part of a group? 0
VBA Conditional formating for specific text in several columns 1
Enter Value based on Cell Color 1
Setting Colors Using Other Than ColorIndex? 2
Remove Hyperlinks Without Changing background shading 14
Run select query from VBA code where criteria comes from a multi select ListBox 2
Excel VBA 1
Using VBA coding to count color Occurrences 4

Top