UserForm with ComboBoxes to open different Word docs

[themikeford]

Hi again,

I am trying to develop a macro that will eliminate the need to sort through 400+ different Word docs of product labels.

I've created a userform with 4 combo boxes (cboSize, cboSubstrate, cboColor, cboFootage) and have populated the lists for each.

I'd like to have the macro open a particular word document based on the combo box selections.

For example:

If cboSize=05 and cboSubstrate=C and cboColor=A and cboFootage=100, then open xyz123.doc

I know huge if/then strings are bad, but that's kind of how it's turning out.

I've been stuck on this last step for a while now and really need a nudge in the right direction. I just need to program my Submit commandbutton to determine and open the appropriate Word doc, based on the information selected from the combo box options. I seriously appreciate any help I can get.

Thanks in advance,
Mike

[Kyle123]

I'd be awfully tempted to have the possible combinations in a column, then in the next column the file that that combination would open, for example. Then just concatenate the comboboxes and look up the result.

[themikeford]

I'm not sure I follow what you mean. I am open to virtually any idea, I'm just not a programmer and this project has taken me very far out of my element.

I was thinking of a series of if/and/and/and/then statements, I just don't know the appropriate syntax to make the code work and be minimally messy.

[themikeford]

Here is are some screencaps of what I'm working with. I have populated the combo box fields from the excel sheet. I basically just need one line of code that says:
If Size=xx and Subs=yy and Color=zz and Feet=110 then open xxyyzz.doc
If Size=aa and Subs=xy and Color=zz and Feet=110 then open aaxyzz.doc
So on and so forth, approximately 430 times.
SheetView1.JPGSheetView2.JPG

[themikeford]

Also, the Size combo box has two columns (a size and a code). Does that affect the value selected in terms of syntax?

[Kyle123]

Consider the following (bear in mind I have seen none of your code so I'm making some assumptions):

1. All the combinations with the files they are supposed to open in sheet1.

Column A	Column B
041/4KnitRIO50	D:\My Documents\Doc1.doc
041/4KnitRIO100	D:\My Documents\Doc2.doc
041/4KnitRIONonstandard	D:\My Documents\Doc3.doc

2. In the Form

Sub aButton_Click()

    Dim strSearch As String
    
    strSearch = Size.Value & Subs.Value & Color.Value & feet.Value
    
    MsgBox (Sheets("Sheet1").Columns(1).Find(strSearch).Offset(, 1).Value)
    
End Sub

Does this help?