I've been trying to solve this issue, off and on, for a couple of months without success. Unfortunately, I have been unable to find any documentation, information, or discussions that address the question at hand.

I have three Sub-Routines that are essentially identical to the one below hard coded with the Partially Qualified Label Name, i.e. "Label's Name without the Index.

Sub lblToggle2(ByRef lblYr As Long)
 Dim i
 Dim lblcnt As Long
    If Not lblYr = 0 Then
        lblcnt = wbCurYear - lblYr   1
        Debug.Print lblYr, lblcnt
        For i = 1 To lblcnt
            Me.Controls("lblCostYrE" & i).Caption = lblYr
            Me.Controls("lblCostYrG" & i).Caption = lblYr
            lblYr = lblYr   1
        Next i
      Else
        For i = 1 To 5
            Me.Controls("lblCostYrE" & i).Caption = vbNullString
            Me.Controls("lblCostYrG" & i).Caption = vbNullString
        Next i
    End If
End Sub

The sub above is based in part on This Link and is called using:

Call lblToggle2(StartYear)

What I am trying to accomplish is basically Code Reduction thereby allowing me to use one Sub to handle multiple Label combinations.

Here are two of the several attempts to make this work (all of which failed)!

Calling Code:

  Call lblToggleTest1(StartYear, """lblCostYrG""", """lblCostYrE""")
  Call lblToggleTest2(StartYear, """lblCostYrG""", """lblCostYrE""")

And Sub-Routine Code

Sub lblToggleTest1(ByRef lblYr As Long, lblG As String, lblE As String)
 Dim i
 Dim lblcnt As Long
 
    If Not lblYr = 0 Then
        lblcnt = wbCurYear - lblYr   1
        For i = 1 To lblcnt
        Debug.Print lblYr, cntrl1.Name, cntrl2.Name, lblcnt
            Me.Controls(lblG).Caption = lblYr
            Me.Controls(lblE).Caption = lblYr
            lblYr = lblYr   1
        Next i
      Else
        For i = 1 To 5
            Me.Controls(lblG).Caption = vbNullString
            Me.Controls(lblE).Caption = vbNullString
        Next i
    End If
End Sub

Sub lblToggleTest2(ByRef lblYr As Long, lblG As String, lblE As String)
 Dim i
 Dim lblcnt As Long
 Dim cntrl1 As Object
 Dim cntrl2 As Object
 cntrl1 = lblG & (i)
 cntrl2 = lblE & (i)
 
    If Not lblYr = 0 Then
        lblcnt = wbCurYear - lblYr   1
        For i = 1 To lblcnt
        Debug.Print lblYr, cntrl1.Name, cntrl2.Name, lblcnt
            Me.Controls(cntrl1).Caption = lblYr
            Me.Controls(cntrl2).Caption = lblYr
            lblYr = lblYr   1
        Next i
      Else
        For i = 1 To 5
            Me.Controls(cntrl1).Caption = vbNullString
            Me.Controls(cntrl2).Caption = vbNullString
        Next i
    End If
End Sub

I have tried declaring the Label variables as Variant, Object, and String. No matter what I try, I get Object Not Found, Object Required, or some other error!

I'm sure it's something simple that I am missing, but I have been scratching my head for so long trying to figure this out that I ma starting to go bald!

Could someone please help/point me in the right direction? Thank you in advance for your assistance.

CodePudding user response：

You have too many quotes in your calling code.

Untested:

Sub Tester()
    lblToggleTest StartYear, "lblCostYrG", "lblCostYrE"
End Sub


Sub lblToggleTest(ByRef lblYr As Long, lblG As String, lblE As String)
    Dim i As Long, lblcnt As Long, haveYear As Boolean, v
    
    haveYear = lblYr <> 0 'was a start year provided?
    lblcnt = IIf(haveYear, wbCurYear - lblYr   1, 5)
    
    For i = 1 To lblcnt
        v = IIf(haveYear, lblYr, vbNullString)
        Me.Controls(lblE & i).Caption = v
        Me.Controls(lblG & i).Caption = v
        If haveYear Then lblYr = lblYr   1
    Next i
    
End Sub