Error nr. 1004!

[MJA]

Hi,

Private Sub UserForm_Initialize()
Dim Rg As Range
Set Rg = Sheets(1).Range(Cells(2, 1), Cells(2, 2).End(xlDown))
With ListBox1
.ColumnCount = 2
.ColumnHeads = True
.RowSource = Rg.Address
End With
End Sub

When I play this macro on sheet 1 no problem!
When I play this macro on sheet 2, I get error number 1004!

My guess is that it goes wrong with the Set Rg = Sheets(1) part.

But the data stands on sheet 1 and I activate the userform on sheet 2!!!

Who can advise me?

[Ivan F Moala]

On 2002-05-19 04:36, MJA wrote:
Hi,

Private Sub UserForm_Initialize()
Dim Rg As Range
Set Rg = Sheets(1).Range(Cells(2, 1), Cells(2, 2).End(xlDown))
With ListBox1
.ColumnCount = 2
.ColumnHeads = True
.RowSource = Rg.Address
End With
End Sub

When I play this macro on sheet 1 no problem!
When I play this macro on sheet 2, I get error number 1004!

My guess is that it goes wrong with the Set Rg = Sheets(1) part.

But the data stands on sheet 1 and I activate the userform on sheet 2!!!

Who can advise me?

Hi MJA
There are 2 things you need to Do/realise
1) Set Rg = Sheets(1).Range(Cells(2, 1), Cells(2, 2).End(xlDown))
is OK when referencing and Sheets(1) is the
active sheet, BUT when you are in another sheet
the Range referencing is Actually looking
@ Sheets(1) and your Activesheet cells(2,2)
hence the Application Range error.

2) You need to explicitly set your range
address to reflect the actual sheet

To fix these 2 try;

Private Sub UserForm_Initialize()

Dim Rg As Range

With Sheets(1)

    Set Rg = .Range(.Range("A2"), .Range("B2").End(xlDown))

End With

With ListBox1

    .ColumnCount = 2

    .ColumnHeads = True

    .RowSource = Rg.Address(external:=True)

End With



End Sub

_________________
Kind Regards,
Ivan F Moala
Have a Nice day

[ This Message was edited by: Ivan F Moala on 2002-05-19 05:34 ]