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