How can I Pass an Array Argument On User Click?

[rabley]

I have a spreadsheet of product info including two columns – “Color” and “Color Family.” The Color column is for specific (sometimes odd) color names like “Starry Night.” The Color Family column is supposed to clarify that the Color actually is for search purposes (e.g. “Silver”).

I’m trying to write a form that would allow the user to quickly and easily fill in the Color Family column, rather than copying and pasting “Silver” every time “Starry Night” appears. My idea so far is to have a series of ComboBoxes and autopopulate them with entries from the Color column. The user could then use the dropdown menu to choose a Color Family.

I’d like to have an “Update Spreadsheet” button for when the user is done choosing Color Families, but I can’t figure out how to get the data from the “Choose Color Families” subroutine to an “Update Spreadsheet” one. I’ve been saving row information in an array, which I want to use to update the correct rows in the spreadsheet. I thought I would declare it as a public variable, but I’ve discovered that I cannot. How can I get the array from one subroutine to another on a user click? I’ve been reading about passing arrays as arguments, but they all seem to depend on calling the subroutines immediately.

Here is my (obviously non-working) test code:

Private Sub cmdChooseColorFamilies_Click()
        
        Dim intCBOCount As Integer
        Dim arrCBOCountArray(0 To 2) As Variant
        
‘ Offset 19 is the Color Family column, and 18 is the Color
       
 For intCBOCount = 0 To 2
            Do
                If IsEmpty(ActiveCell.Offset(0, 19)) = False Then
                     ActiveCell.Offset(1, 0).Select
                End If
            Loop Until IsEmpty(ActiveCell.Offset(0, 19)) = True
            
            frmTPMSColorFamily.Controls("cboColorFam" & intCBOCount).Value = ActiveCell.Offset(0, 18).Value
          
            arrCBOCountArray(intCBOCount) = ActiveCell.Row
            ActiveCell.Offset(1, 0).Select
        Next

End Sub
‘-------------------------------------------------------------------------------------
Private Sub cmdUpdateSpreadsheet_Click(arrCBOCountArray())

    Dim intM As Integer
    
    For intM = 0 To 2
        Cells(arrCBOCountArray(intM), 22).Value = frmTPMSColorFamily.Controls("cboColorFam" & intM).Value
    Next

End Sub

I haven’t yet worked on repeating this for every instance of “Starry Night”. All I’m trying to do so far is get it to work at all, but any suggestions along that direction would also be welcome!

 

[SkipVought]

By the way, you do not need 15 separate comboboxes.

You apparently need TWO...

1. Lists the Products for which you have colors

2. Lists the Colors for the Selected Product.

To support that approch, you would need a TABLE containing a column for Product and one for color. List the ProductID in one column and each of the colors for that ProductID, ie
[tt]
Product Color
Prod1 Silver
Prod1 Blue
Prod2 Red
Prod2 Yellow
Prod2 Blue
...
[/tt]
Then use the Data > get External Data > New database query... to generate a list of unique Products.

When the user selects a Program, run another parameter query to generate a list of colors for that Program. Thats the source for the second combo.

I use this technique often in Excel Charts for my users to select a Cost Center and then a list of Machine Resources, unique to that Cost center, in order to make interactive charts -- ONE CHART, with TWO COMBOS to display HUNDREDS of different Cost Center/Machine Resources. faq68-5829



Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]