VBA multiple textbox to cell value

[hajiali]

Just need a better way to input 112 textboxes from column D - DN

instead of using this below for 112 textboxes

         Cells(Rg.Row, "D") = TextBox3
         Cells(Rg.Row, "F") = TextBox4
         Cells(Rg.Row, "E") = TextBox5
         Cells(Rg.Row, "G") = TextBox6

Private Sub CommandButton1_Click()
Dim EmpID$, dDate$, Mth$, Yr$, Rg, Dd As Range, EmpCells&, i&, x&, Emp1Name As String, Emp2Name As String
EmpID = TextBox1.Value

If TextBox1.Value = "" Or TextBox2.Value = "" Then
   MsgBox "PLEASE ENTER THE EMPLOYEE NUMBER AND EFFECTIVE DATE", vbExclamation: Exit Sub
End If

If Not IsNumeric(EmpID) Then
MsgBox "INVALID EMPLOYEE NUMBER ENTER NUMBER WITHOUT THE E", vbExclamation: Exit Sub
End If

With Sheets("PERMANENT BID")
         Set Rg = .Columns(5).Find(EmpID, lookat:=xlWhole)
         If Rg Is Nothing Then
            MsgBox "EMPLOYEE NUMBER DOES NOT EXIST PLEASE TRY AGAIN", vbExclamation: Exit Sub
         Else
         Cells(Rg.Row, "D") = TextBox3
         Cells(Rg.Row, "F") = TextBox4
         Cells(Rg.Row, "E") = TextBox5
         Cells(Rg.Row, "G") = TextBox6
End Sub

is there a way to use a shortcut like below

 'Set Rg = (.Cells(Rg.Row, "D").Resize(, 112))

 

[6StringJazzer]

You can resize the range but you still can't load 100 textboxes all in one go.

Are your textboxes in a UserForm, or on a worksheet? Let's try this for a UserForm:

   For i = 1 To 112
      Cells(Rg.Row, i+3) = Controls("TextBox" & i+2).Value
   Next i

If your textboxes are on a worksheet, are they Forms controls or ActiveX?

 

[hajiali]

They are in userforms. I will give that a try when I get back on the computer. Thanks

 

[Fluff]

In order to use a loop as suggested by 6String, your textboxes & cells must be in order. At the moment you are going columns D,F,E,G which are not in order & will cause problems.

 

[6StringJazzer]

At the moment you are going columns D,F,E,G which are not in order

Oh, I missed that. Hopefully it is just a typo since the conceptual solution was to resize the cell range for all cells.

 

[Fluff]

Hopefully it is just a typo

Agreed, either that or the OP can change the order of the textboxes to match the columns in order.

 

[hajiali]

Oh, I missed that. Hopefully it is just a typo since the conceptual solution was to resize the cell range for all cells.

Yes thank you but this was a typo on my end. The textbox and Cells are in order. Thanks

 

[6StringJazzer]

Give that code a try and let us know how it works.

 

[hajiali]

You can resize the range but you still can't load 100 textboxes all in one go.

Are your textboxes in a UserForm, or on a worksheet? Let's try this for a UserForm:

   For i = 1 To 112
      Cells(Rg.Row, i+3) = Controls("TextBox" & i+2).Value
   Next i

If your textboxes are on a worksheet, are they Forms controls or ActiveX?

this works great. However is there a way to skip any textbox that are blank. For example out of 112 textbox if 6 are numbers just run only "i" with text. Currently its running though all 112 and really slow

 

[6StringJazzer]

Make these two changes, should speed up:

   Application.ScreenUpdating = False
   For i = 1 To 112
      If Controls("TextBox" & i+2).Value <> "" Then
         Cells(Rg.Row, i+3) = Controls("TextBox" & i+2).Value
      End If
   Next i
   Application.ScreenUpdating = True