Sal,

Cells(x, x) always refers to the active sheet.
So you should prefix cells with the sheet name if you are
referring to another sheet...
'(notice the dots)

With Worksheets(1)
Rw = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rng = .Range(.Cells(2, 2), .Cells(Rw, 2))
End With

Jim Cone
San Francisco, USA


"SP" wrote in message oups.com...
I get an error in this Subroutine at the line marked , it works fine
if I activate the worksheet first and remove the "Worksheets(1)." from
the code.
Option Explicit
Dim Rng As Range
Dim Rw As Long

Private Sub UserForm_Activate()
Rw = Worksheets(1).Range("A65536").End(xlUp).Row
Set Rng = Worksheets(1).Range(Cells(2, 2), Cells(Rw, 2))
ComboBox1.RowSource = Rng.Address
End Sub

Any idea why this is happening ?

Thanks
Sal