PDA

View Full Version : Userform Help - Adding more than one record.



Trebby
04-19-2015, 02:56 AM
Hi,

I've made a userform with the following code but I can't seem to add more than one entry. For example I add one new record and if adding a second new record it just overrides the first. I would like to go to the next available empty cell. I know in the code I'm stating specific cells (so that is possibly half the problem), is there a way of adding code to look at the next cell if the first contains numbers/text?



Private Sub CommandButton1_Click()
Dim LastRow As Object


Set LastRow = Sheet22.Range("a65536").End(xlUp)


LastRow.Offset(26, 19).Value = TextBox1.Text
LastRow.Offset(26, 20).Value = TextBox2.Text
LastRow.Offset(26, 21).Value = TextBox3.Text


MsgBox "One record added"


response = MsgBox("Do you want to enter another record?", _
vbYesNo)


If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""


TextBox1.SetFocus


Else
Unload Me
End If


End Sub

gmayor
04-19-2015, 05:05 AM
You are adding to columns S, T & U, but referencing column A (which isn't changed by this macro) for the last row. However I wouldn't do it like that. It is better to run the main code outside the userform. So have the following as your userform code:



Option Explicit

Private Sub CommandButton1_Click()
Me.Tag = 1
Me.Hide
lbl_Exit:
Exit Sub
End Sub



Then setup the form in the calling macro e.g.


Option Explicit

Sub AddRecord()
Dim NextRow As Long
Dim Response As Long
Dim oFrm As New UserForm1 'the name of the userform
Dim xlSheet As Worksheet
Set xlSheet = ActiveWorkbook.Sheets("Sheet22") 'The name of the sheet
With xlSheet
NextRow = .Cells(.Rows.Count, "S").End(xlUp).Row + 1 'Reference a column that you are adding to
End With
With oFrm
.TextBox1.Text = ""
.TextBox2.Text = ""
.TextBox3.Text = ""
.Show
If Not .Tag = 1 Then GoTo lbl_Exit
xlSheet.Cells(NextRow, 19) = .TextBox1.Text
xlSheet.Cells(NextRow, 20) = .TextBox2.Text
xlSheet.Cells(NextRow, 21) = .TextBox3.Text
End With
Unload oFrm
Response = MsgBox("One record added. Do you want to enter another record?", _
vbYesNo)
If Response = vbYes Then AddRecord 'run the macro again
lbl_Exit:
Set xlSheet = Nothing
Set oFrm = Nothing
Exit Sub
End Sub

Trebby
04-19-2015, 05:18 AM
Hi - Thanks for helping and replying. Sorry for the noob code to begin with (Still very new to VBA, and pulling code from all sorts). Just so I completely understand. I am to put the first code within the userform and the second with a module?

I get an compile error on .TextBox2.Text = "" stating Method or data member not found?

Update -
I looks like I've around that issue but I'm having the issue of it adding the details half-way down the page. I need it in T27:V32 (this is the area for new details - there won't be more than that block as others will be deleted)



Sub AddRecord()
Dim NextRow As Long
Dim Response As Long
Dim oFrm As New UserForm2 'the name of the userform
Dim xlSheet As Worksheet
Set xlSheet = ActiveWorkbook.Sheets("Overtime") 'The name of the sheet
With xlSheet
NextRow = .Cells(.Rows.Count, "T").End(xlUp).Row + 1 'Reference a column that you are adding to
End With
With oFrm
.TextBox1.Text = ""
.TextBox2.Text = ""
.TextBox3.Text = ""
.Show
If Not .Tag = 1 Then GoTo lbl_Exit
xlSheet.Cells(NextRow, 20) = .TextBox1.Text
xlSheet.Cells(NextRow, 21) = .TextBox2.Text
xlSheet.Cells(NextRow, 22) = .TextBox3.Text
End With
Unload oFrm
Response = MsgBox("One record added. Do you want to enter another record?", _
vbYesNo)
If Response = vbYes Then AddRecord 'run the macro again
lbl_Exit:
Set xlSheet = Nothing
Set oFrm = Nothing
Exit Sub
End Sub

gmayor
04-19-2015, 07:47 AM
You shouldn't actually need the three lines


.TextBox1.Text = ""
.TextBox2.Text = ""
.TextBox3.Text = "" as the userform is reset each time, but the error message suggests you don't have a text box with the name TextBox2.

You have lost me with your comment - I'm having the issue of it adding the details half-way down the page. I need it in T27:V32.
The macro looks for the last used line in column T and adds the values to the next row.
If there is nothing in the column the entries will be at the top of the column. If you want to limit to a particular range (which is what I think you may mean) then you need to tell the macro. The following is one way to do that (without seeing any other issues the worksheet might present):



Option Explicit

Sub AddRecord()
Dim NextRow As Long
Dim Response As Long
Dim oFrm As New UserForm2 'the name of the userform
Dim xlSheet As Worksheet
Set xlSheet = ActiveWorkbook.Sheets("Overtime") 'The name of the sheet
With xlSheet
If .Range("T26") = "" And .Range("T27") = "" Then 'Nothing in the first cell or the preceding cell so set NextRow to the row of the first cell in the range
NextRow = 27
Else
NextRow = .Cells(.Rows.Count, "T").End(xlUp).Row + 1 'Set the next available row
End If
If NextRow > 32 Then
MsgBox ("The usable range is full")
GoTo lbl_Exit
End If
End With
With oFrm
.Show
If Not .Tag = 1 Then GoTo lbl_Exit
xlSheet.Cells(NextRow, 20) = .TextBox1.Text
xlSheet.Cells(NextRow, 21) = .TextBox2.Text
xlSheet.Cells(NextRow, 22) = .TextBox3.Text
End With
Unload oFrm
Response = MsgBox("One record added. Do you want to enter another record?", _
vbYesNo)
If Response = vbYes Then AddRecord 'run the macro again
lbl_Exit:
Set xlSheet = Nothing
Set oFrm = Nothing
Exit Sub
End Sub

Paul_Hossler
04-19-2015, 07:57 AM
Just 2 notes about



Set LastRow = Sheet22.Range("a65536").End(xlUp)


1. A worksheet can have a CodeName and a Name

Using Sheet22. that way uses the Codename and is independent of the Name that you see on the worksheet tab

If you use the Codename in the macro, then it doesn't matter the Name of the worksheet

To refer the worksheet object by its Name, then you'd use Worksheets("Sheet22"). etc.

2. Most likely will not make a difference, but since the number of allowed rows does occasionally change




Sheet22.Cells(Sheet22.Rows.Count, 1).End(xlUp)


or



Worksheets("MyData").Cells(Worksheets("MyData").Rows.Count, 1).End(xlUp)


might be more general

13212

Trebby
04-19-2015, 08:54 AM
error message suggests you don't have a text box with the name TextBox2
I agree, strange as that name was correct. After a little bit of row tweaking, it appeared work.


You have lost me with your comment - I'm having the issue of it adding the details half-way down the page. I need it in T27:V32
It makes complete sense to me now, and adjusted the spreadsheet to suit the code (deleted borders and moved cells around).

You've been a massive help and would like to say thank you for your assistance. The sheet is working perfectly :)