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

CodePudding user response：

Dim wks As Worksheet
Dim AddNew As Range
Set wks = ActiveSheet
Dim h As Integer

h = wks.Range("C65356").End(xlUp).Row

If h < 18 Then h = 18
if h > 30 then exit Sub

Set AddNew = wks.Range("A" & h)

AddNew.Offset(1, 2).Value = txtCAC.Text
AddNew.Offset(1, 4).Value = txtName.Text
AddNew.Offset(1, 5).Value = txtType.Text
AddNew.Offset(1, 6).Value = txtClass.Text
AddNew.Offset(1, 7).Value = txtDate.Text
AddNew.Offset(1, 8).Value = txtParent.Text
AddNew.Offset(1, 9).Value = txtManagement.Text
AddNew.Offset(1, 10).Value = txtSuccess.Text
AddNew.Offset(1, 12).Value = txtPercentage.Text
AddNew.Offset(1, 21).Value = txtCommittment.Text
AddNew.Offset(1, 38).Value = txtContribution.Text
AddNew.Offset(1, 40).Value = txtRedemption.Text

'lstDisplay.ColumnCount = 41
'lstDisplay.RowSource = "A2:A65356"

CodePudding user response：

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 `