I can't populate data on userform if the sheet is not active

[Abdo]

hello
I search for way if I run the userform from any sheet should populate data on userform
now I run userform from sheet2 and should brings data from sheet1 . so far the sheet1 should be activate to poulate data otherwise doesn't show anything
here is the code

Sub Due_Date()

Dim i, lastrow As Long
Application.ScreenUpdating = False
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow
    If Cells(i, 1) - Date >= 2 And Cells(i, 4) - Date <= 20 Then
        With UserForm1.ListBox1
            .AddItem
            .List(.ListCount - 1, 1) = Sheet1.Cells(i, 2).Text
            .List(.ListCount - 1, 2) = Sheet1.Cells(i, 3).Text
            .List(.ListCount - 1, 3) = Sheet1.Cells(i, 4).Text
        End With
        
    End If
Next
Application.ScreenUpdating = True
UserForm1.Show
End Sub

any idea guys ?

 

[Fluff]

You need to qualify the cells on this line with the sheet name as you have done with the rest of the code.

If Cells(i, 1) - Date >= 2 And Cells(i, 4) - Date <= 20 Then

 

[Abdo]

Hi,
if you mean like this

If Sheet1.Cells(i, 1) - Date >= 2 And Sheet1.Cells(i, 4) - Date <= 20 Then

also doesn't work .

 

[Fluff]

In what way doesn't it work?

 

[Abdo]

when I run the userform from sheet2 is active and the data are existed in sheet1 then should populate data when run the userform

in one case populate the data if I have select sheet1 and run the userform from it( sheet1 must be activated). otherwise doesn't show data when run userform.

 

[Fluff]

Is Sheet1 the actual name of the sheet (as seen on the tab) or is it the codename?

 

[Abdo]

that's right ,not the same sheet name ,seem to contains a little space, just question but why doesn't show subscript out of range if is not matched the sheet name?

 

[Fluff]

You are using the Sheet Codename, so it must exist, but maybe not the sheet you think it is.

 

[Abdo]

this problem was confused & strange !
thanks for your solution .

 

[Fluff]

My pleasure & glad it's sorted.