Opening a userform on a different sheet than the reference data used in the initialize process

[Kmanbozzy]

Here is my issue. When i open my userform on any sheet other than the sheet "Data Entry" or sheet3 in my case, my initialization fails because i reference a dynamic range on sheet3. I found away around it by selecting sheet3 at the beginning and jumping back to the activesheet at the end. but i want to eliminate using select and actual names of the sheet, hence me using sheet3.

it has to be the way im referring to the range when im away from sheet3

this will not work when away from sheet3 error is at the m= line

Private Sub UserForm_Initialize()Dim sh As Worksheet
Set sh = Sheet3


h = 16  'this is my method of lastrow i use to exclude any empty cells in the range it returns the last row
Do Until m = 1048576
m = sh.Range(Cells(h, 34), Cells(h, 34)).End(xlDown).Row
    If m = 1048576 Then
        Exit Do
    End If
h = sh.Range(Cells(h, 34), Cells(h, 34)).End(xlDown).Row
Loop


rng = sh.Range(Cells(16, 34), Cells(h, 34)).Address


With sh
         ListBox1.RowSource = "'" & .name & "'!" & rng
End With
End Sub

this code works, but has the select in use

Private Sub UserForm_Initialize()Dim sh As Worksheet
Dim ws As Worksheet
Dim rng As Range
Application.ScreenUpdating = False


Set sh = Sheet3
Set ws = ActiveSheet
sh.Select


h = 16   
Do Until m = 1048576
m = sh.Range(Cells(h, 34), Cells(h, 34)).End(xlDown).Row
    If m = 1048576 Then
        Exit Do
    End If
h = sh.Range(Cells(h, 34), Cells(h, 34)).End(xlDown).Row
Loop


rng = sh.Range(Cells(16, 34), Cells(h, 34)).Address


With sh
         ListBox1.RowSource = "'" & .name & "'!" & rng
End With
ws.Select
Application.ScreenUpdating = True
End Sub

help please?

 

[Kmanbozzy]

Please excuse the Dim sh as worksheet right after the sub, its correct in my actual code, that is not the problem

 

[Fluff]

Try

Private Sub UserForm_Initialize()
   Dim sh As Worksheet
   Set sh = Sheet3
   
   With sh
      h = 16  'this is my method of lastrow i use to exclude any empty cells in the range it returns the last row
      Do Until m = 1048576
         m = .Range(.Cells(h, 34), .Cells(h, 34)).End(xlDown).Row
         If m = 1048576 Then Exit Do
         h = .Range(.Cells(h, 34), .Cells(h, 34)).End(xlDown).Row
      Loop
      
      
      Rng = .Range(.Cells(16, 34), .Cells(h, 34)).Address
      
      
      ListBox1.RowSource = "'" & .Name & "'!" & Rng
   End With
End Sub