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]

Hi Mick, thanks again for all you help thus far with this doc. I really appreciate it.

I was going roll this out today after playing around with it over the weekend, but have found one minor problem. When the user manually enters in names or dates in the drop downs, unless they are exactly the same as the items in the drop downs, it throws an error (mismatch 13). Is there a way to handle this issue? It seems that the field has an extra space for one more slot, so if a user enters 01/01/20111 it'll throw up this error. I've used the "Match Required: True" so that they have to enter a date in the list, but I can't work out how to remove this extra character space.. any help would be great.

Thanks,

Adam

 

[MickG]

Personally , I would only allow user to select values in Comboboxes by setting the "Style" property to "fmStyleDropDownList".
But if you want to confirm entry is a date then try adding this bit of code to your userform code.

Set Rng = Range(Range("B5"), Range("B" & rows.Count).End(xlUp))
 
If Not IsDate(ComboBox2) Or Not IsDate(ComboBox3) Then
MsgBox "Date Format Incorrect"
Exit Sub
End If
 
sDt = ComboBox2
eDt = ComboBox3

 

[AGrace]

Personally , I would only allow user to select values in Comboboxes by setting the "Style" property to "fmStyleDropDownList".
But if you want to confirm entry is a date then try adding this bit of code to your userform code.

Set Rng = Range(Range("B5"), Range("B" & rows.Count).End(xlUp))
 
If Not IsDate(ComboBox2) Or Not IsDate(ComboBox3) Then
MsgBox "Date Format Incorrect"
Exit Sub
End If
 
sDt = ComboBox2
eDt = ComboBox3

Good cool on the style.. didn't know it could do that.

 

[AGrace]

Hi Mick et all,

pls

I've realised that this tracker doesn't consider public holidays in its calculations.

I've added a cell background colour throughout the year (yellow - Colour code 36). Is there a way to have the following code also check to see if the user's selected dates have included any public holiday and if so, warn them as it does with the weekend day warning?

Private Sub ComboBox2_Change()
Dim Dts As Date
Dim Txt As String
Dim c As Integer
Dim P As Integer
Dim Dtx As String
If Not ComboBox2.ListIndex = 0 Then
    For Dts = ComboBox1 To ComboBox2
        c = c + 1
        If Weekday(Dts, vbMonday) > 5 Then
            Txt = Txt & Dts & Chr(10)
            P = P + 1
            If P < 3 Then
                If P = 1 Then
                    Dtx = WeekdayName(Weekday(Dts, vbMonday), True, vbMonday)
                Else
                    Dtx = Dtx & " and " & WeekdayName(Weekday(Dts, vbMonday), True, vbMonday)
                End If
        End If
    End If
    Next Dts
If P > 0 And c > P Then
    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's Up..."
ElseIf P = c Then
MsgBox "You have selected a " & Dtx & " which will not be included in the summary", vbInformation + vbOKOnly, "Head's Up..."
End If
End If

Call submitEnabled

End Sub

As always, I greatly appreciated any and all help received!

Adam

 

[Mel Smith]

Hi Adam,

I've fouind this rthread and followed it through as it's similar to what I'm trying to achieve. Could you let me have a copy of the spreadsheet once you've finished it?

Many thanks.

Mel

 

[AGrace]

sure, pm me your email address and I'll fire it over.

 

[AGrace]

Hi all,

For anyone interested I've popped the tracker online. Grab it here:

http://HolidayTracker.4shared.com


To remove or add employees, insert/delete the desired number of employee rows and then in the VBA code, open the code for the UserForm1 find the following sub:

Private Sub UserForm_Initialize()
Dim Dys As Integer
Dim n As Integer
Dim Dt As Date
Dys = DateValue("1/1/" & Year(Now)) - DateValue("1/1/" & (Year(Now) - 1))
ReDim Ray(1 To Dys)
Dt = "1/1/" & Year(Now)
    For n = 1 To Dys
        Ray(n) = IIf(n = 1, Dt, DateAdd("d", 1, Dt))
        Dt = Ray(n)
    Next n
Me.ComboBox1.List = Application.Transpose(Ray)
Me.ComboBox2.List = Application.Transpose(Ray)

nameBox1.List = Worksheets("sheet1").Range("B6:B45").Value  <<< Change the B45 to which ever row the list of employees ends on. 

End Sub

And you're done!

 

[cedricthecat]

Clicked on the link and went to "download" and ended up with something telling me my computer was infected......

Didn't click anything but instead closed all IE from Task Manager.

Not sure if the link is pointing to something dodgy, but I'd suggest you check it out

 

[AGrace]

Clicked on the link and went to "download" and ended up with something telling me my computer was infected......

Didn't click anything but instead closed all IE from Task Manager.

Not sure if the link is pointing to something dodgy, but I'd suggest you check it out

Hi, the site I've published the document to is a free hosting site, so there are adverts. I expect you may have selected an advert.

I made a short video and popped it on youtube.

http://www.youtube.com/watch?v=JDsf2siQOsQ :wink:

 

[LT1511]

Good morning,
None of the links seem to work. How can I get a copy of this file?