GYRO Posted December 27, 2005 Share Posted December 27, 2005 I have an Excel spreadsheet which acts as a sort of TimeCard for users to enter their time in. Every two weeks the user must change a cell entitled "Pay Period Ending Date". What I'd love to figure out (there's gotta be a way), is to automatically have this cell change by itself every second Friday (the Pay Period ends every other Thursday). I love automating things as much as possible and this is one that has me very frustrated. :'( I've searched the internet for clues on this and one of my searches led to this Newsletter Forum website.Is there any way to do this??Thanks. Quote Link to comment Share on other sites More sharing options...
Tushman Posted December 31, 2005 Share Posted December 31, 2005 Is there any way to do this??Thanks.Yes it could be done with some VBA coding. When running VBA routines in the background the user's PC must have security set to low or medium. If it's set to 'High' - it will stop the code from running, so make sure that the users of the timesheet can make that change on their PC. In some corporate settings (like the company I am temping at) they have it locked down extremely tight.I have found this forum here offers some of the best help with VBA.http://www.vbaexpress.com/forum/ Quote Link to comment Share on other sites More sharing options...
daveydoom Posted December 31, 2005 Share Posted December 31, 2005 (edited) I believe there's an Excel formula that goes something like =today()You could make a VBA macro to do this for you. Just add a button to your toolbar and assign the macro to the button. Here's where I go for excellent VBA help: Extreme VBA Edited December 31, 2005 by daveydoom Quote Link to comment Share on other sites More sharing options...
daveydoom Posted December 31, 2005 Share Posted December 31, 2005 (edited) Actually you could just make the macro fill in the date and call the macro Auto_Open() . Every macro requires a name. This will force the macro to automatically run every time that Excel workbook/worksheet is opened. As long as you don't mind the date being filled in every time the file is opened that may be the easiest way to do it . Edited December 31, 2005 by daveydoom Quote Link to comment Share on other sites More sharing options...
Tushman Posted December 31, 2005 Share Posted December 31, 2005 (edited) Actually you could just make the macro fill in the date and call the macro Auto_Open() . Every macro requires a name. This will force the macro to automatically run every time that Excel workbook/worksheet is opened. As long as you don't mind the date being filled in every time the file is opened that may be the easiest way to do it .I think you're misunderstanding what the poster wants. Sounds like the company he works at is a bi-weekly pay schedule so he wants that date every other Friday - not everytime the user opens the excel file.If you want the VBA routine to run whenever the workbook opens up, then what you should use is a private sub function routine so that you can then name the actual macro (the one that runs the pay period date) to something more specific.Press Alt-F11 to bring up the VBA editor while you're in the excel workbook. In the project window you'll see your timesheet excel file listed on the left. Right clikc on "This worksheet" and select view code. The first line & very last line should be thus:Private Sub Workbook_Open()End SubIn between the two lines is where the VBA coding goes. I don't have time to work on this right now and i've just begun to learn VBA. You should post on one of the two forums mentioned above and you'll get an answer right away. Edited December 31, 2005 by Tushman Quote Link to comment Share on other sites More sharing options...
daveydoom Posted December 31, 2005 Share Posted December 31, 2005 (edited) I think you're misunderstanding what the poster wants. Sounds like the company he works at is a bi-weekly pay schedule so he wants that date every other Friday - not everytime the user opens the excel file.No, I understand perfectly what he wants . I just didn't think it would matter one way or another if it gets updated every time the file is opened or not. It may or may not matter, the original poster is the only one who can tell us for sure :)Aagin, the other option is to make the macro, make a button the toolbar and assign the macro to the button. Then you can run it any time you wish Edited December 31, 2005 by daveydoom Quote Link to comment Share on other sites More sharing options...
Tushman Posted December 31, 2005 Share Posted December 31, 2005 (edited) No, I understand perfectly what he wants . I just didn't think it would matter one way or another if it gets updated every time the file is opened or not. It may or may not matter, the original poster is the only one who can tell us for sure No, that's my point - Gyro does not want it updated every time the file is open. I thought he DID tell us that from the very beginning....is to automatically have this cell change by itself every second Friday (the Pay Period ends every other Thursday). I love automating things as much as possible.... Edited December 31, 2005 by Tushman Quote Link to comment Share on other sites More sharing options...
daveydoom Posted December 31, 2005 Share Posted December 31, 2005 (edited) All I'm saying is that it may not matter if it's updated every time the file is opened or not. If the contents of that cell are only used once every two weeks who cares if it's updated more often than that? If it's going to affect anything else on the worksheet then fine, leave that cell blank and use a macro to update it once every two weeks. Only the original poster knows if that cell affects anything else and he hasn't told us.All I was doing was offering one solution. If it's not appropriate for his needs thats fine with me Cheers. Edited December 31, 2005 by daveydoom Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.