Holiday Planner using VBA

AGrace

Board Regular
Joined
Jun 29, 2010
Messages
150
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).

Planner.png


and the userform for the worksheet

plannerUserForm.png


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. :confused:

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

Thanks in advance.

Adam
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

AGrace

Board Regular
Joined
Jun 29, 2010
Messages
150
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 :confused:

Any help would be great.

Thanks

Adam
 

AGrace

Board Regular
Joined
Jun 29, 2010
Messages
150
Ok, sorted that combo box issue now :D


Code:
Private Sub UserForm_Initialize()

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

End Sub

one down, one to go.
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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.
Rich (BB code):
Private Sub ComboBox1_Change()
With ComboBox1
 .value = Format(.value, "dd/mm/yyyy")
End With
End Sub

Code:
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

Active Member
Joined
Sep 21, 2005
Messages
307

ADVERTISEMENT

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

AGrace

Board Regular
Joined
Jun 29, 2010
Messages
150
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

Board Regular
Joined
Jun 29, 2010
Messages
150
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.
Rich (BB code):
Private Sub ComboBox1_Change()
With ComboBox1
 .value = Format(.value, "dd/mm/yyyy")
End With
End Sub

Code:
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.... :LOL:

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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Your Data Dates row(4) , Column"C".
Names:- Column "B" start row 5, alter code to suit.

Code:
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"
Rich (BB code):
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

Board Regular
Joined
Jun 29, 2010
Messages
150
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..
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,631
Members
414,082
Latest member
sasmita

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
Top