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

DarkJester89

Board Regular
Joined
Nov 5, 2017
Messages
94
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
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.

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

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

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

Thanks in advance.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

sericom

Well-known Member
Joined
Jan 19, 2006
Messages
915
Does referencing the specific sheet on all the following lines do what you're after?

VBA Code:
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

Board Regular
Joined
Nov 5, 2017
Messages
94
Office Version
  1. 2016
Platform
  1. Windows
Solution verified! @sericom Do you have any suggestions on when the sheet it's counting is hidden?
 

sericom

Well-known Member
Joined
Jan 19, 2006
Messages
915
All that code should work on a hidden sheet. If not you could unhide it, process it and then hide it again

VBA Code:
Sheets("TheNameOfSheet").visible = True
 

DarkJester89

Board Regular
Joined
Nov 5, 2017
Messages
94
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jan 19, 2006
Messages
915
Try this

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,479
Messages
5,636,573
Members
416,925
Latest member
malamutus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top