Consulting

Results 1 to 4 of 4

Thread: Excel VBA Userform Comboboxes not populating with external data range

  1. #1

    Excel VBA Userform Comboboxes not populating with external data range

    I have a Userform with 2 Comboboxes on that should get the data ranges from an external workbook. I'm stepping through the code with F8 to see the process, everything runs without errors but the ComboBoxes are empty when the userform shows.
    The named ranges are set when I check it in the Watch window. I'm not sure if the problem comes in with the userform_initialize when I assign the named ranges to the combo boxes?
    Any suggestions will be appreciated.

    
    
    Option Explicit
    Public m_Cancelled As Boolean
    Public Const myRangeNameVendor As String = "myNamedRangeDynamicVendor"
    Public Const myRangeNameVendorCode As String = "myNamedRangeDynamicVendorCode"
    Public VendorName As String
    Public VendorCode As String
    
    
    
    
    
    
    Sub NamedRanges(wb As Workbook, wSh As Worksheet)
    
    
        'declare variables to hold row and column numbers that define named cell range (dynamic)
        Dim myFirstRow As Long
        Dim myLastRow As Long
    
    
        'declare object variable to hold reference to cell range
        Dim myNamedRangeDynamicVendor As Range
        Dim myNamedRangeDynamicVendorCode As Range
    
    
    
    
        'identify first row of cell range
        myFirstRow = 2
    
    
    
    
        'Vendor Name range
        With wSh.Cells
    
    
            'find last row of source data cell range
            myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    
            'specify cell range
            Set myNamedRangeDynamicVendor = .Range(.Cells(myFirstRow, "A:A"), .Cells(myLastRow, "A:A"))
    
    
        End With
    
    
        'create named range with workbook scope. Defined name is as specified. Cell range is as identified, with the last row being dynamically determined
        wb.Names.Add Name:=myRangeNameVendor, RefersTo:=myNamedRangeDynamicVendor
    
    
        'Vendor Code range
        With wSh.Cells
    
    
            'specify cell range
            Set myNamedRangeDynamicVendorCode = .Range(.Cells(myFirstRow, "B:B"), .Cells(myLastRow, "B:B"))
    
    
        End With
    
    
        'create named range with workbook scope. Defined name is as specified. Cell range is as identified, with the last row being dynamically determined
        wb.Names.Add Name:=myRangeNameVendorCode, RefersTo:=myNamedRangeDynamicVendorCode
    
    
    End Sub
    
    
    
    
    ' Returns the textbox value to the calling procedure
    Public Property Get Vendor() As String
        VendorName = cboxVendorName.Value
        VendorCode = cboxVendorCode.Value
    End Property
    
    
    Private Sub buttonCancel_Click()
        ' Hide the Userform and set cancelled to true
        Hide
        m_Cancelled = True
    End Sub
    
    
    ' Hide the UserForm when the user click Ok
    Private Sub buttonOk_Click()
        Hide
    End Sub
    
    
    ' Handle user clicking on the X button
    Private Sub FrmVendor_QueryClose(Cancel As Integer, CloseMode As Integer)
    
    
        ' Prevent the form being unloaded
        If CloseMode = vbFormControlMenu Then Cancel = True
    
    
        ' Hide the Userform and set cancelled to true
        Hide
        m_Cancelled = True
    
    
    End Sub
    
    
    
    
    Private Sub FrmVendor_Initialize()
    
    
    'add column of data from spreadsheet to your userform ComboBox
    With Me
        cboxVendorName.RowSource = ThisWorkbook.Names(Split(.Tag, "|")(0)).Address(external:=True)
        cboxVendorCode.RowSource = ThisWorkbook.Names(Split(.Tag, "|")(1)).Address(external:=True)
    
    
    End With
    cboxVendorCode.ColumnCount = 2
    
    
    End Sub
    
    
    
    
    Sub Macro5()
    
    
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim path As String
    Dim MainWB As Workbook
    Dim MasterFile As String
    Dim MasterFileF As String
    
    
    Set ws = Application.ActiveSheet
    Set MainWB = Application.ActiveWorkbook
    
    
    Application.ScreenUpdating = False
    
    
    'Get folder path
    path = GetFolder()
    
    
    MasterFile = Dir(path & "\*Master data*.xls*")
    MasterFileF = path & "\" & MasterFile
    
    
    'Check if workbook open if not open it
    If Not wbOpen(MasterFile, wb) Then
        Set wb = Workbooks.Open(MasterFileF, False, True)
    End If
    
    
    
    
    'Set Vendor Name and Code Range names
    Call NamedRanges(wb, wSh)
    
    
    
    
    'Select Vendor name and Vendor code with User Form and set variables
    
    
        ' Display the UserForm
    With New FrmVendor
        .Tag = myRangeNameVendor & "|" & myRangeNameVendorCode
        .Show
    End With
    
    
        VendorName = FrmVendor.cboxVendorName.Value
        VendorCode = FrmVendor.cboxVendorCode.Value
    
    
    
    
        ' Clean up
        Unload FrmVendor
        Set FrmVendor = Nothing


  2. #2
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Procedures FrmVendor_Initialize and FrmVendor_QueryClose have incorrect names. If they are to be events, they must have a specific name: Userform_Initialize and Userform_QueryClose.

    Artik

  3. #3
    Thanks Artik, I had to make a couple more changes. I received an answer on a different website. Link and explanation to follow.

  4. #4

    ANSWER to: Excel VBA Userform Comboboxes not populating with external data range

    I received an answer on a different website. Link https://stackoverflow.com/questions/...09480_61077956


    To sum up the problem:

    • I had to move the FrmVendor_Initialize procedure the the UserForm code (I actuall removed the intitialize procedure all together)
      Main thing was to change the .tag that sets the address for the comboboxes
      Had to move the variables to get the info from the comboboxes into the New FrmVendor code above it
      I moved all the sub procedures connected to the userform into the Userform code (I know it's not neccessary but made things easier for my code)




    Main code that needed to change:
    ' Display the UserForm
    With New FrmVendor
        .cboxVendorName.RowSource = wb.Names(myRangeNameVendor).RefersToRange.Address(external:=True)
        .cboxVendorCode.RowSource = wb.Names(myRangeNameVendorCode).RefersToRange.Address(external:=True)
        .Show
        VendorName = .cboxVendorName.Value
        VendorCode = .cboxVendorCode.Value
    End With

Tags for this Thread

Posting Permissions

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