Pass value from multiple textboxes on userform to cells in worksheet

[Jimmypop]

Good day again people need some assistance again

I realize and can do one or two textboxes with code that I have done before but my issues (which I hope can be fixed and is not too difficult) is as follows: (bit of a challenge for you):

• I have 468 Textboxes on a userform (named Percentages). I want to that when value in textbox is updated it is passed onto a certain cell on the worksheet namely:
  
  

  Textbox1.Value to cell B2
  Textbox2.Value to cell C2
  Textbox3.Value to cell D2
  Textbox4.Value to cell E2
  Textbox5.Value to cell F2
  Textbox6.Value to cell G2
  Textbox7.Value to cell H2
  Textbox8.Value to cell I2
  Textbox9.Value to cell J2
  Textbox10.Value to cell K2
  Textbox11.Value to cell L2
  Textbox12.Value to cell M2

  
  
• So therefore Textbox13 – Textbox24 will be added to B3:M3 etc. (I have added a screenshot of worksheet and userform)
  
  Range for percentages is B2:M39

 

[kanadaaa]

Try this in the form module.

Private Sub CommandButton1_Click()
    Dim i As Integer, j As Integer
    For j = 1 To 39 'Row
        For i = 1 To 12 'Column
            Cells(j + 1, i + 1).Value = Me.Controls("TextBox" & (j - 1) * 12 + i).Value
        Next
    Next
End Sub

 

[Jimmypop]

Hi kanadaaa

It works but with some error at the end on line

Cells(j + 1, i + 1).Value = Me.Controls("TextBox" & (j - 1) * 12 + i).Value

 

[kanadaaa]

I think you have some textbox that isn't named TextBox + numeral.

 

[kanadaaa]

It must be a hassle to go over every textbox so you might want to use this code to figure out which one is causing the error:

Private Sub CommandButton1_Click()
    Dim i As Integer, j As Integer
    On Error GoTo errHandler
    For j = 1 To 39 'Row
        For i = 1 To 12 'Column
            Cells(j + 1, i + 1).Value = Me.Controls("TextBox" & (j - 1) * 12 + i).Value
        Next
    Next
errHandler:
    Debug.Print i
    Debug.Print j
End Sub

 

[Jimmypop]

It will be yes ? ? ...

I have updated the code and it is not giving me the error now. Whoooppeee...

But it was not updating the last row of worksheet.... Row 39.. I investigated and realized that the last row of text boxes did not follow in sequence...corrected and working now.

It was quite simple solution.??

 

[kanadaaa]

Glad to hear you managed to solve the problem!