Holiday Planner using VBA

[AGrace]

Hi all,

I'm trying to make a creative and easy to use holiday planner that'll be used with the heads of departments in my office.

I so far have the general layout of how I want the worksheet to look (see images below). The calendar covers the entire year (horizontally).

and the userform for the worksheet

What I'd like to do is have a user open the userform from a button on the worksheet (not yet added), select an employee, the type of absence (holiday, sick leave or other) and add the "from" and "to" dates. When they hit submit, the userform will match the employees name from the drop down to the worksheet, note the absence type selected (which should format the target cell(s) accordingly -holiday is green, sick leave red and other blue) then find the "from date" and the "to" date and format (colour) the cells horizontally between these two dates.

I have been plugging away at this over the last day trying different types of code examples people have used for similar workbooks, but haven't the experience or skill to adjust them properly. I'm currently stuck on two things; displaying the dates correctly in the two date drop downs, and of course the submit functionality that adds to the sheet.

For the dates drop downs, i was trying the RowSource property and simply copying jan 1st to dec 31st ($C$3:$NC$3) but it doesn't display the dates correctly.. just numbers.

For the Submit button, i'm completely stumped.

If anyone can help me with either of these two problems, I'd be very grateful!

Thanks in advance.

Adam

 

[AGrace]

DONE AND DONE!

Thank you again Mick! you date code was spot on!

Works perfectly (so far).. going to test it now.

 

[zakasnak]

DONE AND DONE!

Thank you again Mick! you date code was spot on!

Works perfectly (so far).. going to test it now.

Did it work? Can I see? zakasnak@gmail.com

 

[AGrace]

Hi all,

This isn't actually completely there yet. I've spotted a bug, small one. I need to configure this code to exclude weekends, is there a way of amending the code to look at the weekday row when colouring the cells? I have no clue how to do this or what function i'd need to look up.

This is the current code that sets the colours on the cells:

Private Sub CommandButton1_Click()
Dim Rng As Range, Dn As Range
Dim sDt As Date
Dim eDt As Date
Dim Ac As Integer
Dim col As Integer
Set Rng = Range(Range("B5"), Range("B" & Rows.Count).End(xlUp))
sDt = ComboBox1
eDt = ComboBox2
 Select Case True
    Case Is = holidayButton1: col = 43
    Case Is = sickLeaveButton3: col = 53
    Case Is = otherOptionButton4: col = 37
 End Select
 
    For Each Dn In Rng
        If Dn = nameBox1 Then
            For Ac = 1 To 200 ' Change to 366
                If Cells(4, Ac + 2) >= sDt And Cells(4, Ac + 2) <= eDt Then
                    Dn.Offset(, Ac).Interior.ColorIndex = col
                End If
            Next Ac
        End If
    Next Dn
  Unload Me
End Sub

any help would be great!

thanks,

adam

 

[MickG]

Hi, Add the extra code as below:-
This should leave the weekend dates uncoloured.

  For Each Dn In Rng
        If Dn = ComboBox1 Then
            For Ac = 1 To 200 ' Change to 366
           If Weekday(Cells(4, Ac + 2), vbMonday) < 6 Then
                If Cells(4, Ac + 2) >= sDt And Cells(4, Ac + 2) <= eDt Then
                   Dn.Offset(, Ac).Interior.ColorIndex = col
                End If
                End If
            Next Ac
        End If
    Next Dn

Mick

 

[AGrace]

Thanks so much Mick! that's prefect! ray:

 

[AGrace]

Re: clever msgbox that checks date selections...

Ok, sorry to keep asking for help on this, but you're so good at this stuff.. I'm trying, and am learning a lot from this.

However, there's one little update I'd like to include. I'd like there to be a msgbox appear when a user tries to enter dates that span over a weekend. I have it so that the holiday's or sickness do not count over the weekend. But I'd like this to be made clear to the user.

I have this code for the msg box:

MsgBox("Your selection includes one or more weekend days, which will not be included in the summary", vbInformation + vbOkOnly, "Head's Up...")

