Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference cells in a Hidden Worksheet for display in a UserForm
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("ReferenceBuildingHe adingStart").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 ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference cells in a Hidden Worksheet for display in a UserForm
I forgot to add that when the "Reference" worksheet is unhidden the routine
below works fine and displays the values in the Combo box "scott56hannah" wrote: 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("ReferenceBuildingHe adingStart").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 ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference cells in a Hidden Worksheet for display in a UserForm
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 scott56hannah wrote: 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("ReferenceBuildingHe adingStart").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 ? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference cells in a Hidden Worksheet for display in a UserForm
hi
which line is generating the error? Regards FSt1 "scott56hannah" wrote: 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("ReferenceBuildingHe adingStart").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 ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display a hidden worksheet in Excel 2007? | Excel Discussion (Misc queries) | |||
Is it possible to display worksheets cells in a userform | Excel Programming | |||
Creating a varying UserForm using cells from a hidden worksheet | Excel Programming | |||
Reference a cell in a worksheet from a userform | Excel Programming | |||
Excel2000; display worksheet in userform | Excel Programming |