Excel Userform Staff Rota - Annual Leave Request Help

J

JayDub85

New Member
Joined
Jul 8, 2016
Messages
17
Morning,

I'm creating an Excel Rota for managing staff. I've created a simple userform which allows staff to request annual leave. The form saves the date into another sheet where the data can be analysed to show staff on leave on any given date etc.

An Example;

Name: Joe Bloggs
Leave Request Date: 31/08/2016

Once completed it saves the data entered onto another sheet named Requests. This background sheet is used for availability planning etc. A few other userforms accesses this data so that can approve the request, update the request etc. It works fine for Single day requests however i'm trying to work out a way so that if I Put a Range such as From 31/08/2016 to 03/09/2016 it will record the following onto the requests sheet.

Name Request Date
Joe Bloggs 31/08/2016
Joe Bloggs 01/09/2016
Joe Bloggs 02/09/2016
Joe Bloggs 03/09/2016

At this point in time i've no idea how to code this to do this so any help would be greatly appreciated.

Code I have for the single request Userform is below;

Code: 
Private Sub cmdAdd_Click()'Copy input values to sheet.
    Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Requests")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        .Cells(lRow, 1).Value = Me.StaffList.Value
        .Cells(lRow, 2).Value = Me.DateReq.Value
        .Cells(lRow, 3).Value = Me.SubDate.Value
      
    End With
    'Clear input controls.
    Me.StaffList.Value = ""
    Me.DateReq.Value = ""
    Me.SubDate.Value = ""


MsgBox "Your Holiday request has been submitted"
ActiveWorkbook.Save


End Sub
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Hi,
Untested but staying with your existing code give this update a try & see if it does what you want.

Rich (BB code): 
 Private Sub cmdAdd_Click() 'Copy input values to sheet.

    Dim lRow As Long, r As Long
    Dim DaysRequested As Integer, AddDay As Integer
    Dim StartDate As Date
    Dim ws As Worksheet
    
    Set ws = Worksheets("Requests")
    
    lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    
    StartDate = DateValue(Me.DateReq.Value)
    
    DaysRequested = Me.SpinButton1.Value
    
    Do
    'skip weekend dates
    If Weekday(StartDate + AddDay, vbMonday) < 6 Then
    With ws
        .Cells(lRow + r, 1).Value = Me.StaffList.Value
        .Cells(lRow + r, 2).Value = StartDate + AddDay
        .Cells(lRow + r, 3).Value = Me.SubDate.Value
    End With
    r = r + 1
    End If
    AddDay = AddDay + 1
    Loop Until r + 1 > DaysRequested
    
    'Clear input controls.
    Me.StaffList.Value = ""
    Me.DateReq.Value = ""
    Me.SubDate.Value = ""
    Me.SpinButton1.Value = 1


    MsgBox "Your Holiday request has been submitted", 48, "Request Submitted"


    ThisWorkbook.Save




End Sub


Private Sub SpinButton1_Change()
    Me.Label1.Caption = Me.SpinButton1.Value
End Sub


Private Sub UserForm_Initialize()
    With Me.SpinButton1
        .Min = 1
        .Max = 10 ' set max no days that can be requested
        .Value = 1
    End With
    Me.Label1.Caption = 1
End Sub

You will need to add to your form the following additional controls

SpinButton
Label

Change the names if needed & amend code accordingly.

If all well, user should be able to enter start date & with spinbutton, select number day’s holiday required. Code should then add all dates to your master sheet.
I have added a filter to exclude weekend dates but you can adjust as required. You can also limit the max number days user can request where shown in RED.

As always, make back-up before testing new code.

Hope Helpful

Dave
 
Upvote 0
dmt32 said:
Hi,
Untested but staying with your existing code give this update a try & see if it does what you want.

Rich (BB code): 
 Private Sub cmdAdd_Click() 'Copy input values to sheet.

    Dim lRow As Long, r As Long
    Dim DaysRequested As Integer, AddDay As Integer
    Dim StartDate As Date
    Dim ws As Worksheet
    
    Set ws = Worksheets("Requests")
    
    lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    
    StartDate = DateValue(Me.DateReq.Value)
    
    DaysRequested = Me.SpinButton1.Value
    
    Do
    'skip weekend dates
    If Weekday(StartDate + AddDay, vbMonday) < 6 Then
    With ws
        .Cells(lRow + r, 1).Value = Me.StaffList.Value
        .Cells(lRow + r, 2).Value = StartDate + AddDay
        .Cells(lRow + r, 3).Value = Me.SubDate.Value
    End With
    r = r + 1
    End If
    AddDay = AddDay + 1
    Loop Until r + 1 > DaysRequested
    
    'Clear input controls.
    Me.StaffList.Value = ""
    Me.DateReq.Value = ""
    Me.SubDate.Value = ""
    Me.SpinButton1.Value = 1


    MsgBox "Your Holiday request has been submitted", 48, "Request Submitted"


    ThisWorkbook.Save




