PDA

View Full Version : [SOLVED] In a userform, applying results to a different workbook



mleblanc001
04-13-2017, 12:33 PM
Hi Gurus,
I created a userform and was able to send the results of the userform to a different worksheet but how can I send the submitted data to a different workbook. I am submitting an example of a userform that I have created. Your help would be greatly appreciated.:yes

mdmackillop
04-14-2017, 02:00 AM
Dim wb As Workbook

Set wb = Workbooks.Open("C:\VBAX\NewData.xlsx")
With wb
Set ws = .Worksheets("Employee Data")
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


'check for a Name number
If Trim(Me.TextBox_Name.Value) = "" Then
Me.TextBox_Name.SetFocus
MsgBox "Please complete the form"
Exit Sub
End If


'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox_Name.Value
ws.Cells(iRow, 2).Value = Me.TextBox_Surname.Value
ws.Cells(iRow, 3).Value = Me.TextBox_Age.Value
ws.Cells(iRow, 4).Value = Me.TextBox_Gender.Value
ws.Cells(iRow, 5).Value = Me.TextBox_Address.Value
ws.Cells(iRow, 6).Value = Me.TextBox_City.Value
ws.Cells(iRow, 7).Value = Me.TextBox_Province.Value
ws.Cells(iRow, 8).Value = Me.TextBox_Postal_Code.Value
End With
wb.Close True

snb
04-14-2017, 03:55 AM
This code suffices:


Sub M_snb()
with getobject("C:\VBAX\NewData.xlsx")
.sheets("Employee Data").cells(rows.count,1).end(xlup).offset(,8)=array(TextBox_Name,TextBox_Surna me,TextBox_Age,TextBox_Gender,TextBox_Address,TextBox_City,TextBox_Province ,TextBox_Postal_Code)
.close -1
end with
End Sub

mleblanc001
04-14-2017, 10:25 AM
I entered your code and it works amazingly. Thank you so much for all your help. I am loading the resulting final workbooks for anybody else which may want to do this type of data entry. I have changed the user-form workbook to "Example of a basic form-changing worksheets-final.xlsm" and the employee data workbook name to "Example of a basic form-changing worksheets-test-receive.xlsx". I've added a few bells and whistles like having a message that the data has been entered and some data validation, etc.