Userform Unload Data onto Two Worksheets

[coliervile]

I want the userform "frmRequest" to unload the data from its comboboxes and testboxes onto two (2) seperate worksheets "Master" and "Leave Request" at the same time. This coding works for the worksheet "Leave Request", but can't get it to work with both worksheet "Master" and "Leave Request". Your help would br appreciated.

[VBA]Private Sub cmdEmpAdd_Click()
Dim strLastRow As Integer
'Get last row
strLastRow = xlLastRow("Leave Request")

Application.EnableEvents = False

With Sheets("Leave Request")

'If textboxes not null then fill data of textboxes to worksheet.
If (frmEmpRequest.cboEmpName.Value <> vbNullString And frmEmpRequest.cboEmpType.Value <> vbNullString And _
frmEmpRequest.txtEmpStart.Value <> vbNullString And frmEmpRequest.txtEmpEnd.Value <> vbNullString) Then

.Cells(strLastRow + 1, 1).Value = frmEmpRequest.cboEmpName.Value
.Cells(strLastRow + 1, 2).Value = Format(Now, "mmm-dd-yyyy hh:mm:ss")
.Cells(strLastRow + 1, 3).Value = frmEmpRequest.cboEmpType.Value
.Cells(strLastRow + 1, 4).Value = CDate(frmEmpRequest.txtEmpStart.Text)
.Cells(strLastRow + 1, 5).Value = CDate(frmEmpRequest.txtEmpEnd.Text)
strLastRow = strLastRow + 1

'Update listbox with added values
frmEmpRequest.lstEmpBox.RowSource = "'Leave Request'!A2:E" & strLastRow

'Empty textboxes
frmEmpRequest.cboEmpName.Value = vbNullString
frmEmpRequest.cboEmpType.Value = vbNullString
frmEmpRequest.txtEmpStart.Value = vbNullString
frmEmpRequest.txtEmpEnd.Value = vbNullString

Else

MsgBox "Please Enter Data"
End If
End With

Application.EnableEvents = True

End Sub[/VBA]

[lucas]

Charlie, did you try just copying the with statement from with to end with.

past it in just below the other with statement and change the sheet name in the second with statement to Master...

[VBA]With Sheets("Master")[/VBA]

[BreakfastGuy]

Or you could put this above the first WITH statement to read:
[VBA]
For Each Sheet In Sheets(Array("Master", "Leave Request"))
With Sheet
'REST OF THE CODE
End With
Next Sheet
[/VBA]

[coliervile]

Thank you "lucas" and BreakfastGuy for your rsponses. "lucas" I did try what you suggested...

Charlie, did you try just copying the with statement from with to end with.

past it in just below the other with statement and change the sheet name in the second with statement to Master...

I've tried both of your suggestions and the problem seems to be that the data from the userform "frmRequest" unloads onto the first worksheet "Leave Request" before it get's to the second sheet 'Master" and doesn't unload the data on the worksheet "Master".

[lucas]

Charlie, I don't have any time right now but I just noticed that you are clearing your textboxes etc....for instance this part:
[VBA]'Empty textboxes
frmEmpRequest.cboEmpName.Value = vbNullString
frmEmpRequest.cboEmpType.Value = vbNullString
frmEmpRequest.txtEmpStart.Value = vbNullString
frmEmpRequest.txtEmpEnd.Value = vbNullString
[/VBA]

Why are you doing that if the userform is closing when you are done.....
If it has to stay then you will have to put all of your code to copy to each of the sheets before the code to clear your controls.......

what is happening is that your second with statement is not finding any data to copy because it has been cleared by the code from the first with statement.

You are also going to have to address the lastrow problem as it is currently set up to look on the leave request sheet to find the last row....

just a couple of things for you to work on until someone has time to help more......I will come back when I can to see how you are doing.

[coliervile]

Thanks "lucas" for your suggestions. I took your ideas and came up with this coding that seems to work...the coding in Blue is what I did...thanks "lucas"

[VBA]Private Sub cmdAdd_Click()
Dim strLastRow As Integer
'Get last row
strLastRow = xlLastRow("Leave Request")
strLastRow = xlLastRow("Master")

Application.EnableEvents = False

With Sheets("Leave Request")

.Range("A:E").Sort Key1:=.Range("D2"), Order1:=xlAscending, _
Key2:=.Range("B2"), Order2:=xlAscending, _
Header:=xlYes

'If textboxes not null then fill data of textboxes to worksheet.
If (frmRequest.cboName.Value <> vbNullString And frmRequest.cboType.Value <> vbNullString And _
frmRequest.txtStart.Value <> vbNullString And frmRequest.txtEnd.Value <> vbNullString) Then

