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]

Hi,

Did you know that this could probably all be done with sheet functions?

1. regardless of sheet or VBA, I'd use Named Ranges.

If Color is in A2, for instance
[tt]
B2: =index(Color_Family,Match(A2,Color,0),1)
[/tt]

Sheets("YourSheetName").Range("B2").value = application.index(range("Color_Family"), application.match(Sheets("YourSheetName").Range("A2").value,range("Color").0),1)

whichever you choose to do.

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]

 

[rabley]

I'm sorry, I don't quite follow. Where should I put the code you gave? I tried it in "updateSpreadsheet", but I get a runtime error 1004: Method 'Range' of object '_Global' failed.

I'm trying to do as much as possible with VBA because a lot of different users will be using this with a lot of different spreadsheets. I don't want to have to rely on them to copy and paste formulas in the right places, so I thought just having a button for them to click would be easier.

 

[SkipVought]

First, you only need ONE combo.

You make it VISIBLE and POSITION & SIZE it according to the user's cell selection, using the Worksheet_SelectionChange event. For instance, if the column that you want the color family in is C (column 3)...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  if target.column <> 3 then exit sub
  with yourcombo
     .visible = true
     .top = target.top
     .left = target.left
     .width = target.width
     .height = target.height
  end with
End Sub

When the user makes a selection in the combo, then you make to control's visible property FALSE, and use the selected value to calculate the value that should go in the selected cell. In this case the selected value (the color) is used to determine the Color Family from the lookup list. That's the code I posted above...

sub yourcombo_Change()
  with yourcombo
     .topleftcell.value = application.index(range("Color_Family"), application.match(.text,range("Color").0),1)
     .visible = false
  end with
end sub

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]

 

[rabley]

I really appreciate you walking me through this. I'm getting a syntax error on the line that begins "topleftcellvalue".

Do I replace "Color_Family" with the column letter of my Color_Family column (which is V)? Or do I make a second sheet in the workbook with all the Color_Family choices and name that range "Color_Family"? ("Color" is column U in my worksheet).

 

[SkipVought]

Please post your code.

This assumes a control has been placed on your SHEET.

Have you done this?

What is the name of your control?

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]

 

[rabley]

Oh, no, I misunderstood you. I had made a ComboBox on a userform like the other form I have for this spreadsheet.

I'm afraid that if I add something to the sheet itself, it will complicate things. I'm going to have a dozen or so people using this data entry form for many different product lines, each of which gets its own workbook. I was hoping to create a macro in a workbook that they could keep open in one window and access every time they created a new workbook. Adding a control to the sheet in the original workbook wouldn't carry over into the new workbook, would it?

All I really wanted to make was a userform that shows the user the Color, asks what the Color Family should be, and fills in that answer everywhere that Color appears on the sheet.

Again, thank you for your time.

 

[SkipVought]

" I had made a ComboBox on a userform like the other form I have for this spreadsheet."

IMHO, whenever you separate the user from the sheet, it creates a level of difficulty and frustration for the user. I'd keep the user as close to the sheet as possible.

"Adding a control to the sheet in the original workbook wouldn't carry over into the new workbook, would it?"

Yes, it would.

"I'm going to have a dozen or so people using this data entry form for many different product lines, each of which gets its own workbook."

Really!? Is this for a social club or for a company that expects to make a profit? I assume the latter. I would STRONGLY recommend that this approch be reeveluated. I see glaring problems...

1) similar data in multiple locations. This is NOT a best and acceptable way to store and use valuable corporate data. Storing similar data in multiple sheets, files, folders (WHATEVER) makes it about a useful as paper in a file drawer. It renders it useless to global analysis and reporting, which is what a computer is good at.

2) Excel, as much as I am a strong advocate for this excellent spreadsheet tool, is NOT a database, ESPECIALLY for a production firm, as I am sure yours is.

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]

 

[rabley]

Oh, *believe me*, you are preaching to the choir. I cannot tell you how many times I have made this argument to TPTB. I am but a lowly intern, assigned to do data entry. It was my idea to have a data entry form at all, because I refused to waste so much time and effort doing things the old way (i.e., copying and pasting repetitive information individually into each cell, with no hint of concatenation). They’ve at least been convinced to have a single workbook for each product line (until now, they’ve created a new workbook each time new products were added or old products were updated).

