| 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 |
| 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 | |||
Hybrid Mode
