Changing Userform Label derived from one worksheet when inputting on a different worksheet

I have a worksheet that runs a weightlifting meet. Last year I had created a tab that would give information on the current lifter and on who was lifting next.

left side - Display, right side - input tab

When I input an "x" in columns R, S, T, or W on the data tab, it changes the information in the BenchGenerator tab, like so:

Updated Display tab

What I want to do is make a userform display to run on a different screen so people can see this information. I had accomplished this last year by widening excel and using two view windows - display on the second screen and running the meet on the computer. It was ok but very clunky looking. With a floating userform tab, it would look fantastic. I am new to this, but got the form floating:

Private Sub Worksheet_Change(ByVal Target As Range)
UserForm1.Show (vbModeless)
End Sub

And got the labels to initially populate:

Userform Display

Using this code:

Private Sub UserForm_Activate()
UserForm1.Label1.Caption = Sheets("BenchGenerator").Range("c4").Value
UserForm1.Label2.Caption = Sheets("BenchGenerator").Range("c5").Value
UserForm1.Label3.Caption = Sheets("BenchGenerator").Range("c6").Value
UserForm1.Label4.Caption = Sheets("BenchGenerator").Range("d3").Value
UserForm1.Label5.Caption = Sheets("BenchGenerator").Range("d4").Value
UserForm1.Label6.Caption = Sheets("BenchGenerator").Range("d5").Value
UserForm1.Label7.Caption = Sheets("BenchGenerator").Range("d6").Value
End Sub

What it doesn't currently do is update the captions when I input the "x" in the data tab.

As I mentioned, this is my first foray into userforms and looking through mountains of code trying to figure this out, it will not be my last as there is lots to accomplish with them.

Thanks in advance for any help!

Solution

You're pretty close to getting this to work. The problem is that your calling a new form every time a change occurs.

Declare your form as an object outside of the Sub that creates (Show) it.

You can then access it to update labels from another Sub that has the same scope.

Create an UpdateForm sub for example and call it from your Worksheet_Change event.

Try this, place the following code in a new Module:

Dim myForm As Object

Sub launchForm()
    Set myForm = UserForm1
    myForm.Show (vbModeless)
End Sub

Sub updateForm()
    Dim wks As Worksheet
    Set wks = Sheets("BenchGenerator")

    'Update label values here
    myForm.Label1.Caption = wks.Range("C4").Value
    myForm.Label2.Caption = wks.Range("C5").Value
    myForm.Label3.Caption = wks.Range("C6").Value
    myForm.Label4.Caption = wks.Range("D3").Value
    myForm.Label5.Caption = wks.Range("D4").Value
    myForm.Label6.Caption = wks.Range("D5").Value
    myForm.Label7.Caption = wks.Range("D6").Value
End Sub

If you use Worksheet_Change to update the form you'll want to verify the form exist or just skip any errors in the event if it doesn't.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    updateForm
End Sub