Simple? Leave Tracker Issues with Macros

[Susanna123]

I am a newbie and I am trying to create a useful Leave tracker for employees. But I have 2 major issues:

1) I have tried different tutorials on how to hide the first Days of March showing in the month of February using Macros. I either end up having 28 days for all months or having March 1st, 2nd and 3rd (which are Columns AE,AF,AG)showing when I select February. I don't know what the problem could be.

2) I have a scrolling calendar but when I insert a Leave (for example Sick Leave "S" in H13,H14 in my Range C8:AG42) it remains fixed in the same cells even when the month changes to march or other months.

I really hope someone can help me, if not I might need to change my Leave Tracker to a much simpler format.

 

[NoSparks]

Can you post the code you have that 'scrolls' the calendar ?
Please don't post a picture of the code, copy and paste it into your reply, select it and click VBA in the menu bar. Thanks.

 

[Susanna123]

Can you post the code you have that 'scrolls' the calendar ?
Please don't post a picture of the code, copy and paste it into your reply, select it and click VBA in the menu bar. Thanks.

This is what I have so far.

Sub Macro1()

'

   For Col_Number = 31 To 33
     If Cells(1, 2) = Month(Cells(6, Col_Number)) Then
        Columns(Col_Number).EntireColumn.Hidden = False
     Else
        Columns(Col_Number).EntireColumn.Hidden = True
     End If
   Next
   
    Range("C9:AG42").Select
    Selection.ClearContents
End Sub

 

[mumps]

Do you have a separate sheet for each month? It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[NoSparks]

The code you've posted is intended to hide up to 3 columns at the end of the month, it doesn't scroll anything.
Your picture doesn't indicate what's in B1, and row 6 is hidden, so if that code doesn't work I can't tell why.

I think it a good idea to take mumps suggestion and share a (sanitized) copy of your file.
I personally use box.com with no issues, it's free for individual use.

 

[Susanna123]

Click HERE for the link to the Training Sheet.

My main issue is:
In the Sheet "LEAVE Date" an employee is travelling from the 14th to the 26th of March (see Leave Date sheet cells E13,D13). How do I get it to show in the Calendar of the sheet named "LEAVE Chart" without having the leave days show in all months when scrolling through the year ? On the other hand, with the code I used the content gets cleared automatically all the time which is not useful either as it never saves an entry.

Range("C9:AG42").Select
    Selection.ClearContents

Is this better? ..

 

[mumps]

Please review Post #4 for attaching screenshots or uploading your file.

 

[NoSparks]

@mumps,
I downloaded the OP's file with no issues
Am currently playing around with it and should be posting it back in an hour or so.

 

[NoSparks]

Thank you @Susanna123

How do I get it to show in the Calendar of the sheet named "LEAVE Chart" without having the leave days show in all months when scrolling through the year ?

The real problem is that there are no true dates on this calendar, just numbers being written and rewritten in the same columns for every month.

Have a look at this file, I've Moved your statistical columns and put in a years worth of real dates.
Row 1 holds info associated with scrolling the months and is hidden.
The button says wipes data but that is currently commented out in the code.
Sorry, I lost your Working Days formula.

Hope this is something usable.

 

[Susanna123]

Thank you @Susanna123

The real problem is that there are no true dates on this calendar, just numbers being written and rewritten in the same columns for every month.

Have a look at this file, I've Moved your statistical columns and put in a years worth of real dates.
Row 1 holds info associated with scrolling the months and is hidden.
The button says wipes data but that is currently commented out in the code.
Sorry, I lost your Working Days formula.

Hope this is something usable.

Thank you @NoSparks !

It is definitely Usable, It solved my main problem with the months and data although it's best for me if I work on a year per time (a Year per sheet).
It's a bit hard trying to study the code but I am doing my best.

Thanks again