Excel Userform Staff Rota - Annual Leave Request Help

[JayDub85]

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;

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

 

[dmt32]

Hi,
Untested but staying with your existing code give this update a try & see if it does what you want.

 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

 

[JayDub85]

Hi,
Untested but staying with your existing code give this update a try & see if it does what you want.

 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

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

 

[JayDub85]

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

 

[dmt32]

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?

Hi,
glad suggestion helped.

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

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

Dave

 

[JayDub85]

Hi,
glad suggestion helped.

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

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

Dave

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

 

[dmt32]

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

Try:

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

 

[JayDub85]

Try:

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

Worked an treat Dave!

Thanks a lot