Reference cells in a Hidden Worksheet for display in a UserForm

[scott56hannah]

I have workbook which has a number of reference worksheets that need to be
hidden from the users view.

Those sheets need to be referred to by a UserForm as part of drop down lists
and other validations.

When trying to populate the combo box I get the following error....

Run-time error '1004':
Select method of Range class failed

Refer to the routine that is trying to create the list below

Public Sub SetAllLists()
'This routine will set all lists with the current values that are in place
at that time

'Create the Building Drop Down list for the Inspection form
Worksheets("Reference").Activate
Worksheets("Reference").Range("ReferenceBuildingHeadingStart").Select
If ActiveCell.Offset(1, 0).Value = "" Then
ActiveCell.Offset(1, 0).Select

'If there is only one entry in the List box then no need to sort or do
multiple selection
ElseIf ActiveCell.Offset(2, 0).Value = "" Then
ActiveCell.Offset(1, 0).Select

'If more than one entry then use the sort to refresh the list
Else
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("ReferenceBuildingHeadingStart"), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If

'Now reset the List of details shown on the page
Inspection.frmBuildingComboBox.RowSource =
ActiveWindow.RangeSelection.Address

End Sub

Is there any way to refer to data to worksheets that are hidden without
getting this error ?

 

[scott56hannah]

I forgot to add that when the "Reference" worksheet is unhidden the routine
below works fine and displays the values in the Combo box

 

[Dave Peterson]

You very rarely have to select/activate sheets/ranges to work with them.

I _think_ that this does what you want. (You really want a blank line in the
combobox if there is no data???)

Option Explicit
Public Sub SetAllLists()

Dim myRng As Range
Dim RefBldgHdStart As Range

'Create the Building Drop Down list for the Inspection form
With Worksheets("Reference")
Set RefBldgHdStart = .Range("ReferenceBuildingHeadingStart")
Set myRng = .Range(RefBldgHdStart.Cells(1), _
.Cells(.Rows.Count, RefBldgHdStart.Column).End(xlUp)) _
.Resize(, RefBldgHdStart.Columns.Count)
End With

With myRng
'I included the headers in the sort!
.Cells.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

If .Columns(1).Cells.Count = 1 Then
'just use an empty row???
Set myRng = myRng.Offset(1, 0)
Else
'omit the headers
Set myRng = .Resize(.Rows.Count - 1).Offset(1, 0)
End If
End With

'Now reset the List of details shown on the page
Inspection.frmBuildingComboBox.RowSource = myRng.Address(external:=True)

'for my testing
'MsgBox myRng.Address(external:=True)

End Sub

 

[FSt1]

hi
which line is generating the error?

Regards
FSt1