SamT
03-30-2010, 01:33 PM
I'm writing a KB entry titled "Excel and 16 Million Colors." It will show manipulating Cell Colors and allow the user to view and select certain colors to be used as Constants in their procedures. It will illustrate the differences and relationships between "Range.Interior.ColorIndex," "Range.Interior.Color," and Hex Coded color values used in HTML. It will also show the use of the RGB() Function and how it develops its output, how to reverse the RGB process and how to duplicate its functionality with Hex Values.
The first sheet in the Workbook is "Instructions" and is Activated on Workbook_Open. Another sheet is "Template." The instructions will direct the User to copy Sheet("Template") as often as needed and place certain values in Range("A3:B56"). My Code already sucessfully manipulates those values.
The areas I am having trouble with are the Selection process and the Write-to-Module process.
I have the code to develop the Variables holding the Constant Strings that will be written to the modules. The names of the three modules are "Hex_Constants," "Value_Constants," and "Index_Constants." The three Strings will be "HexString," "IndexString," and "ValueString."
I envision a "Start Selecting" and a "Done Selecting" CommandButton on Sheet("Instructions"). After the User clicks "Start Selecting" he/she can browse thru the worksheets and Multiselect various Rows between Row(3) and Row(56) inclusive.
PseudoCode for Selection Process
Dim ar_Rows_Selected(2,54) As Strings where the _
Strings in Col("A") and Col("B") are stored.
Dim Reserved as Enum or collection of worksheets. (Hardcoded)
Dim SelectionsCount as Long
Until "Done_Selecting" (See below)
While SelectionsCount <= 54
If ActiveSheet NOT Reserved Then
Allow MultiSelect in the Rows 3 to 56 only
Increment SelectionsCount by num rows selected
End If
Allow user to activate any other sheet.
If SelectionCount = 54 then
Force Click cmdbut_Done_Selecting (see below)
MsgBox("Warn User")
Wend
Loop 'Until
When the User clicks on the "Done_Selecting" button on Sheet("Instructions"), he/she must be offered the chance, (by UserForm?) to delete any selections from ar_Rows_Selected by reviewing and deselecting from the Col("A") String value. They don't need to see the Col("B") value.
After the User has reviewed their selections, they need to be offered the chance to add to their selections, if fewer than 54. When they are sure, they will click the Command Button "Save New Color Constants."
Then, I will process the Col("A") and Col("B") values and feed another array, ("ar_All_Constants") with the computed values of "HexString," "IndexString," and "ValueString."
Finally:
I will fill ar_Color_Values() As String with values computed from Col("B").
Then, I need to
For i = 1 To Ubound(ar_ColorValues)
MyString = ".Colors(" & i + 2 & ") = " & ar_Color_Values(i) & Chr(13)
'Write MyString to VBA Code below. Should be in Modules("Run_Me_Once"):
Next 1
Option Base 1
Sub Customize_Palette()
With ThisWorkbook
'[Write Above strings here]
'.Colors(3) = 11823615' Examples of Lines to write
'.Colors(4) = 9639167
'.Colors(5) = 13353215
End With
End Sub
I have to Clear the modules "Hex_Constants," "Value_Constants," and "Index_Constants" and write the values in the 3 column array "ar_All_Constants" to the respective modules. For this, I'm totally lost.
Here are examples of the strings I, will be generating and You will be writing:
ValueString = "Constant Hot_Pink As Long = 1823615"
IndexString = "Constant Hot_Pink_Index As Variant = 3"
HexString = "Constant Hot_Pink_Hex As String = "&HFFFFFF""
The User will manually copy/import all relevant modules to their workbook and run Sub Customize_Palette. Then they can use code like:
MyRange.Interior.Color = Hot_Pink
Selection.Row.Interior.ColorIndex = Hot_Pink_Index
With UserForm1.Controls
BackColor = Hot_Pink
My_HTML_String = "<Body BGColor="" & Hot_Pink_Hex & ">""
The user will deal with the copies of Sheet("Template").
Thanks,
SamT
PS: if the best way to allow the User to deselect is a UserForm, AND, you use individual labels or checkboxes, their BackColor's can be set = to the values in Col("B"), which will show them the color they are deselecting. st.
The first sheet in the Workbook is "Instructions" and is Activated on Workbook_Open. Another sheet is "Template." The instructions will direct the User to copy Sheet("Template") as often as needed and place certain values in Range("A3:B56"). My Code already sucessfully manipulates those values.
The areas I am having trouble with are the Selection process and the Write-to-Module process.
I have the code to develop the Variables holding the Constant Strings that will be written to the modules. The names of the three modules are "Hex_Constants," "Value_Constants," and "Index_Constants." The three Strings will be "HexString," "IndexString," and "ValueString."
I envision a "Start Selecting" and a "Done Selecting" CommandButton on Sheet("Instructions"). After the User clicks "Start Selecting" he/she can browse thru the worksheets and Multiselect various Rows between Row(3) and Row(56) inclusive.
PseudoCode for Selection Process
Dim ar_Rows_Selected(2,54) As Strings where the _
Strings in Col("A") and Col("B") are stored.
Dim Reserved as Enum or collection of worksheets. (Hardcoded)
Dim SelectionsCount as Long
Until "Done_Selecting" (See below)
While SelectionsCount <= 54
If ActiveSheet NOT Reserved Then
Allow MultiSelect in the Rows 3 to 56 only
Increment SelectionsCount by num rows selected
End If
Allow user to activate any other sheet.
If SelectionCount = 54 then
Force Click cmdbut_Done_Selecting (see below)
MsgBox("Warn User")
Wend
Loop 'Until
When the User clicks on the "Done_Selecting" button on Sheet("Instructions"), he/she must be offered the chance, (by UserForm?) to delete any selections from ar_Rows_Selected by reviewing and deselecting from the Col("A") String value. They don't need to see the Col("B") value.
After the User has reviewed their selections, they need to be offered the chance to add to their selections, if fewer than 54. When they are sure, they will click the Command Button "Save New Color Constants."
Then, I will process the Col("A") and Col("B") values and feed another array, ("ar_All_Constants") with the computed values of "HexString," "IndexString," and "ValueString."
Finally:
I will fill ar_Color_Values() As String with values computed from Col("B").
Then, I need to
For i = 1 To Ubound(ar_ColorValues)
MyString = ".Colors(" & i + 2 & ") = " & ar_Color_Values(i) & Chr(13)
'Write MyString to VBA Code below. Should be in Modules("Run_Me_Once"):
Next 1
Option Base 1
Sub Customize_Palette()
With ThisWorkbook
'[Write Above strings here]
'.Colors(3) = 11823615' Examples of Lines to write
'.Colors(4) = 9639167
'.Colors(5) = 13353215
End With
End Sub
I have to Clear the modules "Hex_Constants," "Value_Constants," and "Index_Constants" and write the values in the 3 column array "ar_All_Constants" to the respective modules. For this, I'm totally lost.
Here are examples of the strings I, will be generating and You will be writing:
ValueString = "Constant Hot_Pink As Long = 1823615"
IndexString = "Constant Hot_Pink_Index As Variant = 3"
HexString = "Constant Hot_Pink_Hex As String = "&HFFFFFF""
The User will manually copy/import all relevant modules to their workbook and run Sub Customize_Palette. Then they can use code like:
MyRange.Interior.Color = Hot_Pink
Selection.Row.Interior.ColorIndex = Hot_Pink_Index
With UserForm1.Controls
BackColor = Hot_Pink
My_HTML_String = "<Body BGColor="" & Hot_Pink_Hex & ">""
The user will deal with the copies of Sheet("Template").
Thanks,
SamT
PS: if the best way to allow the User to deselect is a UserForm, AND, you use individual labels or checkboxes, their BackColor's can be set = to the values in Col("B"), which will show them the color they are deselecting. st.