Passing dynamic useform name

[dweben]

Hi guys,

I've a program which creates a list of employee names and deposits this in a variety of forms as they're activated. I don't want to create duplicate code by stating a fixed name in the code, but would rather pass the required userform name to the sub and off it goes...

Currently I click on the 'Generate UserForm button' which then runs the below code

Sub OpenFormShiftChanges()

    Dim UserFormName As Object

    Call mdl05_Forms.ShiftChangeOptionsArray
    Set UserFormName = "fmShiftChanges"
    Call mdl05_Forms.BuildArrayOfEmployees(UserFormName, "cmbShiftChangeEmployee")
    fmShiftChanges.Show vbModeless

End Sub

The mdl05_Forms.BuildArrayOfEmployees is another procedure which does the actual list creation and depositing of data into the userform combobox, as below.

Sub BuildArrayOfEmployees(Form As Object, ComboBox As String)

    Call mdl03_Routines.FindHeaderRow
    lLastDataRow = FindLastDataRow(iCostCentreCol)

    For lRow = iHeaderRow + 1 To lLastDataRow
        UserForms.Add(Form).Controls(ComboBox).AddItem _
            ThisWorkbook.ActiveSheet.Cells(lRow, iEmployeeCol) & " (" & ThisWorkbook.ActiveSheet.Cells(lRow, iStaffNumberCol) & ")"
    Next lRow

End Sub

I'm getting a mismatch on the Set UserFormName in the first procedure. I've tried all sorts of combinations of setting variables as Objects, Userforms and Strings, not setting the UserFormName, etc, and I just can't seem to get the UserFormName to pass to the BuildArray procedure.

I can't quite see where I'm going wrong -- would appreciate any pointers please.

Cheers, Paul

[JBeaucaire]

Looks like you're really using that as a string, not an object.

Dim UserFormName As String

[dweben]

I've tried setting all the variables as a String, and while it does pass the string to the second procedure when it gets to the Userforms.Add(Form).Controls bit it just doesn't do the action. Perhaps that line is just written wrong, somehow. I know the additem does work as it works when I put the hard coded version in (fmShiftChanges.cmbShiftChangeEmployee.additem) it creates the list and puts it into the combobox.

Ta, Paul

[Leith Ross]

Hello dweben,

You can get the most recently created UserForm from the VBA USerFoms Collection.

Sub OpenFormShiftChanges()

    Dim UserFormName As Object

    Call mdl05_Forms.ShiftChangeOptionsArray
    
    Set UserFormName = UserForms(UserForms.Count - 1)
    Call mdl05_Forms.BuildArrayOfEmployees(UserFormName, "cmbShiftChangeEmployee")
    fmShiftChanges.Show vbModeless

End Sub