PDA

View Full Version : Solved: Moving Data from a form to Excel Spreadsheet



Solrac3030
12-02-2008, 04:35 PM
I created a form in a Excel Spreadsheet that opens when the file is open. I tried using the auto form but there are too many fiields and I am not able to use that function. I need to figure out the code to move the data entered by the user on the form to the spreadsheet when they clcik on the Next record button. I also have a file save button that the user would click on to save and close the file. need to figure out the code to give the user the file SaveAs dialog and then close the file once the file is given a name. Don't want the user to overwrite the original file.

Any help would be greatly appreciated.

Thanks.

Ischyros
12-02-2008, 08:39 PM
I am confused a bit by your question? You have a standard userform as an data collection interface for you user, correct? If this is the case then here is an example of some code for a simple userform.

Given you have a userform with two controls on it, TextBox1 and CommandButton1, you can write code to respond to a button click such as

Private sub CommandButton1_Click()

Worksheets("Sheet 1").Range("A1") = TextBox1.Text

End Sub

This will insert whatever is in TextBox1 into Range"A1" for the given sheet. Is this what you are asking?

As for the Save button here is the code. Once you assign this macro to a button the user will be prompted with a dialog box where they can specify the directory address and the filename....the file is then saved where they wanted, named as what they wanted, and then it is closed. The default suggested file name here is the workbooks original name plus "_Copy".

Private Sub Save_Button()

Dim strFileName As String, strRecName As String

strRecName = ThisWorkbook.Name + "_Copy"


strFileName = Application.GetSaveAsFilename(strRecName)


ThisWorkbook.SaveAs strFileName

Thisworkbook.Close

End Sub

Solrac3030
12-03-2008, 12:45 PM
I will do that. I need to figure out how to increment the row it saves in each time the user clicks on the new record button. in other words the form has a set of test boxes that the user fills in. Once the user has entered all the dat for that record the user click on the next record button and the following should happen. What is currently in the text boxes should be added to the first row in the spreadhsheet or I should say the second row since the first row has labels. Once the data is added to the spreadsheet the text boxes are cleared and somehow the setting so that it saves on the next row is changed so that when the next data set is entered in the form and the user click on next record again the data is added to the next row.

On save file the counter would be reset, the file saved and the form unloaded.

I will work on this for now what what you have given me so far. maybe I can come up with something.

lucas
12-03-2008, 12:50 PM
Is this a class project?

MaximS
12-03-2008, 01:09 PM
will be easier to help you if you attach sample workbook.

Solrac3030
12-03-2008, 02:49 PM
Not a class project. Attached is the Spreadsheet

lucas
12-03-2008, 02:55 PM
Check out the attached and see if it helps you understand how to input your data to the next row.....

MaximS
12-03-2008, 03:26 PM
use following code to transfer the data from form to the spreadsheet:


Private Sub cmdNew_Click()
Dim LRow As Long
'This will check for last used row
LRow = Worksheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row
Range("A" & LRow + 1).Value = txtDate.Value
Range("B" & LRow + 1).Value = txtInquiry.Value
'And So on
End Sub

Solrac3030
12-03-2008, 04:25 PM
MaximS, that code worked but when I close the the workbook and go to start a new one it remembers the last row used and starts to enter data on that next row. How do you clear the value so that when you open a new spreasheet it starts at the first empty row, row 2 in this case.

MaximS
12-03-2008, 04:32 PM
try replace code containing "LRow = ...." with:


' Change A for any column which always needs to be filled
LRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

Ischyros
12-03-2008, 05:05 PM
If you just want to workbook recognize to the last empty row when the workbook opens and when you want to add a new record just put this code in the "Workbook" Module and in the "New Record" button macro.....its a bit brute force, but easy quick fix.
-----------
Public intRow As Integer

Private Sub Workbook_Open()
intRow = 1
While Worksheets(1).Cells(intRow, 1) <> ""
intRow = intRow + 1
Wend

'now intRow indexes that next available row for a new record
frmData.Show
End Sub

...now for the form button "next record"
--------------
Private Sub cmdNew_Click()
intRow = 1
While Worksheets(1).Cells(intRow, 1) <> ""
intRow = intRow + 1
Wend

End Sub


now intRow can be u sed to assign values to the correct range/row...and each time you open or create a new record it will be added to the next available row.

Hope this is what you were asking....also the code provided by the other posts works as well!

phendrena
12-04-2008, 02:58 AM
This will find the first empty row in sheet1 and when you click CommandButton1 it'll take the data from textbox1 and textbox2 and place them into the empty cells...

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

' Find first empty row
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

' Copy the data from the form to the worksheet
ws.Cells(iRow, 1).Value = Textbox1.Value
ws.Cells(iRow, 2).Value = Textbox2.Value

End sub


Hope this helps.