luizmachado
01-16-2016, 06:49 AM
I have this code that should send all the informations I gave on the userform (previouly opened with a button) to the right place on the last row.
For example,
First entry (row 23: first "last row"):
I click on "Inserir dados" (Fill userform) > it opens the userform > I fill the textbox beside "Descricao" with "test1" and click submit > The code shall make this "test1" goes to C & lastrow -1
All the entries ahead (it may be limited, for example, from row 24 -second row- to row 20000 -I probaly won't reach this many rows-):
I click on "Inserir dados" (Fill userform) > it opens the userform > I fill the textbox beside "Descricao" with "test2" and click submit > The code shall create another row and then make this "test2" goes to C & lastrow -1
I've tried everything and don't know what to do. Do you guys know what I can do to solve it?
This is the code (actually there's much more than just that. I simplified in order to make the tests easier) - I can't post the code on tags (it gets messed up):
Private Sub submit_Click()
Dim lrTest As Long
'Count last row
lrTest = Sheets("Monthly Expenses").Range("B" & Rows.Count).End(xlUp).Row
If lrTest = 23 Then
'Description TextBox
Range("C" & lrTest - 1).value = TextBox1.Text
'Resize table row from B21 to J on the last row
ActiveSheet.ListObjects("Table1").Resize Range("$B$21:$J$" & lrTest + 1)
'Select A1
Range("A1").Select
Else
'Count last row
lrTest = Sheets("Monthly Expenses").Range("B" & Rows.Count).End(xlUp).Row
'Description TextBox
Range("C" & lrTest - 1).value = TextBox1.Text
'Resize table row from B21 to J on the last row
ActiveSheet.ListObjects("Table1").Resize Range("$B$21:$J$" & lrTest + 1)
'Select A1
Range("A1").Select
End If
End Sub
Private Sub UserForm_Initialize()
'Clear category and add options to category
cmbcategoria.Clear
With cmbcategoria
.AddItem "Casa"
.AddItem "Lazer"
.AddItem "Mercado"
.AddItem "Saúde e Higiene"
.AddItem "Servicos"
.AddItem "Snoopy e Bob"
.AddItem "Suporte"
.AddItem "Vestuário"
.AddItem "Outros"
End With
End Sub
Private Sub cancel_Click()
'Close UserForm
Unload Me
End Sub
This is how my workbook looks by now:
Since I can't upload any image, I'm linking it below.
i.imgsafe.org/eb90f7b.png
Ps: I can't link the other posts either, the only thing I can do is tell you that I have 2 more posts on "ExcelForum" and "MrExcel" with the same title.
For example,
First entry (row 23: first "last row"):
I click on "Inserir dados" (Fill userform) > it opens the userform > I fill the textbox beside "Descricao" with "test1" and click submit > The code shall make this "test1" goes to C & lastrow -1
All the entries ahead (it may be limited, for example, from row 24 -second row- to row 20000 -I probaly won't reach this many rows-):
I click on "Inserir dados" (Fill userform) > it opens the userform > I fill the textbox beside "Descricao" with "test2" and click submit > The code shall create another row and then make this "test2" goes to C & lastrow -1
I've tried everything and don't know what to do. Do you guys know what I can do to solve it?
This is the code (actually there's much more than just that. I simplified in order to make the tests easier) - I can't post the code on tags (it gets messed up):
Private Sub submit_Click()
Dim lrTest As Long
'Count last row
lrTest = Sheets("Monthly Expenses").Range("B" & Rows.Count).End(xlUp).Row
If lrTest = 23 Then
'Description TextBox
Range("C" & lrTest - 1).value = TextBox1.Text
'Resize table row from B21 to J on the last row
ActiveSheet.ListObjects("Table1").Resize Range("$B$21:$J$" & lrTest + 1)
'Select A1
Range("A1").Select
Else
'Count last row
lrTest = Sheets("Monthly Expenses").Range("B" & Rows.Count).End(xlUp).Row
'Description TextBox
Range("C" & lrTest - 1).value = TextBox1.Text
'Resize table row from B21 to J on the last row
ActiveSheet.ListObjects("Table1").Resize Range("$B$21:$J$" & lrTest + 1)
'Select A1
Range("A1").Select
End If
End Sub
Private Sub UserForm_Initialize()
'Clear category and add options to category
cmbcategoria.Clear
With cmbcategoria
.AddItem "Casa"
.AddItem "Lazer"
.AddItem "Mercado"
.AddItem "Saúde e Higiene"
.AddItem "Servicos"
.AddItem "Snoopy e Bob"
.AddItem "Suporte"
.AddItem "Vestuário"
.AddItem "Outros"
End With
End Sub
Private Sub cancel_Click()
'Close UserForm
Unload Me
End Sub
This is how my workbook looks by now:
Since I can't upload any image, I'm linking it below.
i.imgsafe.org/eb90f7b.png
Ps: I can't link the other posts either, the only thing I can do is tell you that I have 2 more posts on "ExcelForum" and "MrExcel" with the same title.