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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
 
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.
 
Upvote 0
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
 
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
 
Upvote 0
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
 
Upvote 0
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.
 
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
 
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
 
Upvote 0

Forum statistics

Threads
1,214,367
Messages
6,119,114
Members
448,869
Latest member
Ceblevey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top