DarkJester89
Board Regular
- Joined
- Nov 5, 2017
- Messages
- 109
- Office Version
- 2016
- Platform
- 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.