Help with defining a Range

[Guest]

I'm using a VB to define a range and then loop through the range to fill a
combobox on a form. The code works fine if the selected sheet is the sheet
on which VB draws the range, but the code fails when some other sheet is
selected. So, simplifying things, I've got two sheets, Sheet1(Overall) and
Sheet27(Checking). When I run the form from Sheet1(Overall), I have no
problem. When I run it with some other sheet active, I get an error message.
I think the problem has to do with the Set... command.

Here's the code (including the loop):

Dim myRange As Range
Set myRange = Sheets("Overall").Range(Range("B2"), Range("B2").End(xlDown))

Dim a As Variant
Dim lrow As Integer

'fills combobox1 and sets the variable lrow
For Each a In myRange
ComboBox1.AddItem a
lrow = lrow + 1
Next a
ComboBox1.ListRows = lrow


Now, I know someone will say, simply use VB to select the "Overall" sheet,
then run the Set command. This does work, but it seems rather unnecessary.
How do I specify the range on the Overall sheet without having to activate
and select it first?

Thanks,
Dave

 

[Bob Phillips]

I hope no-one will say that <g>!

See if this sorts it

With Sheets("Overall")
Set myRange = .Range(.Range("B2"), .Range("B2").End(xlDown))
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Looks like a winner. Thanks, Bob.

I hope no-one will say that <g>!

See if this sorts it

With Sheets("Overall")
Set myRange = .Range(.Range("B2"), .Range("B2").End(xlDown))
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)