Hi,
I’ve come to a point where I’m confused & unable to continue.
I have a user form where I enter the values into each TextBox.
I then use a command button of which shows me the InputBox.
I would enter a number in the InputBox & click ok.
Now here is where I’ve failed for a few hours trying various non working options.
Example.
Say I entered 7 in the InputBox.
I then would like the user form values to be inserted / pasted into row 7.
This should be a simple task but for some reason it’s got me this time.
Here is my code.
Code
Private Sub CommandButton1_Click()
Dim i As Integer
Dim ControlsArr As Variant, ctrl As Variant
Dim x As Long
Dim z As Integer
z = Application.InputBox("INSERT DATA TO WHICH ROW ?", "ROW NUMBER MESSAGE BOX", Type:=1)
For i = 1 To 11
With Me.Controls("TextBox" & i)
If .Text = "" Then
MsgBox "ALL FIELDS MUST BE COMPLETED", 48, "GRASS NEW CUSTOMER FORM"
.SetFocus
Exit Sub
End If
End With
Next i
ControlsArr = Array(Me.TextBox1, Me.TextBox2, Me.TextBox3, Me.TextBox4, Me.TextBox5, Me.TextBox6, Me.TextBox7, Me.TextBox8, Me.TextBox9, Me.TextBox10, Me.TextBox11)
With ThisWorkbook.Worksheets("GRASS")
.Range("A4").EntireRow.Insert Shift:=xlDown
.Range("A4:K4").Borders.Weight = xlThin
For i = 0 To UBound(ControlsArr)
Select Case i
Case 3
.Cells(4, i + 1) = Val(ControlsArr(i))
ControlsArr(i).Text = ""
Case Else
.Cells(4, i + 1) = ControlsArr(i)
ControlsArr(i).Text = ""
End Select
Next i
End With
Application.ScreenUpdating = False
ActiveWorkbook.Save
Application.ScreenUpdating = True
With ThisWorkbook.Worksheets("GRASS")
.Range("A5").Select
.Range("A4").Select
MsgBox "DATABASE HAS NOW BEEN UPDATED", vbInformation, "SUCCESSFUL MESSAGE"
End With
Unload GrassNewCustomer
End Sub
Display More