+ Reply to Thread
Results 1 to 5 of 5

passing variables to subroutine from userform

Hybrid View

PD3 passing variables to... 06-07-2013, 12:27 AM
Norie Re: passing variables to... 06-07-2013, 12:39 AM
PD3 Re: passing variables to... 06-07-2013, 01:28 AM
Norie Re: passing variables to... 06-07-2013, 01:35 AM
PD3 Re: passing variables to... 06-07-2013, 02:11 AM
  1. #1
    Registered User
    Join Date
    05-17-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    passing variables to subroutine from userform

    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

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: passing variables to subroutine from userform

    What's the problem?

    The code looks fine to me, the only thing I would suggest is to not use the combobox's enter event to populate it.

    Actually just notice something else, you don't need to pass the userform to the sub - passing the combobox on it's own will work.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    05-17-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: passing variables to subroutine from userform

    Hi Norie,
    Thanks for getting back, When stepping through the calling routine it hits the call statement at this line in the code and comes up with an error 13 - as below.

    'call subroutine to load variables
    mod_ListboxTools.ComboLoadList shtName, rngName, cboName, ufrmName

    7-06-2013 3-20-11 PM.jpg

    I've altered the called routine first line but still have the problem as described.
    Sub ComboLoadList(shtName As String, rngName As String, cboName As Object, frmName As Object)
    
    Dim Cell As Range

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: passing variables to subroutine from userform

    I think this is the problem, and it's the part I suggested isn't needed.
    frmName As form
    If you did want to pass the userform you could pass it as an Object as you have the combobox.

  5. #5
    Registered User
    Join Date
    05-17-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: passing variables to subroutine from userform

    Hi Norie,
    Mea Culpa!
    (crap! - spent a long time trouble shooting a problem that was the code but not the code)

    I had the same called routine in the userform code area - effectively repeating the code in two places. During trouble shooting I copied this into the same area to compare the code/s. It all runs now and looks like the following. Thanks for your assistance.

    Option Explicit
     Option Base 1
    '***********************************************************************
    '*  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 ComboBox
    Dim ufrmName As UserForm
    
    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 sub() as below

    '***********************************************************************
    '*  Subroutine called from a form to generate a unique list
    '*  of entries from a column of data 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 ComboBox, frmname As UserForm)
    
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1