Can I populate two different workbooks from one userform?

B

benchwarm

New Member
Joined
Dec 3, 2014
Messages
2
I'm new to vba so please forgive my ignorance. I am using Excel 2010 on Windows 7.

I have created a custom userform in a workbook "Assistant Hire Log - Individual". The userform works correctly in populating sheet 1 "Hire Log".
Upon pushing the "OK" button, I would like the userform to also populate a different workbook "Assistant Hire Log - Universal" which also has a sheet one "Hire Log". I have found some posts regarding updating two different worksheets form one userform, but is it possible to update two different workbooks from one userform. In advance, thank you so much for any help...

Current code listed below:
Private Sub butOK_Click()
Dim iRow1 As Long
Dim iRow2 As Long
Dim WS1 As Worksheet, WS2 As Worksheet
Set WS1 = Worksheets("Assistant Hire Log - Individual")
Set WS2 = Worksheets("Assistant Hire Log - Universal")

'Find first empty row in database
iRow1 = WS1.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row

'Find first empty row in database
iRow2 = WS2.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row

'Check user input
If Me.txtFirstName.Value = "" Then
MsgBox "Please enter a First Name.", vbexlamation, "Hire Log"
Me.txtFirstName.SetFocus
Exit Sub
End If
If Me.txtLastName.Value = "" Then
MsgBox "Please enter a Last Name.", vbexlamation, "Hire Log"
Me.txtLastName.SetFocus
Exit Sub
End If
If Me.txtEmail.Value = "" Then
MsgBox "Please enter an Email Address.", vbexlamation, "Hire Log"
Me.txtEmail.SetFocus
Exit Sub
End If
If Me.txtIDNo.Value = "" Then
MsgBox "Please enter an ID Number.", vbexlamation, "Hire Log"
Me.txtIDNo.SetFocus
Exit Sub
End If
If Me.txtEIDNo.Value = "" Then
MsgBox "Please enter an EID Number.", vbexlamation, "Hire Log"
Me.txtEIDNo.SetFocus
Exit Sub
End If
'Write data to database "Assistant Hire Log - Individual"
WS1.Cells(iRow1, 0).Value = Me.txtFirstName.Value
WS1.Cells(iRow1, 1).Value = Me.txtLastName.Value
WS1.Cells(iRow1, 2).Value = Me.txtIDNo.Value
WS1.Cells(iRow1, 3).Value = Me.txtEIDNo.Value
WS1.Cells(iRow1, 4).Value = Me.txtEmail.Value
WS1.Cells(iRow1, 5).Value = Me.CombHireType.Value
WS1.Cells(iRow1, 6).Value = Me.CombHireTypeII.Value
WS1.Cells(iRow1, 7).Value = Me.ComboStatus.Value
WS1.Cells(iRow1, 8).Value = Me.ComboStatusII.Value
WS1.Cells(iRow1, 9).Value = Me.txtLR.Value
WS1.Cells(iRow1, 10).Value = Me.txtJobTitle.Value
WS1.Cells(iRow1, 11).Value = Me.txtCC.Value
WS1.Cells(iRow1, 12).Value = Me.txtDepartment.Value
WS1.Cells(iRow1, 13).Value = Me.txtManager.Value
WS1.Cells(iRow1, 14).Value = Me.CombHourly.Value
WS1.Cells(iRow1, 15).Value = Me.txtNotes.Value
WS1.Cells(iRow1, 16).Value = Format(Me.txtDateReceived.Value, "MM/DD/YY")
WS1.Cells(iRow1, 17).Value = Format(Me.txtEffectiveDate.Value, "MM/DD/YY")
WS1.Cells(iRow1, 18).Value = Format(Me.txtBGStart.Value, "MM/DD/YY")
WS1.Cells(iRow1, 19).Value = Format(Me.txtBGClear.Value, "MM/DD/YY")
WS1.Cells(iRow1, 20).Value = Format(Me.txtPhysical.Value, "MM/DD/YY")
WS1.Cells(iRow1, 21).Value = Format(Me.txtFinal.Value, "MM/DD/YY")
WS1.Cells(iRow1, 22).Value = Format(Me.txtHRISdate.Value, "MM/DD/YY")
WS1.Cells(iRow1, 24).Value = Me.ComboEntity.Value
WS1.Cells(iRow1, 25).Value = Me.TxtInitials.Value
WS1.Cells(iRow1, 29).Value = Format(Now, "dd/mm/yyyy hh:nn:ss")
'Write data to database "Assistant Hire Log - Universal"
WS2.Cells(iRow2, 0).Value = Me.txtFirstName.Value
WS2.Cells(iRow2, 1).Value = Me.txtLastName.Value
WS2.Cells(iRow2, 2).Value = Me.txtIDNo.Value
WS2.Cells(iRow2, 3).Value = Me.txtEIDNo.Value
WS2.Cells(iRow2, 4).Value = Me.txtEmail.Value
WS2.Cells(iRow2, 5).Value = Me.CombHireType.Value
WS2.Cells(iRow2, 6).Value = Me.CombHireTypeII.Value
WS2.Cells(iRow2, 7).Value = Me.ComboStatus.Value
WS2.Cells(iRow2, 8).Value = Me.ComboStatusII.Value
WS2.Cells(iRow2, 9).Value = Me.txtLR.Value
WS2.Cells(iRow2, 10).Value = Me.txtJobTitle.Value
WS2.Cells(iRow2, 11).Value = Me.txtCC.Value
WS2.Cells(iRow2, 12).Value = Me.txtDepartment.Value
WS2.Cells(iRow2, 13).Value = Me.txtManager.Value
WS2.Cells(iRow2, 14).Value = Me.CombHourly.Value
WS2.Cells(iRow2, 15).Value = Me.txtNotes.Value
WS2.Cells(iRow2, 16).Value = Format(Me.txtDateReceived.Value, "MM/DD/YY")
WS2.Cells(iRow2, 17).Value = Format(Me.txtEffectiveDate.Value, "MM/DD/YY")
WS2.Cells(iRow2, 18).Value = Format(Me.txtBGStart.Value, "MM/DD/YY")
WS2.Cells(iRow2, 19).Value = Format(Me.txtBGClear.Value, "MM/DD/YY")
WS2.Cells(iRow2, 20).Value = Format(Me.txtPhysical.Value, "MM/DD/YY")
WS2.Cells(iRow2, 21).Value = Format(Me.txtFinal.Value, "MM/DD/YY")
WS2.Cells(iRow2, 22).Value = Format(Me.txtHRISdate.Value, "MM/DD/YY")
WS2.Cells(iRow2, 24).Value = Me.ComboEntity.Value
WS2.Cells(iRow2, 25).Value = Me.TxtInitials.Value
WS2.Cells(iRow2, 29).Value = Format(Now, "dd/mm/yyyy hh:nn:ss")
'Clear the form
For Each Ctl In Me.Controls
If TypeName(Ctl) = "TextBox" Or TypeName(Ctl) = "ComboBox" Then
Ctl.Value = ""
ElseIf TypeName(Ctl) = "OptionButton" Then
Ctl.Value = False
End If
Next Ctl
End Sub
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Hi and Welcome to MrExcel,

