Hi, I'm relatively new to VBA excel programming and have gotten stuck on this problem. I have a sub procedure that is intended to load a list of values into a combobox; I wish to call this from a userform. The call from the user form needs to nominate/pass these variables so the called subroutine can identify the range for the values. The called subroutine is located in a separate module called "mod_ListboxTools".
Although I've spent some time looking (and experimenting) for what may be at issue my lack of VBA programming concepts is most probably bring me unstuck.
Any assistance appreciated.
Calling sub() located in a Userform
'***********************************************************************
'* Load a unique list of entries into a form's list/combo box list.
'*
'* inputs
'* cboName = "cbo_pdtWghtForm"
'* shtName = worksheet holding input range i.e. "Table 3.3A"
'* rngName = input range for combo box i.e. "Wrought Form"
'*
'***********************************************************************
Private Sub cbo_pdtWghtForm_Enter()
Dim shtName As String
Dim rngName As String
Dim cboName As Object
Dim ufrmName As Object
Set ufrmName = ufrm_1MechProp
Set cboName = cbo_pdtWghtForm
shtName = "Entry_Arrays"
rngName = "PdtForm_List"
'call subroutine to load vaiables
mod_ListboxTools.ComboLoadList shtName, rngName, cboName, ufrmName
End Sub
Called subroutine that is located in a module called "mod_ListboxTools"
'***********************************************************************
'* Subroutine called from a userform to load a unique list
'* of entries from a worksheet range into a combobox list.
'* Uses a loop to step through each cell in a Worksheet.Range
'* and adds each range value to a list/combo box list.
'* If a range value is blank, it is not added.
'*
'* inputs
'* frmName = form name
'* cboName = combobox to load list with i.e. "cbo_pdtWghtForm"
'* shtName = worksheet holding input range i.e. "Table 3.3A"
'* rngName = input range for combo box i.e. "Wrought Form"
'*
'***********************************************************************
Sub ComboLoadList(shtName As String, rngName As String, cboName As Object, frmName As form)
Dim Cell As Range
' clear the contents of the combo/listbox
' set combo/listbox prompt value
' load values from range into combobox "cbo_pdtWghtForm"
' select only non-empty values
With frmName
cboName.Clear
cboName.Value = "Select Product Form"
For Each Cell In Worksheets(shtName).Range(rngName)
If IsEmpty(Cell) = False Then
Cell = Trim(Cell)
cboName.AddItem Cell.Value
Else:
End If
Next Cell
End With
End Sub
Bookmarks