I advocated for an actual database, but each of our suppliers fills in the spreadsheets on his/her own and sends them to us, and the Boss wants to stick with what he knows, which is Excel.

So, yes, I’m doing what I can with my hands tied. My goal with the new format of the spreadsheet and the userform is to standardize things enough so that the day someone finally convinces them to use an actual database, they can export the information more easily. No more storing color, finish, and size in the same cell!

 

[SkipVought]

So your users never enter data directly into a sheet; rather into the form. Okay!

Why not, as the user selects the Color from a combo on your form, use that value to lookup the ColorFamily value and use that value directly when you update the sheet. Then its just a part of the data entry process.



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]

 

[rabley]

That is how I originally programmed it, but the Boss would like the Color Family to be determined only by two approved employees, for consistency. One man's Light Brown is another man's Medium Brown, and all that. The suppliers will use the main data entry form and send it to us. Our two employees will use the Color Family form to make those changes (this form will also autopopulate a few other columns with Search Categories).

 

[SkipVought]

I'm reviewing this thread in light of the known facts as revealed.

This seems strange to me. Please clarify...

"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."

So far, I seem to understand; the user fills in the Color Family value based on the Color, doing this from a dropdown.

"My idea so far is to have a series of ComboBoxes

This is where I loose the track. What series of combos? Is there not ONE combo for selecting a COLOR and returning a ColorFamily?

" and autopopulate them with entries from the Color column. The user could then use the dropdown menu to choose a Color Family."

Now you are saying that the user does not do this. Only TWO people do this. But they are doing the selection of a Color based on WHAT?

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]

 

[rabley]

Now you are saying that the user does not do this. Only TWO people do this. But they are doing the selection of a Color based on WHAT? "

My original question was referring to our 2 employees when it said "users". Suppliers as "users" didn't seem important at the time; I'm sorry if it was confusing. Here is what happens:

1) Supplier uses Form #1 to enter product information into the spreadsheet, including Color (e.g., "Spruce"). The Color Family column is left blank. I have this form working just fine now.

2) Our 2 employees look at a sample of "Spruce"-colored product and realize that it actually means "Light Green", so they use theoretical Form #2 to enter "Light Green" into the Color Family column wherever the Color column says "Spruce". Form #2 will also use "Light Green" to automatically create certain search categories and product subtitles, without further input from our employees.


We have thousands of products from different suppliers, so I can't just make a big table that says, "Spruce = Light Green", "Starry Night = Silver", etc. There are too many colors, and some suppliers use them to mean different shades. We need a human step in the process to make those determinations.

Theoretically, I could make our employees sort the spreadsheets by color and then copy and paste their entries in the Color Family column, and I could then write Excel formulas for the Search Category/title columns, but I'd rather make things a simple as possible for our employees.

My idea was to have a userform with a series of comboboxes (a series so that our employees can look at several Colors at a time, and update the spreadsheet only at the end when they were ready). There would be one combobox for each unique Color in the spreadsheet.

 

[SkipVought]

Then it sould be relatively simple, using Data > Validation - LIST. Apply the data validation to the column that the TWO users will assign color family. They just pick from the list.

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]

 

[rabley]

They'd still have to do it repeatedly every time the same color came up. I'm hoping to avoid that.

 

[SkipVought]

OK.

Then what doe this mean?

"There would be one combobox for each unique Color in the spreadsheet."

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]

 

[rabley]

Let's say the spreadsheet has 6 products, and their color column looks like this -

Color:
Spruce
Moondust
Starry Night
Spruce
Pink
Starry Night


My form would have 15 or so comboboxes, empty as a default, with the list of Color Family choices waiting in the dropdowns. I would fill in the first four of them with the following values:

Spruce
Moondust
Starry Night
Pink


Once our two employees choose a Color Family from the dropdown for each color (so, 4 times in this example) and hit Update Spreadsheet, the code would look for every instance of each of the 4 colors and fill in the Color Family chosen. So, it would fill in "Light Green" in the 1st and 4th rows, because the "Spruce" combobox value was turned into "Light Green" by our employees.

Is that any clearer? It's only my draft of an idea, so if you have a better strategy, I'm definitely open to it.

 

[SkipVought]

"My form would have 15 or so comboboxes, empty as a default, with the list of Color Family choices waiting in the dropdowns."

Are you using ComboBox and DropDown interchangeably or do they mean something different?