.Cells(strLastRow + 1, 1).Value = frmRequest.cboName.Value
.Cells(strLastRow + 1, 2).Value = Format(Now, "mmm-dd-yyyy hh:mm:ss")
.Cells(strLastRow + 1, 3).Value = frmRequest.cboType.Value
.Cells(strLastRow + 1, 4).Value = CDate(frmRequest.txtStart.Text)
.Cells(strLastRow + 1, 5).Value = CDate(frmRequest.txtEnd.Text)
strLastRow = strLastRow + 1

'Update listbox with added values
frmRequest.ListBox1.RowSource = "'Leave Request'!A2:E" & strLastRow

Else

MsgBox "Please Enter Data"
End If
.Range("A:E").Sort Key1:=.Range("D2"), Order1:=xlAscending, _
Key2:=.Range("B2"), Order2:=xlAscending, _
Header:=xlYes

End With

With Sheets("Master")

.Range("A:E").Sort Key1:=.Range("D2"), Order1:=xlAscending, _
Key2:=.Range("B2"), Order2:=xlAscending, _
Header:=xlYes

'If textboxes not null then fill data of textboxes to worksheet.
If (frmRequest.cboName.Value <> vbNullString And frmRequest.cboType.Value <> vbNullString And _
frmRequest.txtStart.Value <> vbNullString And frmRequest.txtEnd.Value <> vbNullString) Then

.Cells(strLastRow + 1, 1).Value = frmRequest.cboName.Value
.Cells(strLastRow + 1, 2).Value = Format(Now, "mmm-dd-yyyy hh:mm:ss")
.Cells(strLastRow + 1, 3).Value = frmRequest.cboType.Value
.Cells(strLastRow + 1, 4).Value = CDate(frmRequest.txtStart.Text)
.Cells(strLastRow + 1, 5).Value = CDate(frmRequest.txtEnd.Text)
strLastRow = strLastRow + 1

'Update listbox with added values
frmRequest.ListBox1.RowSource = "'Leave Request'!A2:E" & strLastRow

'Empty textboxes
frmRequest.cboName.Value = vbNullString
frmRequest.cboType.Value = vbNullString
frmRequest.txtStart.Value = vbNullString
frmRequest.txtEnd.Value = vbNullString
End If

.Range("A:E").Sort Key1:=.Range("D2"), Order1:=xlAscending, _
Key2:=.Range("B2"), Order2:=xlAscending, _
Header:=xlYes

End With
Application.EnableEvents = True
End Sub[/VBA]

[lucas]

Looking pretty good Charlie.
Are you preceding your module with Option Explicit? I think the following would throw an error if you were.

I would take a look at this as I am not sure if it is doing what you want it to do.
[VBA]strLastRow = xlLastRow("Leave Request")
strLastRow = xlLastRow("Master")
[/VBA]
you may not need the second line at all......not sure.....

You might try changing the second one's name like:
[VBA]strLastRow = xlLastRow("Leave Request")
strLastRow2 = xlLastRow("Master")
[/VBA]
Then you will need to change where it is called in the second sheet code.....your blue code above. Anywhere that you see strLastRow you would want to change it to strLastRow2

I have no way to test this so I am guessing about all of it. Glad you got it working for you though.

[Simon Lloyd]

i haven't tested this of course but it's a little tidier:
[VBA]
Private Sub cmdEmpAdd_Click()
Dim strLastRow As Integer
For Each Sheet In Sheets(Array("Master", "Leave Request"))
With Sheet
'Get last row
strLastRow = xlLastRow(Sheet)
Application.EnableEvents = False

'If textboxes not null then fill data of textboxes to worksheet.
If (frmEmpRequest.cboEmpName.Value <> vbNullString And frmEmpRequest.cboEmpType.Value <> vbNullString And _
frmEmpRequest.txtEmpStart.Value <> vbNullString And frmEmpRequest.txtEmpEnd.Value <> vbNullString) Then

.Cells(strLastRow + 1, 1).Value = frmEmpRequest.cboEmpName.Value
.Cells(strLastRow + 1, 2).Value = Format(Now, "mmm-dd-yyyy hh:mm:ss")
.Cells(strLastRow + 1, 3).Value = frmEmpRequest.cboEmpType.Value
.Cells(strLastRow + 1, 4).Value = CDate(frmEmpRequest.txtEmpStart.Text)
.Cells(strLastRow + 1, 5).Value = CDate(frmEmpRequest.txtEmpEnd.Text)
strLastRow = strLastRow + 1

'Update listbox with added values
frmEmpRequest.lstEmpBox.RowSource = "'Leave Request'!A2:E" & strLastRow
Else

