Excel Timesheet

[trigirl]

I have an Excel timesheet, and I am wondering if there is a way to have the timesheet default to PM after 12 noon, so the employees dont have to put in PM, they would just put their time and the sheet would default it to PM.

 

[tassadar]

just stick the password for the sheet in brackets after the command as in below.

Andy

activesheet.protect (password)

activesheet.unprotect (password)

 

[trigirl]

Can this be done?

 

[trigirl]

ok thanks

 

[trigirl]

I put in the password and now I am getting an error saying I have the wrong password, but it is correct, I can enter it manually and it works fine.
Here is the code I am using:

Sub Button3_Click()
ActiveSheet.Unprotect (rw4455)
InpBx1 = InputBox("Enter the hour you want.")
InpBx2 = InputBox("enter the minutes you want.")
InPBx3 = InputBox("Enter AM or PM ")
Application.ActiveCell.Value = InpBx1 & ":" & InpBx2 & ":" & "00 " & InPBx3
ActiveSheet.Protect (rw4455)
End Sub

 

[tassadar]

yeah for some reason it doesn't recognise that the passwords are the same(no idea why). the way to get around it is don't protect the sheet in the normal way. Instead protect in in the workbook open event as in below, it then recognises it and should be fine.

Andy

Private Sub Workbook_Open()

sheets("your sheet name").protect (rw4455)

End sub

 

[trigirl]

Is there a way I can also add a prompt that says: Did you check to make sure you entered time correctly? and then they have to click yes to proceed.
I would like this to show once the time they entered is recorded on the sheet form the script above.

 

[daniels012]

Do you enter time to the minute? Or every 5 minutes? Or Quarter hour?

The reason I ask, is you could create a combo box with the times in it and then there is no doubt the time would be correct.

michael

 

[trigirl]

We enter time down to the min, so this would be too much for a combo box. I created a Validation that would prompt this, but it does not work when using this script, maybe there is a way to write it into the script.

 

[daniels012]

How about a USER FORM?
That has a combo box that has 1-12 for the hours.
Another that has 1-59 minutes and
a check box for am/pm
Then an OK button to enter the time into the active Cell?

Michael

 

[trigirl]

Well the script we are using kinda of does this now and they can still select the wrong one (AM/PM), so I would really just like to go with a reminder. Plus the fact that I just got everyone trained to use the pop-up box in the script.