Help with Range and Cells methods

Posted by Tm Francis-Wright on August 15, 2000 10:57 AM

I'm trying to get a userform to set up
a combobox contents from a lookup table
on a worksheet. I want this worksheet
to be hidden from the user, who will
have another sheet activated.

(The VBA code for the userform uses variables
in place of the 3 and 5 below.)

How can I access the range without activating
the hidden sheet? What am I doing wrong?

Thanks, Tim F-W

Here's the results from the Immediate pane:

debug.Print range("incometypes").name
=Hidden!$A$104:$F$111

'with worksheet called "Hidden" activated
debug.Print Range("incometypes"). _
Range(Cells(3,1),cells(5,1)).Address
$A$106:$A$108

'with some other worksheet activated
debug.Print Range("incometypes"). _
Range(Cells(3,1),cells(5,1)).Address
[yields Run-Time error '1004":
application-defined or object-defined error]

Posted by erica on August 17, 0100 5:35 AM

Try: Sheets("YourSheetName").Range....
If you specify the sheetname it will know where to find the range, even if that sheet is hidden.

Posted by Tim Francis-Wright on August 17, 0100 6:40 AM

I thought so, too, but I still get the same error.
The NumTypes line works fine.
The TypeRange part works--only if
the Hidden sheet is activated.

Can I get this to work even if that sheet isn't active? (Another part of the code will really
work best if it can refer to another sheet
as the active sheet.)

Here's more of the code for the form:

For Each ThisControl in Me.Controls
With This Control
If .Name like "TypeCB*" then
' each control called TypeCB1 etc. is on a
' page of a multipage control
WhichPage = .Parent.Index + 1
NumTypes=Application.WorksheetFunction.HLookup( _
WhichPage, Range("IncomeTypes"), 2)
TypeRange = Sheets("Hidden").Range _
("IncomeTypes").Range(Cells(3, WhichPage), _
Cells(2 + NumTypes, WhichPage)).Address
' {snip}
End if
End With
Next