I can make this come in when the user hits Submit, but I would like it to be clever and know that the user has selected dates that span over a weekend, and only appear when they have selected a date on a weekend.

Any help here would be great.

Thanks again

Adam

 

[MickG]

Hi, Try this in Combobox3:-

Private [COLOR="Navy"]Sub[/COLOR] ComboBox3_Change()
[COLOR="Navy"]Dim[/COLOR] Dts [COLOR="Navy"]As[/COLOR] Date
[COLOR="Navy"]Dim[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] fd [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]If[/COLOR] Not ComboBox3.ListIndex = 0 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]For[/COLOR] Dts = ComboBox2 To ComboBox3
        [COLOR="Navy"]If[/COLOR] Weekday(Dts, vbMonday) > 5 [COLOR="Navy"]Then[/COLOR]
            Txt = Txt & Dts & Chr(10)
            fd = True
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dts
[COLOR="Navy"]If[/COLOR] fd = True [COLOR="Navy"]Then[/COLOR]
    MsgBox "Your selection includes one or more weekend days" _
    & Chr(10) & "which will not be included in the summary" & _
    Chr(10) & Chr(10) & "Weekend Dates" & Chr(10) & Txt, _
    vbInformation + vbOKOnly, "Head'[COLOR="Green"][B]s Up..."[/B][/COLOR]
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[AGrace]

Re: clever msgbox that checks date selections...

Hi Mick,

The code work fine, I had to change the combobox names, but looks great. The only thing is, when someone selects a date that is either just a saturday or sunday (or both) could it possibly show a different message?

Something like "You've selected either a Sat or Sun (or both). This entry will not be added", with a vbExclamation+vbRetryCancel??

This is because the user might select dates in the future without actually knowing what days they fall on.

So far you've been a true rock star.. I think this is the last thing.

As always, your help is greatly appreciated.

Adam

 

[MickG]

Try this:-
It now includes a seperate msg , if you only select "Sat" or "Sun" Or Both.

Private [COLOR="Navy"]Sub[/COLOR] ComboBox3_Change()
[COLOR="Navy"]Dim[/COLOR] Dts [COLOR="Navy"]As[/COLOR] Date
[COLOR="Navy"]Dim[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] P [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dtx [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]If[/COLOR] Not ComboBox3.ListIndex = 0 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]For[/COLOR] Dts = ComboBox2 To ComboBox3
        c = c + 1
        [COLOR="Navy"]If[/COLOR] Weekday(Dts, vbMonday) > 5 [COLOR="Navy"]Then[/COLOR]
            Txt = Txt & Dts & Chr(10)
            P = P + 1
            [COLOR="Navy"]If[/COLOR] P < 3 [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]If[/COLOR] P = 1 [COLOR="Navy"]Then[/COLOR]
                    Dtx = WeekdayName(Weekday(Dts, vbMonday), True, vbMonday)
                [COLOR="Navy"]Else[/COLOR]
                    Dtx = Dtx & " and " & WeekdayName(Weekday(Dts, vbMonday), True, vbMonday)
                [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dts
[COLOR="Navy"]If[/COLOR] P > 0 And c > P [COLOR="Navy"]Then[/COLOR]
    MsgBox "Your selection includes one or more weekend days" _
    & Chr(10) & "which will not be included in the summary" & _
    Chr(10) & Chr(10) & "Weekend Dates" & Chr(10) & Txt, _
    vbInformation + vbOKOnly, "Head'[COLOR="Green"][B]s Up..."[/B][/COLOR]
[COLOR="Navy"]ElseIf[/COLOR] P = c [COLOR="Navy"]Then[/COLOR]
MsgBox "You have selected a " & Dtx & " which will not be included in the summary", vbInformation + vbOKOnly, "Head'[COLOR="Green"][B]s Up..."[/B][/COLOR]
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[AGrace]

Perfect. Thank you so much Mick, this is now finished!