Why 15 comboboxes?

As I understand it, the colors on the sheet must be interpreted by experienced personnel to MAP TO your company's available colors (color family).

So the person my make an association for a customer (workbook) ONCE, like Starry Night (the customer's color) means Silver (your company's color). Am I correct so far?

Having made that association, the entire sheet can be UPDATED with Silver in the cell adjacent to each occurrence of Starry Night. Then its DONE! At least for THAT color.

So maybe you have a Listbox that lists all the customer's colors ONE TIME.

The user selects a line in the listbox and then a color family in the combobox and hits GO! The sheet is updated as described above and the item is removed from the list of customer colors.

Am I on track?

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]

 

[rabley]

Are you using ComboBox and DropDown interchangeably or do they mean something different?"

I just meant that a dropdown is part of a ComboBox. When you first see the ComboBox, there is nothing in the field, but you can see the limited choices available once you click on it.

"Why 15 comboboxes?"

I chose 15 because most product lines don't have more than 15 colors.

"Am I on track? "

Yes! Yay! That's just what I would like to do ... how do I do it? I can see how I'd initially populate the Color ListBox, but not how I would then connect the selections to ones made in the Color Family ListBox, and then back to the spreadsheet.

 

[rabley]

Hey, I think I've come up with something that works. I'll post the code below in case someone stumbles upon this later. Thank you for trying to help me, though. I appreciate your time.

Private Sub UserForm_Initialize()

    ActiveWorkbook.Sheets("Sheet1").Activate


    'Look for the first empty cell in the "Color Family" Column, starting at Row 3 to make room for the button
    Range("C3").Select

    Do
        If IsEmpty(ActiveCell.Offset(0, 19)) = False Then
            ActiveCell.Offset(1, 0).Select
        End If
    Loop Until IsEmpty(ActiveCell.Offset(0, 19)) = True
    
    With cboColorFam
        .AddItem "Light Blue"
        .AddItem "Green"
        .AddItem "Dark Red"
        .AddItem "White"
        .AddItem "Black"
        
    End With
    
    populateComboBox
    
End Sub

Private Sub populateComboBox()

    cboColorFam.Value = ActiveCell.Offset(0, 18).Value

End Sub


Private Sub cboColorFam_Click()

    Dim strCurrentColor As String
    strCurrentColor = ActiveCell.Offset(0, 18).Value
    
    populateSpreadsheet
    
    Range("C3").Select ' start at the top again

    Do
        Do
            If IsEmpty(ActiveCell.Offset(0, 19)) = False Then
                ActiveCell.Offset(1, 0).Select
            End If
        Loop Until IsEmpty(ActiveCell.Offset(0, 19)) = True ' find the next empty Color Family cell
        
        If ActiveCell.Offset(0, 18) = strCurrentColor Then
            populateSpreadsheet
        End If
        ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell)
    
    Range("C3").Select ' start at the top again
    
    Do
        If IsEmpty(ActiveCell.Offset(0, 19)) = False Then
            ActiveCell.Offset(1, 0).Select
        End If
    Loop Until IsEmpty(ActiveCell.Offset(0, 19)) = True ' find the next empty Color Family cell
    
    populateComboBox

End Sub

Private Sub populateSpreadsheet()
    Dim arrBaseColor() As String
    Dim strColorForSearch As String
    
    ActiveCell.Offset(0, 19).Value = cboColorFam
    
    If InStr(1, cboColorFam, " ", vbTextCompare) > 0 Then
        arrBaseColor = Split(cboColorFam, " ", -1, 1)
        strColorForSearch = arrBaseColor(1)
    Else
        strColorForSearch = cboColorFam
    End If
    
    ActiveCell.Offset(0, 101).Value = "Colors///" & strColorForSearch & "///" & ActiveCell.Offset(0, 6).Value & "s"
    ActiveCell.Offset(0, 102).Value = "Colors///" & cboColorFam & "///" & ActiveCell.Offset(0, 6).Value & "s"

    If ActiveCell.Offset(0, 34).Value = "Y" Or ActiveCell.Offset(0, 34).Value = "y" Then
        ActiveCell.Offset(0, 103).Value = "Colors///Metallic"
        ActiveCell.Offset(0, 104).Value = "Colors///Metallic " & ActiveCell.Offset(0, 6).Value & "s"
    End If

End Sub