I need VBA/ macro help, please

June 29, 2009 at 05:39:49
Specs: Windows XP
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

See More: I need VBA/ macro help, please

Report •


#1
June 29, 2009 at 09:18:35
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.

MIKE

http://www.skeptic.com/


Report •

#2
June 29, 2009 at 12:33:28
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.

MIKE

http://www.skeptic.com/


Report •

#3
June 29, 2009 at 12:40:03
thanks mike i will give it a try and see how it plays out

Report •

Related Solutions

#4
June 29, 2009 at 13:03:14
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.

MIKE

http://www.skeptic.com/


Report •

#5
June 29, 2009 at 13:07:32
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


Report •

#6
June 29, 2009 at 13:11:14
do you know of a solution derby or should I just give up the dream of making this spreadsheet work :)

Report •

#7
June 29, 2009 at 13:17:23
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.


Report •

#8
June 29, 2009 at 20:06:44
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 Sub

Next:

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.

MIKE

http://www.skeptic.com/


Report •

#9
June 30, 2009 at 06:56:14
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.


Report •

#10
June 30, 2009 at 07:15:37
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?

MIKE

http://www.skeptic.com/


Report •

#11
June 30, 2009 at 07:18:54
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

Report •

#12
June 30, 2009 at 07:35:33
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?

MIKE

http://www.skeptic.com/


Report •

#13
July 14, 2009 at 14:58:51
I got it to work. Thank you both for your input and help.

Report •


Ask Question