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]

Ok, so I've half sorted the dates in the drop down boxes.

I copied the dates from the sheet1 that go horizontally from left to right, and transposed them to list vertically on sheet2 (=Sheet2!$B$3:$B$367). Using RowSource with this source they now display in the drop downs. However whenever I select a date it still appears a series of numbers in the drop down box...? I don't know why

Any help would be great.

Thanks

Adam

 

[AGrace]

Ok, sorted that combo box issue now :D

Private Sub UserForm_Initialize()

ComboBox1.List = Worksheets("sheet2").Range("B3:B367").Value

End Sub

one down, one to go.

 

[MickG]

The following code will give you dates for currents Year.
If your using the rowsource, you probably need to add the following to your "Combobox_Change" Code.

Private Sub ComboBox1_Change()
With ComboBox1
 .value = Format(.value, "dd/mm/yyyy")
End With
End Sub

Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
[COLOR="Navy"]Dim[/COLOR] Dys [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dt [COLOR="Navy"]As[/COLOR] Date
Dys = DateValue("1/1/" & year(Now)) - DateValue("1/1/" & (year(Now) - 1))
ReDim Ray(1 To Dys)
Dt = "1/1/" & year(Now)
    [COLOR="Navy"]For[/COLOR] n = 1 To Dys
        Ray(n) = IIf(n = 1, Dt, DateAdd("d", 1, Dt))
        Dt = Ray(n)
    [COLOR="Navy"]Next[/COLOR] n
Me.ComboBox2.List = Application.Transpose(Ray)
Me.ComboBox3.List = Application.Transpose(Ray)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[zakasnak]

@agrace, I would be interested to see your finished product.

 

[AGrace]

Edit:

When they hit submit, it'll match the employee name from the nameCombox1 to the worksheet (sheet1.range("B2:B20")), note the absence type selected (which should format the target cell(s) with "Holiday", "Sick Leave" or "Other") then find the "from date" and the "to" date and add the absence type value to the cells horizontally between these two dates.

No longer need to colour the cells as this can be done via conditional formatting.

 

[AGrace]

@agrace, I would be interested to see your finished product.

Sure thing. I'll email it to you once it's finished.

 

[AGrace]

The following code will give you dates for currents Year.
If your using the rowsource, you probably need to add the following to your "Combobox_Change" Code.

Private Sub ComboBox1_Change()
With ComboBox1
 .value = Format(.value, "dd/mm/yyyy")
End With
End Sub

Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
[COLOR="Navy"]Dim[/COLOR] Dys [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dt [COLOR="Navy"]As[/COLOR] Date
Dys = DateValue("1/1/" & year(Now)) - DateValue("1/1/" & (year(Now) - 1))
ReDim Ray(1 To Dys)
Dt = "1/1/" & year(Now)
    [COLOR="Navy"]For[/COLOR] n = 1 To Dys
        Ray(n) = IIf(n = 1, Dt, DateAdd("d", 1, Dt))
        Dt = Ray(n)
    [COLOR="Navy"]Next[/COLOR] n
Me.ComboBox2.List = Application.Transpose(Ray)
Me.ComboBox3.List = Application.Transpose(Ray)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Thanks Mick. I managed to get the combobox's to display the dates correctly. Which also helps when trying to find the correct cell destination for adding the holiday entry. I just need to figure out how to do this now....

It's funny, I've seen something like this before on these forums. I just don't know the syntax to use or search for.. <sigh>

 

[MickG]

Your Data Dates row(4) , Column"C".
Names:- Column "B" start row 5, alter code to suit.

Private [COLOR=navy]Sub[/COLOR] UserForm_Initialize()
[COLOR=navy]Dim[/COLOR] Dys [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Dt [COLOR=navy]As[/COLOR] Date
Dys = DateValue("1/1/" & year(Now)) - DateValue("1/1/" & (year(Now) - 1))
ReDim Ray(1 To Dys)
Dt = "1/1/" & year(Now)
    [COLOR=navy]For[/COLOR] n = 1 To Dys
        Ray(n) = IIf(n = 1, Dt, DateAdd("d", 1, Dt))
        Dt = Ray(n)
    [COLOR=navy]Next[/COLOR] n
Me.ComboBox2.List = Application.Transpose(Ray)
Me.ComboBox3.List = Application.Transpose(Ray)
'[COLOR=green][B]if you don't need, the above, then still keep below,[/B][/COLOR]
'[COLOR=green][B]so that data always shows in comboboxes[/B][/COLOR]
ComboBox1.ListIndex = 0
ComboBox2.ListIndex = 0
ComboBox3.ListIndex = 0

Code to colour holiday cells:-
Base on :-
Names :- Combobox1
Start Date :- Combobox2
EndDate :- Combobox3
3 OptionButtons , "Green", "Red" and "Blue"

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 = ComboBox2
eDt = ComboBox3
 Select Case True
    Case Is = OptionButton1: col = 4
    Case Is = OptionButton2: col = 3
    Case Is = OptionButton3: col = 5
 End Select
 
    For Each Dn In Rng
        If Dn = ComboBox1 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
End Sub

Regards Mick

 

[AGrace]

wow, i'm so close to cracking this i can taste it.

Thank's Mick for your contribution.. it's almost there. I can get the code to correctly add to the worksheet now, and have modified the colours to try and match the legend as much as poss. the only trouble I'm having now is the combo boxes for the from and to dates seem to set the drop down dates to the american style (mm/dd/yy), whereas the source for these is uk (dd/mm/yy).. i think if i can find the cause of this and fix it, this'll be ready :D

hmmmm.. even when i change the format of the date in the source to (DD-month-yy) it still appears as (mm/dd/yy) in the drop down. I swear this wasn't the case earlier..