A relatively quick fix using your existing code would be to utilize a Sub that writes the record to the Worksheet that is passed to the Sub as an argument.
Then have your button code call that Sub for each Worksheet (the references include each respective Workbook).

Untested framework....
Code: 
Private Sub butOK_Click()
 
 'Check user input
 If Me.txtFirstName.Value = "" Then
   MsgBox "Please enter a First Name.", vbexlamation, "Hire Log"
   Me.txtFirstName.SetFocus
   Exit Sub
 End If
 '....
 '....your code
 '....
 
 Call WriteRecord(WS1:= _
   Workbooks("Assistant Hire Log - Individual.xlsm").Sheets("Hire Log"))
   
 Call WriteRecord(WS1:= _
   Workbooks("Assistant Hire Log - Universal.xlsm").Sheets("Hire Log"))
 
 'Clear the form
 For Each Ctl In Me.Controls
   If TypeName(Ctl) = "TextBox" Or TypeName(Ctl) = "ComboBox" Then
      Ctl.Value = ""
   ElseIf TypeName(Ctl) = "OptionButton" Then
      Ctl.Value = False
   End If
 Next Ctl
 
End Sub

Private Sub WriteRecord(WS1 As Worksheet)
 Dim iRow1 As Long
 
 'Find first empty row in database
 
 iRow1 = WS1.Cells(Rows.Count, 2) _
   .End(xlUp).Offset(1, 0).Row
 
 'Write data to database "Assistant Hire Log - Individual"
 WS1.Cells(iRow1, 0).Value = Me.txtFirstName.Value
 WS1.Cells(iRow1, 1).Value = Me.txtLastName.Value
 '....your code
 '....
 WS1.Cells(iRow1, 29).Value = Format(Now, "dd/mm/yyyy hh:nn:ss")

