CODEWITHSUNDEEP
excelvba
RAHUL SINGHA ROY
RAHUL SINGHA ROY

Reputation: 11

fetching value from textbox and inserting into excel

I made a form to fetch values from textbox1, textbox2, textbox3 and input those values into a spreadsheet by clicking the add button. See attached picture:

attached picture.

But I am able to insert data to my form only once and all 3 rows are getting filled by the same data.

Private Sub Add_Click()

Dim roww As Integer
Dim colh As Integer

For colh = 0 To 2
    For roww = 0 To 2
        Range("A2").Offset(colh, roww).Value = Controls("TextBox" & roww + 1).Value
    Next roww
Next colh

End Sub

enter image description here

Upvotes: 1

Views: 269

Answers (3)

user6432984
user6432984

Reputation:

Method 1

Here I refactor your could correctly defining a target range for your values and correcting the colh to iterate over the columns as well as controls.

Private Sub Add_Click()
    Dim colh As Long
    Dim Target As Range

    With Worksheets("Sheet1")
        Set Target = .Range("A" & .Rows.Count).End(xlUp).Offset(1)
    End With

    For colh = 1 To 3
        Target.Cells(1, colh) = Controls("TextBox" & colh).Value
    Next colh
End Sub

Method 2

This is how I would do it. I define a second sub that takes a ParamArray array of Values that I write to the Target Range. In this way I am able to give the controls meaningful names.

Private Sub btnAddMethod2_Click()
   AddRecord txtName.Value, txtAge.Value, txtPhoneNumber.Value
End Sub

Sub AddRecord(ParamArray Values())
    Dim Target As Range
    'Define Target Range
    With Worksheets("Sheet1")
        Set Target = .Range("A" & .Rows.Count).End(xlUp).Offset(1)
    End With

    Target.Resize(1, UBound(Values) + 1).Value = Values
End Sub

enter image description here

Upvotes: 1

Jem Eripol
Jem Eripol

Reputation: 225

Just improved Michal's answer, but i added a search criteria for the last row with input such that you can add as much input you want without compromising your previous inputs.

 Private Sub Add_Click()

    Dim colh As Integer

    'This is to find the last row in your input sheet1 (?) whichever sheet you are doing input.
    Dim iRow as Long
    Dim ws as Worksheets("Sheet1") 'whatever name of sheet you have.
    iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
       SearchDirection:=xlPrevious, LookIn:=xlValues).row + 1

    For colh = 1 To 3
        ws.Cells(iRow , colh).Value = Controls("TextBox" & colh).Value
    Next colh

End Sub

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37367

Change your nested loop:

For colh = 0 To 2
    For roww = 0 To 2
        Range("A2").Offset(colh, roww).Value = Controls("TextBox" & roww + 1).Value
    Next roww
Next colh

to simple loop:

For colh = 0 To 2
    Range("A2").Offset(colh, roww).Value = Controls("TextBox" & colh + 1).Value
Next colh

BUT, before that loop, you must specify roww (row offset). Now, clicking button will fill only one row.

Upvotes: 1

Related Questions