How to switch from active sheet to specific sheet for counter?

[DarkJester89]

Sub ShowUserForm1()

''Check where the last row is on coloumn B
'' The counter removes one because the first cell is "Topic"
counter = Cells(Rows.Count, 4).End(xlUp).Row

While (Range("D" & counter).Value) = ""
 counter = counter - 1
Wend


'' Loop through all the objects in the userform
'' In this example, it's important that the label and textbox will be names Label# / TextBox# because it removes 5 or 7 strings to extract the object number
'' Check the number of the object, and if it's higher than the counter, hides it

For Each formObject In UserForm1.Controls

    If TypeName(formObject) = "Label" Then
        If Left(formObject.Caption, 5) = "Label" Then
        objectNumber = Right(formObject.Name, Len(formObject.Name) - 5)
    
        'Change the label caption according to the cell value
        formObject.Caption = Cells(CInt(objectNumber) + 1, 4).Value
    
        If CInt(objectNumber) > counter Then formObject.Visible = False
        End If
    End If
    
    If TypeName(formObject) = "TextBox" Then
    objectNumber = Right(formObject.Name, Len(formObject.Name) - 7)
    
    If objectNumber > 12 Then objectNumber = objectNumber - 12
        
    If CInt(objectNumber) > counter - 1 Then formObject.Visible = False
    End If
    
Next


If counter < 5 Then
'' Change the userform height, you can play with the numbers
UserForm1.Height = 70 + 40 * counter
' Move the button up higher
UserForm1.CommandButton1.Top = 40 + 43 * counter - 60

ElseIf counter < 13 Then
'' Change the userform height, you can play with the numbers
UserForm1.Height = 70 + 35 * counter
' Move the button up higher
UserForm1.CommandButton1.Top = 40 + 35 * counter - 60

ElseIf counter > 13 Then
'' Change the userform height, you can play with the numbers
UserForm1.Height = 70 + 50 * counter
' Move the button up higher
UserForm1.CommandButton1.Top = 40 + 53 * counter - 60

End If

UserForm1.Show
End Sub

The code activates and calls userform based on data in active sheet. I'm trying to adjust to "specific sheet" and haven't had much luck yet but hopefully a smarter soul can assist. So I can call the userform and it pull data from the inactive sheet.

counter = Cells(Rows.Count, 4).End(xlUp).Row

^I've adjusted this to this, but it doesn't work.

ThisWorkbook.Sheets("TheNameOfSheet").Cells(Rows.Count, 4).End(xlUp).Row

Thanks in advance.

 

[sericom]

Does referencing the specific sheet on all the following lines do what you're after?

counter = Sheets("TheNameOfSheet").Cells(Rows.Count, 4).End(xlUp).Row

While (Sheets("TheNameOfSheet").Range("D" & counter).Value) = ""

formObject.Caption = Sheets("TheNameOfSheet").Cells(CInt(objectNumber) + 1, 4).Value

 

[DarkJester89]

Solution verified! @sericom Do you have any suggestions on when the sheet it's counting is hidden?

 

[sericom]

All that code should work on a hidden sheet. If not you could unhide it, process it and then hide it again

Sheets("TheNameOfSheet").visible = True

 

[DarkJester89]

It won't work with as hidden sheet, I couldn't figure out how to put application updating haha one door opens, 2 close i guess lol thanks for the assist, any suggestions would be greatly appreciated!

 

[sericom]

Try this

Sub ShowUserForm1()

    Application.ScreenUpdating = False
    Sheets("TheNameOfSheet").Visible = True
   
    ''Check where the last row is on coloumn B
    '' The counter removes one because the first cell is "Topic"
    counter = Sheets("TheNameOfSheet").Cells(Rows.Count, 4).End(xlUp).Row
   
    While (Sheets("TheNameOfSheet").Range("D" & counter).Value) = ""
     counter = counter - 1
    Wend
       
    '' Loop through all the objects in the userform
    '' In this example, it's important that the label and textbox will be names Label# / TextBox# because it removes 5 or 7 strings to extract the object number
    '' Check the number of the object, and if it's higher than the counter, hides it
   
    For Each formObject In UserForm1.Controls
   
        If TypeName(formObject) = "Label" Then
            If Left(formObject.Caption, 5) = "Label" Then
            objectNumber = Right(formObject.Name, Len(formObject.Name) - 5)
       
            'Change the label caption according to the cell value
            formObject.Caption = Sheets("TheNameOfSheet").Cells(CInt(objectNumber) + 1, 4).Value
       
            If CInt(objectNumber) > counter Then formObject.Visible = False
            End If
        End If
       
        If TypeName(formObject) = "TextBox" Then
        objectNumber = Right(formObject.Name, Len(formObject.Name) - 7)
       
        If objectNumber > 12 Then objectNumber = objectNumber - 12
           
        If CInt(objectNumber) > counter - 1 Then formObject.Visible = False
        End If
       
    Next
       
    If counter < 5 Then
    '' Change the userform height, you can play with the numbers
    UserForm1.Height = 70 + 40 * counter
    ' Move the button up higher
    UserForm1.CommandButton1.Top = 40 + 43 * counter - 60
   
    ElseIf counter < 13 Then
    '' Change the userform height, you can play with the numbers
    UserForm1.Height = 70 + 35 * counter
    ' Move the button up higher
    UserForm1.CommandButton1.Top = 40 + 35 * counter - 60
   
    ElseIf counter > 13 Then
    '' Change the userform height, you can play with the numbers
    UserForm1.Height = 70 + 50 * counter
    ' Move the button up higher
    UserForm1.CommandButton1.Top = 40 + 53 * counter - 60
   
    End If
   
    Sheets("TheNameOfSheet").Visible = False
    Application.ScreenUpdating = True
   
   
    UserForm1.Show
End Sub

 

[DarkJester89]

Thank you @sericom