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 Keramidas" wrote:

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



--


Gary


"Roger on Excel" wrote in message
...
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