excel userform calendar for leave tracking

[bqheng]

Hi all,

I'm trying to make an excel spreadsheet calendar for tracking leave. Top row will have months and days while first column will have the employee's name. Employee who wish to take leave will open up the userform, select the dates from the calendar, choose the type of leave and click submit. The information from the userform will be transferred to the spreadsheet. I only managed to do the codes for the userform but lack the knowledge to do the others. Can someone please help me with codes?

Attached is the file.

[SamT]

Put the Start Date in the first cell in Row 2. (assuming that the cell is "C2", in "C3" put the formula "= C2+1". Copy that formula all the way to the End Date cell.

with Row 2, Format Cells; Custom = "dd"

Please, rename the controls by these rules (http://www.vbaexpress.com/forum/showthread.php?47549-Load-Form-in-Personal-Forms-Library-With-VBA&p=298150&viewfull=1#post298150)


Private Sub CommandButtonSaveEntry_Click()
WriteData Calendar1.Value, Calendar2.Value
End Sub



Private Sub WriteData(StrtDate As Long, EndDate As Long)
Dim StrtCol As Long
Dim EndCol As Range
Dim StaffRow As Long

With Sheets("Sheet1")
StrtCol = .Rows(2).Find(StrtDate, LookIn:=xlValues).Column
EndCol = .Rows(2).Find(EndDate, LookIn:=xlValues).Column
StaffRow = .Columns(1).Find(ComboBox1.Value, LookIn:=xlValues).Row
.Range(Cells(StaffRow, StrtCol), Cells(StaffRow, EndCol)).Interior.ColorIndex = 3
End With
End Sub

[bqheng]

Hi, I do not understand how to change the controls for the second part of your code. Can you please help me?

[SamT]

I think I found and fixed all the typos in my first post. I hope that you fixed them in the code before you tried it.

Be sure and type "Option Explicit" at the very top of all your code pages.

I fixed the date in Cell "B2" of your attachment. You had the date as "1/1/1900." I changed it to "9/1/2013." (USA style dates.)

No code would ever find a current date in the year 1900.

You must correct that in your own workbook.

[bqheng]

Hi, I tried the code again, but there is this error with the StrtCol line. Do I need to name a range or something? I'm rather confused as to what is wrong with the code. I copied your edited codes, typed "Option Explicit" at the the top of the page and changed the date formatting as you instructed.

[SamT]

The problem was that the two calendars were returning a full Date, but Row(2) contains only 2 character day numbers as Strings. Since Find could not see a full date in Row(2), it never returned a Range Object, so you got the 'Object not set" error.

To get the below code to work, I declared "StrtDate" and EndDate" as Variants so they could hold different Types of data, then transformed the passed Data from full dates to day number Strings which Find could use.

xls Worksheets do not have enough Columns for an entire year of dates. I have provided means of returning the Month name from the StrtDate and EndDate values.

You really need to Un-Merge the cells containing the Month Name. After Un-Merging, merely select all the cells you want the Name to be centered in and Format Horizontal Alignment = "Center Across Selection." VBA hates to try and use merged cells. It will mess up almost every time it tries. Be sure and keep the month name in the first Cell of the selection.


Private Sub CommandButton1_Click()
WriteData Calendar1.Value, Calendar2.Value
End Sub

Private Sub WriteData(StrtDate As Variant, EndDate As Variant)
Dim StrtMonth As String
Dim EndMonth As String
Dim EOMCol As Long 'End Of Month Column

Dim StrtDateCol As Long
Dim EndDateCol As Long
Dim StaffRow As Long

'StrtMonth = Format(StrtDate, "mmmm")
'EndMonth = Format(EndDate, "mmmm")
StrtDate = Format(StrtDate, "dd")
EndDate = Format(EndDate, "dd")

With ThisWorkbook.Sheets("Sheet1")
StrtCol = .Rows(2).Find(StrtDate).Column
'EOMCol = Cells(2, StrtCol).End(xlToRight).Column
EndCol = .Rows(2).Find(EndDate).Column
StaffRow = .Columns(1).Find(ComboBox1.Value).Row
.Range(Cells(StaffRow, StrtCol), Cells(StaffRow, EndCol)).Interior.ColorIndex = 3
End With
End Sub

[bqheng]

Hi SamT, could you please take a look at the attached file? I copied and pasted the code in your last post and I still couldn't get it to work properly :((

[SamT]

Easy fix :D

Hint :)

Dim StrtDateCol As Long
Dim EndDateCol As Long

2 @ StrtCol
2 @ EndCol

[bqheng]

Hi SamT, finally got it to wortk :) THanks alot!! I will tweak it to the way I want it to work and hopefully you will still continue to help me if i encounter problems later.

[ssalghamdi]

Hi all,

I'm trying to make an excel spreadsheet calendar for tracking leave. Top row will have months and days while first column will have the employee's name. Employee who wish to take leave will open up the userform, select the dates from the calendar, choose the type of leave and click submit. The information from the userform will be transferred to the spreadsheet. I only managed to do the codes for the userform but lack the knowledge to do the others. Can someone please help me with codes?

Attached is the file.

[Aussiebear]

(Sigh.....) ssaighamdi, so what is your point? You use the quote function to hijack an old thread, you fail to attach your file, and didn't explain your question. Any chance you could have another go??? After all we can't read minds.