Visual Basic - I'm stuck
Posted by May on December 28, 2001 2:20 PM
I am working on a project in excel that'll automatically add 4 hours of leave to each employee on the 1st of the month. So far (thanks to someone on this site) I have it set up to add the 4 hours on the 1st, but I have to manually run the maco myself on the 1st. I want the macro to run automatically when I open the worksheet on the 1st (it can only run once incase I open the sheet several times on the 1st). I've placed what I have so far below. Can someone e-mail me the corrected code? Also is there a way to include in the code that if the 1st lands on the weekend and no one opens the sheet to run the macro it'll check 3 days back on Monday and if the code hasn't run to go ahead and run it. If anyone can help I'd really really appreciate it. thanks!
Public Sub FirstOfMonth()
FOM = Format(Date, "dd")
If FOM = 1 Then
Range("C3").Select ' Enter starting point in sheet
Do
OriginalTime = ActiveCell.Value
ActiveCell.FormulaR1C1 = (OriginalTime + "4")
ActiveCell.Offset(RowOffset:=1).Activate
Loop Until ActiveCell.Row = "20" 'Enter final point in sheet
End If
End Sub
Posted by Kevin Mac on December 28, 2001 2:44 PM
Just rename Public Sub FirstOfMonth()
Public Sub Auto_Open()
See if that helps
Posted by Tom Dickinson on December 28, 2001 3:00 PM
You ask another programmer, you get a different way of doing it. I would suggest designating a blank cell that can hold the last month in which the hours were added (I used A1 in the program). That way, no matter what day the 1st falls on, or how many times the program is opened on the first, the hours are added only once. As for the auto running of the macro, title it "Auto_Open"
Private Sub Auto_Open()
Dim Ctr as Integer
If range("A1") <> Format(Date, "mmm") Then
Range("A1") = Format(Date, "mmm")
For Ctr = 3 to 20
Range("C" & Ctr) = Range("C" & Ctr).value + 4
Next
End If
End Sub
Posted by Kevin Mac on December 28, 2001 3:13 PM
FarfromProgrammin'
While I wish I was a programmer, I am still in the process of learning all this, I was just trying to provide a quick resolution for this question, as I know that time seems to always be a factor. Thanks to all the guys and gals on this board that make it possible for "in training" rookies like me to look professional in all the projects you assist with!!!!
