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 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?
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