End Sub

Best practice coding for UserForms tend to limit the code in the UserForm module to what is needed for the User Interface.

So a better but more advanced approach would be for the UserForm to pass the Record data to a Sub or Function in a standard code module which would contain the logic of how to write the record to worksheets.

Passing a large number of arguments to a Sub or Function is cumbersome, so that approach would probably be coupled with the use of a Class or User Defined Type to hold the values for each field of a single record.
 
Upvote 0
Jerry Sullivan said:
Hi and Welcome to MrExcel,

A relatively quick fix using your existing code would be to utilize a Sub that writes the record to the Worksheet that is passed to the Sub as an argument.
Then have your button code call that Sub for each Worksheet (the references include each respective Workbook).

Untested framework....
Code: 
Private Sub butOK_Click()
 
 'Check user input
 If Me.txtFirstName.Value = "" Then
   MsgBox "Please enter a First Name.", vbexlamation, "Hire Log"
   Me.txtFirstName.SetFocus
   Exit Sub
 End If
 '....
 '....your code
 '....
 
 Call WriteRecord(WS1:= _
   Workbooks("Assistant Hire Log - Individual.xlsm").Sheets("Hire Log"))
   
 Call WriteRecord(WS1:= _
   Workbooks("Assistant Hire Log - Universal.xlsm").Sheets("Hire Log"))
 
 'Clear the form
 For Each Ctl In Me.Controls
   If TypeName(Ctl) = "TextBox" Or TypeName(Ctl) = "ComboBox" Then
      Ctl.Value = ""
   ElseIf TypeName(Ctl) = "OptionButton" Then
      Ctl.Value = False
   End If
 Next Ctl
 
End Sub

Private Sub WriteRecord(WS1 As Worksheet)
 Dim iRow1 As Long
 
 'Find first empty row in database
 
 iRow1 = WS1.Cells(Rows.Count, 2) _
   .End(xlUp).Offset(1, 0).Row
 
 'Write data to database "Assistant Hire Log - Individual"
 WS1.Cells(iRow1, 0).Value = Me.txtFirstName.Value
 WS1.Cells(iRow1, 1).Value = Me.txtLastName.Value
 '....your code
 '....
 WS1.Cells(iRow1, 29).Value = Format(Now, "dd/mm/yyyy hh:nn:ss")

End Sub

Best practice coding for UserForms tend to limit the code in the UserForm module to what is needed for the User Interface.

So a better but more advanced approach would be for the UserForm to pass the Record data to a Sub or Function in a standard code module which would contain the logic of how to write the record to worksheets.

Passing a large number of arguments to a Sub or Function is cumbersome, so that approach would probably be coupled with the use of a Class or User Defined Type to hold the values for each field of a single record.
Click to expand...




Thank you, I'll give it a go...
 
Upvote 0
I didn't notice this when I cut and pasted your code, but this statement should not work because the Column numbers for a worksheet start at 1 not 0.

Code: 
WS1.Cells(iRow1, 0).Value = Me.txtFirstName.Value

Start instead with ..

Code: 
WS1.Cells(iRow1, 1).Value = Me.txtFirstName.Value
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Opening userform closes excel and can't open other workbooks
Replies
3
Views
838
NHagedorn
N
R
  • Question Question
UserForm Data Validation
2
Replies
17
Views
2K
reberryjr
R
B
  • Question Question
VBA: Find and add data from userform to first empty row in sheet (not working)
Replies
4
Views
2K
Battin
B
B
  • Question Question
Combining VBA Code
2
Replies
10
Views
2K
BLB76
B
C
  • Question Question
Userforms. Adding values to multiple sheets using checkboxes
Replies
3
Views
412
mrmmickle1
mrmmickle1

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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
Back
Top