用户窗体组合框未填充外部数据范围

问题描述

我有一个带有2个组合框的用户窗体，该窗体应从外部工作簿获取数据范围.我正在使用F8逐步执行代码以查看该过程，所有内容运行都没有错误，但是在用户窗体显示时ComboBox是空的.

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.

当我在监视"窗口中对其进行检查时，已设置并可以使用命名范围.我不确定在将命名范围设置为组合框时 userform_initialize 是否会带来问题?

The named ranges are set and works when I check it in the Watch window.I'm not sure if the problem comes in with the userform_initialize when I set the named ranges to the combo boxes?

任何建议将不胜感激.

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

推荐答案

FrmVendor_Initialize()子句由标记之前的 With New FrmVendor 行触发.放.将代码移到新的子

The FrmVendor_Initialize() sub is triggered by the line With New FrmVendor which is before the tag is set. Move the code into a new sub

Sub SetCboxRowSource()

    '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

并在设置标签后调用

With New UserForm1
    .Tag = myRangeNameVendor & "|" & myRangeNameVendorCode
    .SetCboxRowSource
    .Show
End With

或者直接用

 With New FrmVendor
      .cboxVendorName.RowSource = wb.Names(myRangeNameVendor).RefersToRange.Address(external:=True)
      .cboxVendorCode.RowSource = wb.Names(myRangeNameVendorCode).RefersToRange.Address(external:=True)
      .Show
   End With

这篇关于用户窗体组合框未填充外部数据范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！