If that's the case, how do I add the variables to the collection? Or do I
have something else going on, i.e. the Select Case is not the right tool?

Thanks - Any help is appreciated - RobertC

Public strQuery As String
Public strQueryName As String
Public strQueryParameters As String
Public strWorksheet As String

'// Establish variables

Private Sub cboSelectQuery_Change()

    strQuery = cboSelectQuery.Text
    Select Case strQuery
        Case Is = "JC Budgets to Date"
            strQueryName = "JC Budgets to Date"
            strQueryParameters = "Enter Transaction Date: mm/dd/yy"
            strWorksheet = "JC Budgets"
        Case Is = "JC Costs to Date"
            strQueryName = "JC Costs to Date"
            strQueryParameters = "Enter Accounting Date: mm/dd/yy"
            strWorksheet = "JC Costs"
        Case Is = "JC Detail to Date"
            strQueryName = "JC Detail to Date"
            strQueryParameters = "Enter Accounting Date: mm/dd/yy"
            strWorksheet = "JC Detail"
    End Select

Private Sub cmdLoadDate_Click()
    Dim Db As Database
    Dim Qd As QueryDef
    Dim Rs As Recordset
    Dim Ws As Object
    Dim i As Integer
    Dim Path As String
    Dim sTransDate As String
    Dim nRet As Long

    '// evalInput will return success or failure, and if successful,
    '// set passed string to the user-selected date and pass-in date
variable.

    nRet = EvaluateInput(sTransDate)

    '// Unload form and exit if input not valid

    If nRet <> 0 Then

    '// Err msg displayed in EvaluateInput()
    '// Unload the form and exit
        Exit Sub
    Else
        Unload frmSelectVariables
    End If

    '// Set the Path to the database.
    Path = "k:\database\IPP.mdb"

    '// Set the Worksheet

    Set Ws = Sheets(strWorksheet) = strWorksheet

    '// More code here

    Set Ws = Sheets(strWorksheet) = strWorksheet

does not look right. What was it intended to do? It actually does a logical
test, which is nonsense, and tries to assign a True or False value to Ws.
Perhaps you meant:

    Set Ws = Sheets(strWorksheet)

However, the subscript out of range error indicates that the sheet specified
in strWorksheet does not exist in the active workbook. Perhaps you need to
qualify the reference with a workbook reference:

    Set Ws = Workbooks(strWorkbook).Sheets(strWorksheet)

--

John Green
Sydney
Australia

One of the most common errors to occur is when a user changes a
worksheet name.  If the sheet is originally called "Sheet1", ad you
have code that says:

Sheets("Sheet1").Select

Everything will work fine.  But if a user changes the name to
"Revenue" (or whatever), you'll get a subscript error.

If you look in the Project Explorer in the VBA Editor in a blank
workbook, you should see that the first worksheet object is described
as Sheet1(Sheet1).  If, in Excel, you change the name of the worksheet
to "Revenue" and then go back to the VBA Editor, you'll see that this
has changed to Sheet1(Revenue).  Revenue is the name that Excel refers
to the worksheet by, while Sheet1 is the actual worksheet object.

To avoid subscript errors then, code like this can be used:

    Dim TheSheet As Worksheet
    Set TheSheet=Sheet1
    Sheets(Sheet1.Name).Select

Regardless of the changes made to the worksheet name, you won't get a
subscript error, as it selects based on the value of the Name
property, rather than trying to find the specific string "Sheet1".  In
a blank workbook, Sheet1.Name will be "Sheet1".  When you change the
worksheet name to "Revenue", Sheet1.Name will equal "Revenue".

Hope this helps...

Clayton.