dumping contents of UserForm into next avail row

draconus

New Member
Joined
Oct 27, 2002
Messages
12
This is silly I know, I have looked around various resources but I can not find what i need to do to dump the contents of a Userform into the next available row in a spreadsheet. I would have thought this was a basic function, but I am obviously looking in the wrong places. Please help with a statement or pointer to a resource.
TIA
Mal 'going-grey-quickly' Thomas
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
In VBA:

Range("A65536").End(xlUp).Offset(1,0)

returns the next empty cell in column A.
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Mal,

As regards finding the next available row, try this routine from Dave Hawley's site for finding the last row, then offset by one:

<pre>Sub FindLastRow()
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
MsgBox LastRow
End If
End Sub</pre>

With regards to 'dumping' info from a Userform, I assume that you mean values from textboxes. Try something like this in the CommandButton1_Click code:
ThisWorkbook.Sheets(1).Range("A1").Value = TextBox1.Value

Its probably best to find your next available row first and then loop through your textboxes incrementing the row destination with each new textbox.

HTH
 

draconus

New Member
Joined
Oct 27, 2002
Messages
12
magic ! all good tips, thanks very much, folks. I now think I am on the correct path to glory !

Cheers
Mal
Hong Kong
 

ElectricSkywalker

Board Regular
Joined
May 27, 2002
Messages
112
I am in the same boat as you...I have been creating a userform in Excel...and then placing the data into a Excel Spreadsheet...so I can do lookups on it.

This is the code I have been using:


Private Sub bnOK_Click()
'Open the Storage Sheet for the Raw Data
Sheets("Storage").Activate
'Find the Next Row
NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1
'Add the entered data to the next avaliable line
Cells(NextRow, 1) = txtFirstName.Text
Cells(NextRow, 2) = txtSurname.Text
Cells(NextRow, 3) = cbDeptarment.Value
Cells(NextRow, 4) = cbManager.Value
Cells(NextRow, 5) = lbSunday.Value

' Add in the Code
Cells(NextRow, 11) = "SARS"

Cells(NextRow, 12) = txtSARS1.Text
Cells(NextRow, 13) = txtSARS2.Text
Cells(NextRow, 14) = txtSARS3.Text
Cells(NextRow, 15) = txtSARS4.Text
Cells(NextRow, 16) = txtSARS5.Text


Cells(NextRow, 42) = txtSARSCOMMENT.Text



'Clear all the Entries Back to Blank
txtFirstName.Text = ""
txtSurname.Text = ""
cbDeptarment.Value = ""
cbManager.Value = ""
lbSunday.Value = ""
txtSARS1.Text = ""
txtSARS2.Text = ""
txtSARS3.Text = ""
txtSARS4.Text = ""
txtSARS5.Text = ""

txtSARSCOMMENT.Text = ""

'Set the data entry focus back to the first entry
txtFirstName.SetFocus

End Sub


Private Sub bnCancel_Click()

Unload Me
End Sub
 

draconus

New Member
Joined
Oct 27, 2002
Messages
12
Thanks for all the help. I have now got a beautiful looking data entry page. My operators now love me !

Cheers
Mal
Hong Kong
 

Forum statistics

Threads
1,144,765
Messages
5,726,164
Members
422,659
Latest member
RGP268

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top