-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Security with tabs
- Thread starter fyrbryr
- Start date
DominicB
Well-known Member
- Joined
- Oct 3, 2005
- Messages
- 1,569
Good evening fyrbryr
Sure can. But first, just a word of warning. This type of preotection is a little on the weak side and can be removed in seconds (seriously, less than a minute) so don't trust it with your deepest darkest secrets, but it's OK to stop users from tinkering with formulae the shouldn't, or accidental erasure etc.
To do this from excel, just go to Tools > Protection > Protect Sheet and enter a password if you want.
A macro to do this is really simple, something like the below will suffice:
Sub Test()
ActiveSheet.Protect Password:="test"
End Sub
However, this will lock the currently active sheet, so it would be pointless to have a button on the sheet to lock it, you might be better off assigning it to a button on a toolbar.
HTH
DominicB
Sure can. But first, just a word of warning. This type of preotection is a little on the weak side and can be removed in seconds (seriously, less than a minute) so don't trust it with your deepest darkest secrets, but it's OK to stop users from tinkering with formulae the shouldn't, or accidental erasure etc.
To do this from excel, just go to Tools > Protection > Protect Sheet and enter a password if you want.
A macro to do this is really simple, something like the below will suffice:
Sub Test()
ActiveSheet.Protect Password:="test"
End Sub
However, this will lock the currently active sheet, so it would be pointless to have a button on the sheet to lock it, you might be better off assigning it to a button on a toolbar.
HTH
DominicB
Upvote
0
adding to
I thank you for your tip .. I guess i should have expanded on this.. I have already protected it in the fasion you suggest. but then the users puts the data in and this is when i want to lock it. It is a time card sheet and i would like their time locked after the input, by hitting a submit button.
I am not doing this for any profit, i am a firefighter and my chief asked me to develop this time sheet to remove or min paper work.
I thank you for your tip .. I guess i should have expanded on this.. I have already protected it in the fasion you suggest. but then the users puts the data in and this is when i want to lock it. It is a time card sheet and i would like their time locked after the input, by hitting a submit button.
I am not doing this for any profit, i am a firefighter and my chief asked me to develop this time sheet to remove or min paper work.
Upvote
0
DominicB
Well-known Member
- Joined
- Oct 3, 2005
- Messages
- 1,569
Hi fyrbryr
You're being a bit economical with the old information here.
If you've already protected the sheets, but users are entering info, then you must have unlocked some cells, yes? And you want to unprotect the sheet, lock all cells and then reprotect the sheet so nothing can be edited using VBA. Am I right in this?
DominicB
You're being a bit economical with the old information here.
If you've already protected the sheets, but users are entering info, then you must have unlocked some cells, yes? And you want to unprotect the sheet, lock all cells and then reprotect the sheet so nothing can be edited using VBA. Am I right in this?
DominicB
Upvote
0
Dominic,
Yes you are correct, we have 38 firefighters on duty and they enter there own hours unattended in the unlocked cells, after their times are entered i need them to "submit" the sheet tab and lock it from them altering it without permission. I know this is not proobably the best application to do it in, but it is working with what i have.
I appreciate all help here.
Fyrbryr
Yes you are correct, we have 38 firefighters on duty and they enter there own hours unattended in the unlocked cells, after their times are entered i need them to "submit" the sheet tab and lock it from them altering it without permission. I know this is not proobably the best application to do it in, but it is working with what i have.
I appreciate all help here.
Fyrbryr
Upvote
0
Hi,
I propose you read up on this topic http://www.mrexcel.com/board2/viewtopic.php?t=92622 and post your sheet on the board. It will probably be to everybody’s help to know how the guys are entering in the hours.
RAM
I propose you read up on this topic http://www.mrexcel.com/board2/viewtopic.php?t=92622 and post your sheet on the board. It will probably be to everybody’s help to know how the guys are entering in the hours.
RAM
Upvote
0
DominicB
Well-known Member
- Joined
- Oct 3, 2005
- Messages
- 1,569
Hi fyrbryr
Try this - without seeing your sheet it's a litle generic, but that said it's probablythe simplest way. It does exactly what I suggested in my last post. It unlocks the sheet (assuming a password of "test"), sets all cells to locked and re-protects it with the password.
Sub Test()
ActiveSheet.Unprotect Password:="test"
Cells.Select
Selection.Locked = True
ActiveSheet.Protect Password:="test"
End Sub
HTH
DominicB
Try this - without seeing your sheet it's a litle generic, but that said it's probablythe simplest way. It does exactly what I suggested in my last post. It unlocks the sheet (assuming a password of "test"), sets all cells to locked and re-protects it with the password.
Sub Test()
ActiveSheet.Unprotect Password:="test"
Cells.Select
Selection.Locked = True
ActiveSheet.Protect Password:="test"
End Sub
HTH
DominicB
Upvote
0
Justinlabenne
Well-known Member
- Joined
- Jun 12, 2004
- Messages
- 957
One way if you can use vba or are familiar with it:
Build a form for the firefighters to enter data into: They could pick their name and weekday (?) from a dropdown list, enter their hours into a textbox, and the info could be written to anthor sheet that they never even see.
If you don't know vba, I can send you an example via email if you would like to see what I mean further, For your purposes, something could be set up with a very basic understanding of vba. If this interests you, use the email button at the bottom of my post, or send me a private message with your email.
Build a form for the firefighters to enter data into: They could pick their name and weekday (?) from a dropdown list, enter their hours into a textbox, and the info could be written to anthor sheet that they never even see.
If you don't know vba, I can send you an example via email if you would like to see what I mean further, For your purposes, something could be set up with a very basic understanding of vba. If this interests you, use the email button at the bottom of my post, or send me a private message with your email.
Upvote
0
Work schedule throw a dent in my replies.
Thank you for your time and replies, i won't be back at work to try them out or reply with thought untill tuesday. We work every third day for 24 hrs and we get the following 48 hrs off. All my notes and laptop are at work...
thanks again
Thank you for your time and replies, i won't be back at work to try them out or reply with thought untill tuesday. We work every third day for 24 hrs and we get the following 48 hrs off. All my notes and laptop are at work...
thanks again
Upvote
0
Trying not to wear out my welcome
DominicB, in your last email you suggested and code, just wondering where i could put the code for it to be effective?
RAM, I looked over your suggestion and the only problem is the computer here are locked out to any downloaded of info, so i could not download the zip file to post my sheets.
Thanks in advance for anymore help
DominicB, in your last email you suggested and code, just wondering where i could put the code for it to be effective?
RAM, I looked over your suggestion and the only problem is the computer here are locked out to any downloaded of info, so i could not download the zip file to post my sheets.
Thanks in advance for anymore help
Upvote
0
