| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting color number to use in vba code.
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting color number to use in vba code.
Do you mean
..Interior.ColorIndex = Range("A1").value -- __________________________________ HTH Bob "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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting color number to use in vba code.
Thanks Bob - works perfectly,
Best regards, Roger |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting color number to use in vba code.
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting color number to use in vba code.
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting color number to use in vba code.
just curious if you tried it and if it worked for you.
-- Gary "Roger on Excel" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting color number to use in vba code.
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 "Gary Keramidas" wrote: just curious if you tried it and if it worked for you. -- Gary "Roger on Excel" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting color number to use in vba code.
try changing this line and it should set the color in A2.
Set rng = wks1.Range("A2:A10") to Set rng = wks1.Range("A2") -- Gary "Roger on Excel" wrote in message ... 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 "Gary Keramidas" wrote: just curious if you tried it and if it worked for you. -- Gary "Roger on Excel" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting color number to use in vba code.
Thanks Gary,
How would one paste the color number into the same cell - eg 3 for red? Thanks, Roger "Gary Keramidas" wrote: try changing this line and it should set the color in A2. Set rng = wks1.Range("A2:A10") to Set rng = wks1.Range("A2") -- Gary "Roger on Excel" wrote in message ... 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 "Gary Keramidas" wrote: just curious if you tried it and if it worked for you. -- Gary "Roger on Excel" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting color number to use in vba code.
forms use hex numbers for fore and back colors, not decimals. that's what's in
the 2 arrays. -- Gary "Roger on Excel" wrote in message ... Thanks Gary, How would one paste the color number into the same cell - eg 3 for red? Thanks, Roger "Gary Keramidas" wrote: try changing this line and it should set the color in A2. Set rng = wks1.Range("A2:A10") to Set rng = wks1.Range("A2") -- Gary "Roger on Excel" wrote in message ... 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 "Gary Keramidas" wrote: just curious if you tried it and if it worked for you. -- Gary "Roger on Excel" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting color number to use in vba code.
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 -- Gary "Roger on Excel" wrote in message ... Thanks Gary, How would one paste the color number into the same cell - eg 3 for red? Thanks, Roger "Gary Keramidas" wrote: try changing this line and it should set the color in A2. Set rng = wks1.Range("A2:A10") to Set rng = wks1.Range("A2") -- Gary "Roger on Excel" wrote in message ... 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 "Gary Keramidas" wrote: just curious if you tried it and if it worked for you. -- Gary "Roger on Excel" wrote in message ... 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting color number to use in vba code.
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 "Gary Keramidas" wrote: 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 -- Gary "Roger on Excel" wrote in message ... Thanks Gary, How would one paste the color number into the same cell - eg 3 for red? Thanks, Roger "Gary Keramidas" wrote: try changing this line and it should set the color in A2. Set rng = wks1.Range("A2:A10") to Set rng = wks1.Range("A2") -- Gary "Roger on Excel" wrote in message ... 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 "Gary Keramidas" wrote: just curious if you tried it and if it worked for you. -- Gary "Roger on Excel" wrote in message ... 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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting color number to use in vba code.
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. -- Gary "Roger on Excel" wrote in message ... 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 "Gary Keramidas" wrote: 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 -- Gary "Roger on Excel" wrote in message ... Thanks Gary, How would one paste the color number into the same cell - eg 3 for red? Thanks, Roger "Gary Keramidas" wrote: try changing this line and it should set the color in A2. Set rng = wks1.Range("A2:A10") to Set rng = wks1.Range("A2") -- Gary "Roger on Excel" wrote in message ... 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 "Gary Keramidas" wrote: just curious if you tried it and if it worked for you. -- Gary "Roger on Excel" wrote in message ... 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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting color number to use in vba code.
Thanks for all your help.
Ive been editing and using the code you gave me and have learned alot about forms. Best regards, Roger |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Selecting cells of a specific color only. | Excel Discussion (Misc queries) | |||
| Selecting no color for a graphed series | Charts and Charting in Excel | |||
| How to color automatically color code sums in cells | Charts and Charting in Excel | |||
| Selecting Background Color? | Excel Programming | |||
| Selecting cells by color? | Excel Programming | |||
Linear Mode
