This is the code below. I have a command button that opens the userform. If i have no data in datasheet the userform doesnt open and throws back an error. but if i have a test row then the userform opens.
Private Sub UserForm_Initialize()
Dim LDate As String
Dim lastRowColA As Long
Dim AssetDataCells As String
Set tempsheet3 = Sheets(cTempSheet3Name)
LDate = Date
lblUsername = GetXLUserName
cboAsset.List = Sheet2.Range("A2", "A10").Value
cboAssetSelect.List = tempsheet3.Range("A1", "A10").Value
lblDateAuto = LDate
'Copy data to temporary sheet where it will provide a list of unique Assets for the cboAssey combobox and
'filtered data for the lstAssetEstimates listbox
Set dataSheet = Sheets(cDataSheetName)
Set temporarySheet = Sheets(cTemporarySheetName)
firstFilteredCopyRow = 0
'Determine the last row containing data
With dataSheet
lastDataRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With temporarySheet
'Clear temporary data
.AutoFilterMode = False
.Cells.ClearContents
'Filter unique Assets on data sheet column B and copy list to temporary sheet column A
AssetDataCells = "A" & cFirstDataRow & ":A" & lastDataRow
dataSheet.Range(AssetDataCells).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=dataSheet.Range(AssetDataCells), CopyToRange:=.Range("A1"), Unique:=True
'Sort column A so that Assets are in alphabetical order
.Columns("A:A").Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes, MatchCase:=False
'Get the number of unique Assets and populate the cboAsset combobox. The combobox's RowSource
'starts at A2 so that it doesn't include the column header
lastRowColA = .Cells(.Rows.Count, "A").End(xlUp).Row
cboAsset.RowSource = temporarySheet.Name & "!" & .Range("A2:A" & lastRowColA).Address
'Copy data sheet columns A:F to temporary sheet columns B:G where it will be filtered to provide values for
'the lstAssetEstimates listbox
dataSheet.Range("A" & cFirstDataRow & ":F" & lastDataRow).Copy Destination:=.Range("B1")
End With
End Sub