End Sub


Private Sub SpinButton1_Change()
    Me.Label1.Caption = Me.SpinButton1.Value
End Sub


Private Sub UserForm_Initialize()
    With Me.SpinButton1
        .Min = 1
        .Max = 10 ' set max no days that can be requested
        .Value = 1
    End With
    Me.Label1.Caption = 1
End Sub

You will need to add to your form the following additional controls

SpinButton
Label

Change the names if needed & amend code accordingly.

If all well, user should be able to enter start date & with spinbutton, select number day’s holiday required. Code should then add all dates to your master sheet.
I have added a filter to exclude weekend dates but you can adjust as required. You can also limit the max number days user can request where shown in RED.

As always, make back-up before testing new code.

Hope Helpful

Dave
Click to expand...


Dave it worked Flawlessly :)

You've just saved a massive headache.

The code didn't work at first....... then I realised I had change the name of one of the fields....

Thanks for your help :)
 
Upvote 0
Dave,

You've opened a can of worms. Code above works flawlessly and thanks again.

I wanted to simplify this the process slightly ( Visually ) so that when I add a value to the Spin Button it would update another Label with the end date. So you can easily see start date and end date. I thought The code below would work the label never updates at all.

Any input?

Private Sub EndDate_Change()

Me.EndDate.Caption = Me.DateReq.Value + Me.Spinbutton1.Value

End Sub
Click to expand...
 
Upvote 0
JayDub85 said:
Dave,

You've opened a can of worms. Code above works flawlessly and thanks again.

I wanted to simplify this the process slightly ( Visually ) so that when I add a value to the Spin Button it would update another Label with the end date. So you can easily see start date and end date. I thought The code below would work the label never updates at all.

Any input?
Click to expand...

Hi,
glad suggestion helped.

You probably need to move your addition line of code to the SpinButton_Change event

Rich (BB code): 
Private Sub SpinButton1_Change()
    Me.Label1.Caption = Me.SpinButton1.Value
    Me.EndDate.Caption = Me.DateReq.Value + Me.SpinButton1.Value
End Sub

Dave
 
Upvote 0
dmt32 said:
Hi,
glad suggestion helped.

You probably need to move your addition line of code to the SpinButton_Change event

Rich (BB code): 
Private Sub SpinButton1_Change()
    Me.Label1.Caption = Me.SpinButton1.Value
    Me.EndDate.Caption = Me.DateReq.Value + Me.SpinButton1.Value
End Sub

Dave
Click to expand...

Hey,

I tried the above and i'm getting a type mistmatch, i'm surmising this is because one is a date and one is a number so its struggling to add them both to get the +1 days etc. :(

Back to the drawing board I guess :)
 
Upvote 0
JayDub85 said:
Hey,

I tried the above and i'm getting a type mistmatch, i'm surmising this is because one is a date and one is a number so its struggling to add them both to get the +1 days etc. :(

Back to the drawing board I guess :)
Click to expand...

Try:

Code: 
Private Sub SpinButton1_Change()
    Me.Label1.Caption = Me.SpinButton1.Value
    If IsDate(Me.DateReq.Value) Then Me.EndDate.Caption = DateValue(Me.DateReq.Value) + Me.SpinButton1.Value
End Sub


Dave
 
Upvote 0
dmt32 said:
Try:

Code: 
Private Sub SpinButton1_Change()
    Me.Label1.Caption = Me.SpinButton1.Value
    If IsDate(Me.DateReq.Value) Then Me.EndDate.Caption = DateValue(Me.DateReq.Value) + Me.SpinButton1.Value
End Sub


Dave
Click to expand...

Worked an treat Dave!

Thanks a lot :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Issues copying data to another sheet
Replies
4
Views
275
jynxy
J
Guna13
  • Question
Trigger email to Indidudula User for leave request
Replies
0
Views
310
Guna13
Guna13
G
  • Question
Staff Scheduler
Replies
3
Views
537
Gwill1983
G
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
408
AndyEd
A
N
  • Question
Help with populating rota based on colour of cell
Replies
0
Views
345
Nashpotatoes
N

Forum statistics

Threads
1,215,094
Messages
6,123,071
Members
449,092
Latest member
ipruravindra

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