Security with tabs

fyrbryr

New Member
Joined
Nov 26, 2005
Messages
5
How or Can I lock out the tabs of exel spreadsheets and can I create a macro button to do this. So that a user clicks on the button and the tab is locked from edit.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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
 

fyrbryr

New Member
Joined
Nov 26, 2005
Messages
5
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.
 

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
 

fyrbryr

New Member
Joined
Nov 26, 2005
Messages
5

ADVERTISEMENT

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
 

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569

ADVERTISEMENT

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
 

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.
 

fyrbryr

New Member
Joined
Nov 26, 2005
Messages
5
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
 

fyrbryr

New Member
Joined
Nov 26, 2005
Messages
5
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,088
Members
412,310
Latest member
mark884
Top