MsgBox "Please Enter Data"
End If
End With
Next Sheet
'Empty textboxes
frmEmpRequest.cboEmpName.Value = vbNullString
frmEmpRequest.cboEmpType.Value = vbNullString
frmEmpRequest.txtEmpStart.Value = vbNullString
frmEmpRequest.txtEmpEnd.Value = vbNullString

Application.EnableEvents = True

End Sub
[/VBA]

[coliervile]

Thanks "lucas". I do have the coding preceded with "Option Explicit" and the coding is working without an error . I've left the coding the way it is for now...

[VBA]strLastRow = xlLastRow("Leave Request")
strLastRow = xlLastRow("Master")[/VBA]

Just for my edificaton why would preceding the coding with "Option Explicit" cause error with the above code???

[lucas]

[vba]strLastRow = xlLastRow("Leave Request")
strLastRow = xlLastRow("Master")[/vba]

Just for my edificaton why would preceding the coding with "Option Explicit" cause error with the above code???

I thought it might throw a duplicate name error....but I guess not.....

[lucas]

Simon has taken the time to show you how to put your sheets in an array Charlie.....you should look at that option....just for the learning experience if nothing else.

[coliervile]

Thanks Simon Lloyd for your suggestion. I did run your code and it did not unload the userform data to either worksheet. I'll attach the workbook so that you can run it to see what may be hanging it up. I didn't include your coding in the workbook you'll have to make that change...

[Simon Lloyd]

Try this, i had a look at your workbook but don't know what i am supposed to be doing with it or how to operate it....that said you have other problems in your code(s) where you have Dot qualifiers that you do not need, anway try this one as i added .name to xlLastRow(Sheet).
[VBA]
Private Sub cmdEmpAdd_Click()
Dim Sheet As Worksheet
Dim strLastRow As Integer
For Each Sheet In Sheets(Array("Master", "Leave Request"))
With Sheet
'Get last row
strLastRow = xlLastRow(Sheet.Name)
Application.EnableEvents = False

'If textboxes not null then fill data of textboxes to worksheet.
If (frmEmpRequest.cboEmpName.Value <> vbNullString And frmEmpRequest.cboEmpType.Value <> vbNullString And _
frmEmpRequest.txtEmpStart.Value <> vbNullString And frmEmpRequest.txtEmpEnd.Value <> vbNullString) Then

.Cells(strLastRow + 1, 1).Value = frmEmpRequest.cboEmpName.Value
.Cells(strLastRow + 1, 2).Value = Format(Now, "mmm-dd-yyyy hh:mm:ss")
.Cells(strLastRow + 1, 3).Value = frmEmpRequest.cboEmpType.Value
.Cells(strLastRow + 1, 4).Value = CDate(frmEmpRequest.txtEmpStart.Text)
.Cells(strLastRow + 1, 5).Value = CDate(frmEmpRequest.txtEmpEnd.Text)
strLastRow = strLastRow + 1

'Update listbox with added values
frmEmpRequest.lstEmpBox.RowSource = "'Leave Request'!A2:E" & strLastRow
Else

MsgBox "Please Enter Data"
End If
End With
Next Sheet
'Empty textboxes
frmEmpRequest.cboEmpName.Value = vbNullString
frmEmpRequest.cboEmpType.Value = vbNullString
frmEmpRequest.txtEmpStart.Value = vbNullString
frmEmpRequest.txtEmpEnd.Value = vbNullString

Application.EnableEvents = True

End Sub
[/VBA]

[coliervile]

Simon Lloyd that coding did work and it's a lot more clean and condense than what I had...the difference between a "rookie (me) and someone much better at these issues (you). My workbook tracks employees request for time off from work. There are two main userforms, one for employee usage and the other is for administration type functions (correcting errors, delete requests, editing request, etc.) I'm open to any ideas that make a better product. I would appreciate not only yours but others as well. I hope others can use what I've come up with too.

[Simon Lloyd]

Phew! glad your sorted!

"rookie (me) and someone much better at these issues (you).

for a rookie your workbook is very advanced, and as for me being better....well i only get there through trial and error as i don't have the skill to just knock out some wonderful or efficient code, i don't use excel professionally just got caught up in "fixing" something for work once!

Best of luck!

[coliervile]

Can you explain a bit more about this comment...

that said you have other problems in your code(s) where you have Dot qualifiers that you do not need

Thanks for your complement about my workbook. The workbook are other codes written for other projects that I've adapted for what I needed. There were also a lot of times when I ran into problems and have had the assistance of others like yourself on VBAX that have helped me work through them... "XLD", "lucas", yourself and others THANK YOU.