Click here to Skip to main content
15,867,835 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Having created a VBA with 2 TextBox and 3 OptionButton, I would like when I press the "save" button to enter the contents inside the Excel cells.

So far everything is okay, the problem arises when I would like to insert more than one item.
So it goes to replace what I have already included (rightly from the code I wrote).

My Code now:

VB
If Me.TextBox1.Text = "" Then
MsgBox ("Cannot Be Empty!")
Else
Range ("A3").Value = TextBox1.Text
End If
If Me.TextBox2.Text= "" Then
MsgBox ("Cannot Be Empty!")
Else
Range ("B3").Value = TextBox2.Text
End If


What I have tried:

VB
Private Sub CommandButton1_Click()
Dim lRow As Long
If Me.TextBox1.Text = "" Then
    MsgBox ("Cannot Be Empty!")
Else
    For lRow = 3 To 100
        Worksheets("Sheet1").Range("A" & lRow).Value = TextBox1.Text
    Next lRow
End If
If Me.TextBox2.Text = "" Then
    MsgBox ("Cannot Be Empty!")
Else
    For lRow = 3 To 100
        Worksheets("Sheet1").Range("B" & lRow).Value = TextBox2.Text
    Next lRow
End If
End Sub


I tried too:

VB
Range ("A3").Value= Range ("A3").Value + 1
Posted
Updated 28-Jan-19 4:55am
v2
Comments
Richard MacCutchan 25-Jan-19 4:47am    
what exactly is the problem?
Atipos 25-Jan-19 4:58am    
I would like to be able to insert more data in more cells, in progression.

Ex .:
TextBox1: Hello
TextBox2: Test
Press the Save button;

Except for Hello in cell A3 and Test in cell B3,

I would like to insert later
TextBox1: Test
TextBox2: LoL
Press the Save button;

Except for Test in cell A4 and LoL in cell B4.
Richard MacCutchan 25-Jan-19 5:19am    
So what is the actual problem that prevents you from doing it?
Atipos 25-Jan-19 5:21am    
Having tried some ways of code but no one to give me that result.
The fact that range ("A3") should increase when i pressed the Save Button.

1 solution

You could use the Range.Offset property[^]
Keep track of which column you are up to and just increase the column offset by that number. It's the address you want to increase, not the Value
E.g.
VB
If Me.TextBox1.Text = "" Then
    MsgBox ("Cannot Be Empty!")
Else
    For lRow = 3 To 100
        Worksheets("Sheet1").Range("A1").Offset(lRow - 1, 0).Value = TextBox1.Text
    Next lRow
End If
If Me.TextBox2.Text = "" Then
    MsgBox ("Cannot Be Empty!")
Else
    For lRow = 3 To 100
        Worksheets("Sheet1").Range("A1").Offset(lRow - 1, 1).Value = TextBox2.Text
    Next lRow
End If

Note the use of lRow - 1 and not lRow .. because we are offsetting from our start point, not using an absolute address.

You could also use the AutoFill [^] method here instead of the loops
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900