Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
I am buillding a spreasheet that keeps track of attendance points and vacation hours and sick hours. I need the spreadsheet to clear out the vacation hours used when there anniversary date is reached and then start counting new vacation days from that point. the attendance points need to be tracked for the whole year. any help is greatly appreciated
The way you have your sheet setup, just zeroing out the totals does you no good.
You could put something like:
=IF(MONTH(Y13)&DAY(Y13)=MONTH(TODAY())&DAY(TODAY()),0,COUNTIF(C16:AJ54,"s"))
Which will zero out the TOTALS for Sick time on the anniversary date, but the next day it will recount ALL of the days marked with an S again.
You need to Zero out the range C16:AJ54, not the totals.
The simplest way I can think of is to create your own macro from the tool bar and clear the cells manually on the anniversary date.
But, having the macro trigger on a specific date is beyond my VBA talents.You could also, just create another sheet for the new year, that way you don’t loose any history.
0 
OK, this is not guaranteed to work, but after a bit of playing around I came up with this:
In cell A1 put the formula:
=IF(MONTH(Y13)&DAY(Y13)=MONTH(TODAY())&DAY(TODAY()),1,0)
When Today’s Date equals the Anniversary Date, Cell A1 changes to 1
Then add this VBA code:To add the VBA code
Right click on the Employee Tab
Select View Code
Copy or Paste the below:Private Sub Worksheet_Calculate()
If [A1] = 1 Then
'Clear sheet
Range("C16:AK54").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-24
Range("C16").Select
End If
End Sub
Again, no guarantees, your mileage may vary, use at your own risk.
0
It's not going to work.
On the Anniv. Date, it will keep recalculating cell A1 and this kicks of the VBA, so it will keep zeroing out the range until the Anniv. Date and Today's date are not longer equal.
Which means that you will be unable to add any data on the Anniv. Date.Sorry.
Close but no cigar.
0
Doesn't this require that the worksheet be opened every day in order to check the Month and Day?
Well, OK, not every day, but it has to be opened on the anniversary day or A1 won't ever be set to 1.
In addition, if any calculations are done on the anniversary date, then the range will be cleared each time, which could inhibit the use of the sheet on that day.
P.S. The code can be shortened to this, or even shorter if you don't need to Select C16.
Private Sub Worksheet_Calculate() If [A1] = 1 Then Range("C16:AK54").ClearContents Range("C16").Select End Sub
0
do you know of a solution derby or should I just give up the dream of making this spreadsheet work :)
0
To be honest, I haven't been following either thread close enough to know if there is an answer.
If I get a chance I'll take a look, but I'm not making any promises.
0
This is a bit of a kludge but it seems to work.
Open the VB Editor using ALT-F11
Open the Project Explorer from the Task Bar.
Open the appropriate VBA project
Open the MS Excel Objects
Click on Sheet 1 Employees
Enter the macro:
Private Sub Worksheet_Calculate()
If [A1] = 1 Then
If [A2] = "" Then
Range("C16:AK54").ClearContents
Range("C16").Select
Range("A2").Value = 1
End If
End If
End SubNext:
In the Project Explorer window, double click ThisWorkbook
Enter the macro:
Private Sub Workbook_Open()
Range("A2").Value = ""
End Sub
What we are doing:If A1 equals 1 then today is the Anniversary Date.
If A2 equals a blank, then this is the first time thru and we have not yet cleared our range of cells.
Clear range of cells
Reposition cursor to cell C16
Reset the value of A2 from a blank cell to 1
This way, when we recalculate the sheet, the second IF fails, and we do not clear the cells a second or subsequent time.
A2 acts like an on/off flag.
The second part simply resets the value of cell A2 to blank, each time the sheet is opened, insuring that the second IF above will be true when the Anniversary Date comes around again.
0
Doesn't this still require that the workbook be opened on the anniversary date? If not, A1 will never get set to 1.
Maybe you need another switch so that the first time the workbook is opened on or after the anniversary date the range gets cleared.
Shortcut Tip:
Right click the sheet tab for Sheet 1 and choose View code to open the VBE for Sheet 1.
0
Doesn't this still require that the workbook be opened on the anniversary date?
Yes, it is one of the drawbacks that I have mentioned previously.
You could argue that every work day the sheet will be opened, but that argument falls short also, because at some point, one of the anniversary dates will fall on a Saturday, Sunday or Holiday when no one is at work. Then what happens?
Additionally, since this happens automatically, when you forget when someones anniversary date is, you are going to get a bit of a surprise when you open their sheet and find it blank.
Also, you lose all the attendance history of that employee.I'm not a big fan of doing it this way.
That being said, is there any way to run VBA code on a closed workbook?
0
There is nothing that says i have to use this format if you think that there is a better way to lay it out, and make it work I am all ears
0
It's the clearing of all the data on the anniv. date that I don't like.
You are losing a great deal of data on employee attendance trends. This type of data can be very useful to any business.Your worksheets does the job, but as I mentioned, it's a bit cluttered and difficult to understand at first glance.
Have you looked at the other worksheets I suggested?
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
