Simple? Leave Tracker Issues with Macros

Susanna123

New Member
Joined
Mar 3, 2022
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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.
 

Attachments

  • FEBRUARY Leave Tracker.png
    FEBRUARY Leave Tracker.png
    116.7 KB · Views: 55
  • MARCH Leave Tracker.png
    MARCH Leave Tracker.png
    114.8 KB · Views: 57

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.
 
Upvote 0
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.



VBA Code:
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
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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.

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

Is this better? ..
 
Upvote 0
Please review Post #4 for attaching screenshots or uploading your file.
 
Upvote 0
@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.
 
Upvote 0
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.
 
Upvote 0
Solution
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 (y)(y)
 
Upvote 0

Forum statistics

Threads
1,223,996
Messages
6,175,857
Members
452,676
Latest member
woodyp

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
Back
Top