Userform VBA to fill Dynamically TextBoxes

[ricafonyat]

My userform has 10 textboxes with the following names:

TB_Year1
TB_Year2
TB_Year3
TB_Year4
TB_Year5
TB_Year6
TB_Year7
TB_Year8
TB_Year9
TB_Year10

And I have two named ranges in my worksheet("Sheet1")

Range1 --- > firstYear example: 2022
Range2 --- > totalYears example: 5

My goal is to fill the TB_Year1 with the value of the FirstYear and the rest adding 1 year until totalYears

Any help would be appreciated

 

[DanteAmor]

Try this

Private Sub UserForm_Activate()
  Dim i As Long
  If Range("Range2").Value < 11 Then
    For i = 1 To Range("Range2").Value
      Controls("TB_Year" & i) = Range("Range1").Value + i - 1
    Next
  End If
End Sub

 

[ricafonyat]

Tks a lot Dante!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[ricafonyat]

Here is my final code and it works perfecty!

Private Sub UserForm_Activate()
  
  TotalYears = shDataBase.Range("TotalYears")
  OPeningYear = shDataBase.Range("OpeningYear")
  
  Dim i As Long
    For i = 1 To TotalYears
      Controls("TBRoomsSegmentYear" & i) = OPeningYear + i - 1
    Next

  Dim j As Long
    For j = 1 To TotalYears
      Controls("LBYear" & j) = OPeningYear + j - 1
    Next

End Sub

Best Regards

 

[DanteAmor]

It can be simplified to this:

Private Sub UserForm_Activate()
  Dim i As Long, TotalYears As Long, OPeningYear As Long
  
  TotalYears = shDataBase.Range("TotalYears")
  OPeningYear = shDataBase.Range("OpeningYear")
  
  For i = 1 To TotalYears
    Controls("TBRoomsSegmentYear" & i) = OPeningYear + i - 1
    Controls("LBYear" & i) = OPeningYear + i - 1
  Next
End Sub

 

[ricafonyat]

Much Better!