I am trying to have an entry data userform where the input starts at "A19" and will end at row "A30" once the empty rows get filled 1 by 1, instead of the current situation where it starts at "A1" and goes unrestricted.
Private Sub cmdAdd_Click()
Dim wks As Worksheet
Dim AddNew As Range
Set wks = ActiveSheet
Set AddNew = wks.Range("A2:A65565").End(xlUp).Offset(1, 0)
AddNew.Offset(0, 2).Value = txtCAC.Text
AddNew.Offset(0, 4).Value = txtName.Text
AddNew.Offset(0, 5).Value = txtType.Text
AddNew.Offset(0, 6).Value = txtClass.Text
AddNew.Offset(0, 7).Value = txtDate.Text
AddNew.Offset(0, 8).Value = txtParent.Text
AddNew.Offset(0, 9).Value = txtManagement.Text
AddNew.Offset(0, 10).Value = txtSuccess.Text
AddNew.Offset(0, 12).Value = txtPercentage.Text
AddNew.Offset(0, 21).Value = txtCommittment.Text
AddNew.Offset(0, 38).Value = txtContribution.Text
AddNew.Offset(0, 40).Value = txtRedemption.Text
lstDisplay.ColumnCount = 41
lstDisplay.RowSource = "A2:A65356"
End Sub
As apparently your goal is to overwrite an adjacent fixed target range with a complete set of textbox entries, I'd propose the following steps:
[0.] Define the fixed start cell in target cell e.g. via set tgt = Sheet1.Range("A19").[1. a)] Split a list of needed textbox names thus getting a 1-dimensional array, which btw will be 0-based automatically.[1. b)] Provide for data by a 2-dim data array and make it zero-based, too in order to
synchronize both array counters in the following loop (1.c).[1. c)] Fill the data array rows with all textbox contents by a For..Next loop
and check for not allowed zero-length inputs;
if there are any display a warning message and redirect focus to the empty textbox.[2.] Eventually dump back the data array to the chosen target range
by using the number of listed textbox controls cnt to .Resize the target range (e.g. 12 in OP).Private Sub cmdAdd_Click()
'0. Define fixed start cell in target range
dim tgt as Range
set tgt = Sheet1.Range("A19") ' change to any wanted sheet Code(Name)
'1. a) split a list of needed textbox names getting a 1-dim 0-based array automatically
Dim myTextboxes As Variant
myTextboxes = Split( _
"txtCAC,txtName,txtType,txtClass,txtDate,txtParent,txtManagement," & _
"txtSuccess,txtPercentage,txtCommittment,txtContribution,TxtRedemption", _
",")
Dim cnt As Long
cnt = UBound(myTextboxes) + 1 ' count number of textboxes
' b) provide for data by a 2-dim data array (make it zero-based, too)
Dim data As Variant
ReDim data(0 To cnt - 1, 0 To 0) ' define dimensions holding data
' c) fill data array rows with all textbox contents
Dim i As Long, ctrl As MSForms.Control
For i = LBound(data) To UBound(data) ' i.e. 0 To 11
Set ctrl = Me.Controls(myTextboxes(i)) ' set control to memory
data(i, 0) = ctrl.Text ' get textbox content
' check for complete entries or exit sub
If Len(Trim(data(i, 0))) = 0 Then ' check for zero-length input
MsgBox "Fill in empty Textbox(es) first!", vbExclamation, ctrl.Name
ctrl.SetFocus ' set focus to empty box
Exit Sub ' escape procedure
End If
Next
'2. dump data to target range
tgt.Resize(cnt, 1) = data ' write data
End Sub
Further hints
I think there will be to need to define a RowSource (btw better to use "Sheet1!A19:A30" if you are overwriting all data anyway by command button.
Side note: Prefer to get a last row cell via e.g. Sheet1.Range("A" & .Rows.Count).End(xlUp) or the row index via .Range("A" & .Rows.Count).End(xlUp).Row instead of coding a fixed rows count (current sheets have at about 1 million). You might be interested in reading Finding last used cell
`