Search code examples
excelvbauserform

How do I code my userform to insert the specified number of rows beneath the selected cell?

I have a userform that I've created that first asks how many rows I would like to insert. The next part of the userform asks what values I would like in columns 1 and 32 of each newly created row (I've set it up so that a maximum of 6 new rows can be created at one time). My data has 45 columns, and the only data that I want to change in the newly created rows is the data in the two columns i said earlier (1 and 32). I want the data from all the other columns from the original row to be copied down into each new row. My problem is that I can't seem to figure out how to write a code that will do this the way I want it. To provide an example, if I respond to the userform that I want to add 3 rows below the currently active cell, it will then ask me what values i want to enter for columns 1 and 32 for each of these new rows. So I would enter something like this:

First New Row
Column 1: 8/17/2019
Column 32: 400

Second New Row
Column 1: 8/10/2019
Column 32: 500

Third New Row
Column 1: 8/3/2019
Column 32: 600

I've tried many different codes but I've only really figured out how to write it so that it inserts one row below the active cell and its completely blank, I don't know how to program it so that it enters he values I selected for columns 1 and 32 and copies all other data down from the original row. I've figured out the code for the clear and cancel button on my userform already, I am now only concerned with writing this code for the "OK" button.

Private Sub CancelButton_Click()

    Unload Me

End Sub

Private Sub ClearButton_Click()

    Call UserForm_Initialize

End Sub

Private Sub OKButton_Click()

    Dim lRow As Long
    Dim lRsp As Long

    On Error Resume Next

    lRow = Selection.Row()
    lRsp = MsgBox("Insert New row above " & lRow & "?", _
            vbQuestion + vbYesNo)
    If lRsp <> vbYes Then Exit Sub

    Rows(lRow).Select
    Selection.Copy
    Rows(lRow + 1).Select
    Selection.Insert Shift:=xlDown

    Application.CutCopyMode = False

    Rows(lRow).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone

End Sub

Private Sub UserForm_Initialize()

    AddRowsTextBox.Value = ""

    Date1TextBox.Value = ""
    Date2TextBox.Value = ""
    Date3TextBox.Value = ""
    Date4TextBox.Value = ""
    Date5TextBox.Value = ""
    Date6TextBox.Value = ""

    Qty1TextBox.Value = ""
    Qty2TextBox.Value = ""
    Qty3TextBox.Value = ""
    Qty4TextBox.Value = ""
    Qty5TextBox.Value = ""
    Qty6TextBox.Value = ""


End Sub
Solution

  • what i understand from your requirement, I would have add one more spin button on the form to make it user friendly. It may look like this.

    enter image description here

    User form Code may please be modified according to control names in your form

    Option Explicit
Public Bal As Double, XQnty As Double, LargeOrder As Double, Sm As Double
Private Sub CommandButton1_Click()
Dim lRow As Long
Dim lRsp As Long

lRow = ActiveCell.Row()
lRsp = MsgBox("Insert New row Below " & lRow & "?", vbQuestion + vbYesNo)


If lRsp <> vbYes Then Exit Sub
Dim Ws As Worksheet
Dim R As Long, i As Integer, RowtoAdd As Integer
Set Ws = ThisWorkbook.ActiveSheet
RowtoAdd = Me.SpinButton1.Value
R = ActiveCell.Row
  With Ws
        .Cells(R, 32).Value = LargeOrder
        For i = 1 To RowtoAdd
        .Cells(R + 1, 1).EntireRow.Insert Shift:=xlDown
        .Cells(R, 1).EntireRow.Copy Destination:=.Cells(R + 1, 1)
        .Cells(R + 1, 1).Value = Me.Controls("TextBox" & i).Value
        .Cells(R + 1, 32).Value = Me.Controls("TextBox" & 7 + i).Value
        R = R + 1
        Next i
  End With
Unload Me
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub SpinButton1_Change()
Dim x As Integer, i As Integer, Y As Double
Me.TextBox7.Value = Me.SpinButton1.Value
x = Me.SpinButton1.Value
Sm = 0
    For i = 1 To 6
    Me.Controls("TextBox" & i).BackColor = IIf(i <= x, RGB(255, 100, 100), RGB(255, 2550, 255))
    Me.Controls("TextBox" & i + 7).Value = IIf(i <= x, Int(Bal / x), 0)
    Sm = Sm + IIf(i <= x, Int(Bal / x), 0)
    Next

    If Sm <> Bal Then
    Me.TextBox8.Value = Int(Bal / x) + Bal - Sm
    End If
ManualBal
End Sub
Private Sub TB_LO_Change()
LargeOrder = Val(Me.TB_LO.Value)
Bal = XQnty - LargeOrder
ManualBal
End Sub
Private Sub UserForm_Initialize()
Dim i As Integer, dx As Variant
Me.SpinButton1.Value = 1
Me.TextBox7.Value = 1
Me.TextBox1.BackColor = RGB(255, 100, 100)
dx = ThisWorkbook.ActiveSheet.Cells(ActiveCell.Row, 1).Value
XQnty = ThisWorkbook.ActiveSheet.Cells(ActiveCell.Row, 32).Value
LargeOrder = 575
Bal = XQnty - LargeOrder
Sm = 0
If IsDate(dx) = False Then dx = Now()
    For i = 1 To 6
    Me.Controls("TextBox" & i).Value = Format(dx - i * 7, "mm-dd-yyyy")
    Sm = Sm + Int(Bal / 6)
    Me.Controls("TextBox" & i + 7).Value = Int(Bal / 6)
    Next
    If Sm <> Bal Then
    Me.TextBox8.Value = Int(Bal / 6) + Bal - Sm
    End If
Me.TB_LO = LargeOrder
Me.TB_Bal = 0
End Sub
Private Sub ManualBal()
Dim x As Integer, i As Integer
x = Me.SpinButton1.Value
Bal = XQnty - LargeOrder
Sm = 0
    For i = 1 To 6  ' Or may use 6 insted of X
    Sm = Sm + Val(Me.Controls("TextBox" & i + 7).Value)
    Next
    Me.TB_Bal.Value = Bal - Sm
End Sub
Private Sub TextBox8_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ManualBal
End Sub
Private Sub TextBox9_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ManualBal
End Sub
Private Sub TextBox10_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ManualBal
End Sub
Private Sub TextBox11_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ManualBal
End Sub
Private Sub TextBox12_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ManualBal
End Sub
Private Sub TextBox13_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ManualBal
End Sub

    Here Text Box 1 to 6 for dates, 7 for Spin Button values and Text Box 8 to 13 for quantity. May please either modify code according to control names Or modify Control names according to code.

    Edit: Two new Text Box added named TB_BAL to show when entering values in manually in Quantity text boxes (balance calculated only at exit event of text boxes) and TB_LO to change LargeOrder during run.