[Still need] VBA help with Range and Cells methods

Posted by Tim Francis-Wright on August 18, 2000 8:42 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

Posted by Tim Francis-Wright on August 22, 0100 7:11 AM

Thanks for the help, Ivan.
I found another part of the answer by
trial-and-error. When I was setting the
RowSource of the listboxes and comboboxes,
using .Range("DefinedName"), VBA was assuming
that the active sheet was the worksheet in
question--in the worksheets, any defined range
name didn't need a sheet qualifier.

So, I used
For Each ThisControl in Me.Controls
With ThisControl
' ** ...snip

With Worksheets("Hidden")
' ** the key part is .Name & "!" &
TypeRange = .Name & "!" & .Range("Defined") _
.Range(.Cells(4, WhichPage), _
.Cells(3 + NumTypes, WhichPage)).Address
End With
.RowSource = TypeRange
.Value = Application.WorksheetFunction.HLookup _
(WhichPage, Range("Defined"), 4)

' ** ...snip
End With
Next

Posted by Ivan Moala on August 18, 0100 2:09 PM

Tim
I suspect that you have not fully qualified your
range arguments. When selecting or referencing
a range from another sheet eg
Worksheets("Sheet1").Range(Range("a1"), _
Range("b4")).select

you have to qualify the inner and outer range
In this example the inner range is;
Sheets("sheet1").range("a1")
your outer range is not qualified
= activesheet.range("b4")
A more effecient syntax should be;
With Worksheets("Sheet1")
.Range(.Range("a1"), .Range("b4")).select
End With

HTH

Ivan

Posted by Ivan Moala on August 25, 0100 3:56 PM

Tim
Glad you found the solution.
Sorry I could reply here or to your email.
I was temp off line......very annoying considering
I got bombarded by a few 100 emails.

Ivan