Copying data in a form into an alternative Workbook
 

Hello All,
I have a workbook with a form created in VB that allows data input into
fields, and on command (using a Command Button) will dump the data from the
form into a database on another worksheet in the same workbook, and then
clears form. This all works fine. What I would like is for the data to be
stored into fields on an alternative Excel Workbook as i have many users who
will be entering data in the forms but I also need to consolidate the date
for all users.
Can i do this in the VB script inside the macro?
Copy of my script for the form Below.

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")

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

'check for a Date
If Trim(Me.txtDate.Value) = "" Then
Me.txtDate.SetFocus
MsgBox "You must enter a date"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtDate.Value
ws.Cells(iRow, 2).Value = Me.txtJudge.Value
ws.Cells(iRow, 3).Value = Me.txtCourt.Value
ws.Cells(iRow, 4).Value = Me.txtTimeStart.Value
ws.Cells(iRow, 5).Value = Me.TxtTimeEnd.Value

Copying data in a form into an alternative Workbook
 

Yes, you do it basically the same way you are now moving it to a worksheet,
but add the new workbook and range designation.

example:
Workbooks(2).Cells(iRow, 1).Value = Me.txtDate.Value
Workbooks(2).Cells(iRow, 2).Value = Me.txtJudge.Value
Workbooks(2).Cells(iRow, 3).Value = Me.txtCourt.Value
Workbooks(2).Cells(iRow, 4).Value = Me.txtTimeStart.Value
Workbooks(2).Cells(iRow, 5).Value = Me.TxtTimeEnd.Value

Of course, you would need to define iRow for Workbooks(2), but the idea is
to let VBA know that you want the value in another workbook.

"dave m" wrote:

Hello All,
I have a workbook with a form created in VB that allows data input into
fields, and on command (using a Command Button) will dump the data from the
form into a database on another worksheet in the same workbook, and then
clears form. This all works fine. What I would like is for the data to be
stored into fields on an alternative Excel Workbook as i have many users who
will be entering data in the forms but I also need to consolidate the date
for all users.
Can i do this in the VB script inside the macro?
Copy of my script for the form Below.

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")

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

'check for a Date
If Trim(Me.txtDate.Value) = "" Then
Me.txtDate.SetFocus
MsgBox "You must enter a date"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtDate.Value
ws.Cells(iRow, 2).Value = Me.txtJudge.Value
ws.Cells(iRow, 3).Value = Me.txtCourt.Value
ws.Cells(iRow, 4).Value = Me.txtTimeStart.Value
ws.Cells(iRow, 5).Value = Me.TxtTimeEnd.Value