Lock Cells, Protect Sheet With Password & Time Stamp
Jul 18, 2007
I have a worksheet with employees names and their overtime hours worked that week. I would like to add some code to a button that will prompt for a password. If that password matches a predefiened password then lock a range of cells and place a time stamp next to the button and maybe change the button color of the button to Red. Now if the person with the password wants to change the locked cells, they can click the button again and enter the password and the button will turn green so they can make the changes. I would like the time stamp to be updated every time the cells are locked. Also wanted to have one button for every work week so about four buttons per worksheet. Here is an example of the worksheet. The range of cells I want to lock are the ones in bold.
I have a sheet that is password protected. I have this code attached to a command button. It will unlock the worksheet, autofilter it, print it, unfilter it, and password protect the sheet again. However it is protecting it without a password. I need to have it protected with the password so that someone will not be able to just go to tools to unprotect the sheet.
Private Sub CommandButton1_Click() ActiveSheet.Unprotect "rainforest" Columns("O:O").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlAnd ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Selection.AutoFilter Field:=1 Selection.AutoFilter ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowSorting:=True, AllowFiltering:=True End Sub
I have been really trying to get this sorted myself and I can get it to work with one exception. I can't get the sheet to lock with a password. It locks, but if I try to unlock it again, it does so without prompting me to enter a password.
Essentially I am wanting to unprotect two sheets, unlock the cells that were previously locked (so users couldn't enter data in them when the sheet is protected), then reapply the same password to protect the sheet, but now they will be able to enter data in the unlocked range of cells).
The second macro is to reverse the changes made in the first and 're-lock' the unlocked cells) (and again apply the same password to the sheet).
1) I need to have code to protect the sheet that my user form is writing to. In other words, when the information from the user form is populated to the sheet, I want that sheet protected by a password that I set where I can unlock it, but no one else can.
2) I need to protect my VBA code with a password, I do not know how to do this, I have looked everywhere. I do however think this is something simple and I will kick myself once I find out where it is.
In the attached example, I have two fields. In the first (top), I have fields I want the user to be able to enter data (revenue/weight/skids). The information at the bottom is generated by formulas that act on the user-provided data and display the results at the bottom. The key, though, is keeping Excel-ignorant users from inadvertantly destroying the formulas and data in the middle section. I still need to display the results at each step, though, so hiding those middle fields is not an option. How can I lock only some cells within a single spreadsheet like this?
This question is related to my post here: http://www.excelforum.com/excel-programming/634024-cell-range-protect-unprotect-toggle.html
When you protect a worksheet via VBA then unprotect it the conventional way via: Tools>Protection>Unprotect Sheet. Why is it when you unprotect a worksheet in this way Excel does not ask you for a Password. You simpy only have to select Unprotect Sheet and the sheet is unprotected which for me kinda defeats the object.
Sheet Names: 1) PasswordPage - - I would like this to be the page where a user needs to type in their password. If it is correct, their own worksheet would display. 2) Sally - - Hidden and protected, unless Sally types in correct password. Sally's password would be Summer 3) Vanessa - - Hidden and protected, unless Vanessa types in correct password. Vanessa's password would be Pluto 4) George - - Hidden and protected, unless George types in correct password. George's password would be Carpet 5) Alexander - - Hidden and protected, unless Alexander types in correct password. Alexander's password would be Lampost
I would like to ensure that noone else see's anyone else's sheet unless they know the appropriate password, as each individual sheet contains sensitive information.
I also plan to "protect" each sheet also, so that the Sally, Vanessa, George and Alexander cannot make changes to the formulas, etc. - - this I already know how to do.
My question relates more to, How do I hide/ password protect each sheet as outlined above? I assume I must use visual basic, however I am not very familar with it.
I want to hide a sheet and I can do that. However, what I want to do it that when the user wants to view the sheet I just hid, he has to unhide and then enter a password. Right now tthere is no password.
I've got a work book with numourous password protected sheets which i quite often have to update (as i use around 10 of these with at least 10 sheets in each its time consuming). I've had several goes at creating 2 macros to unlock and then lock the sheets (with my password). I've managed to create on that unlocks the sheets using the password but i can't get a macro to lock the sheets up again using the password.
I have a spreadsheet whee teammates can inut the date in one cell and the exact time in another cell - how do I lock the cell with the time so it can't be changed?
Have a worksheet that will be used multiple times. User will enter name and employee number only on the first instance that the worksheet is opened. Would like to protect that info automatically for further usage. Most of the worksheet is protected but some cells need to remain unlocked so the "whole" sheet protection will not work.
How do I protect only specified cells, say F3, G3, H3, I3, after the user enters data thru an input box without changing any other protected cells on the sheet?
How would I go about lock a cell after there is an entry. Once the entry has been made the user could not change the entry. For example if a user enter in cell b2 he could not change the entry, he could change a2, c2, d2. He would also be able to put an other entry in b3, once enter the cell is lock.
Trying to incorporate the following in a worksheet: Lock the cells automatically in the columns that are older than 2 days or more Lock the cells corresponding to today and yesterday using a command button after entering X Copy the sheet multiple times with the same features
I have the following question: I attached my file. In sheet "boekhouding" I want every cell in column H to contain a button that locks that specific row. So I can't accidently change the dropdownmenu for that row.
I also have some sort of strange problem in worksheet "Rekeningen", Range D1-D5 is named "Grootboek" were it should be named uitgaven. I can't change the name, but it doesn't seem to matter in worksheet rekeningen (the drop down menu for uitgaven works normally), what is wrong here?
I was wondering if it was possible to hide cells using protect sheet? I have a worksheet that contains confidential data and would like it so the cells appeared blank unless you had the password. I know you can hide rows and columns but I need everything on the sheet to be hidden.
I am using Excel 2007 and I have a worksheet with multiple columns, some of these columns are protected and some are not.
I then lock the worksheet with a password so the user can't edit certain cells that are protected but can modify the unprotected cells, the problem is the user cannot use the filter or sort the workbook.
Is there a way to allow filtering or sorting but still lock down the worksheet.
Is there anyway to protect each sheet at one time? I have about 70 sheets and I dont wan to go to each and protect them. They all have the same password so I hope thats not a problem.
I've got a spreadsheet set up for a simple barcode scanning system. The user scans the barcode of the product when complete, and it logs it to a sheet along with a date & time timestamp. I'm then planning to use a v-lookup (barcode to product name) & pivot-table to count instances of 'product name' so that I can monitor what is being produced easier, nothing too tricky.
The problem I'm having comes from the products of the same spec having the same barcode, so data validation is hard to apply, as I can't 'not allow dupes' - the concern I have is that if the user unknowingly (or knowingly!) scans the same ticket twice, I think I've got 2 of a product when I've only got one. The idea I had was to lock all cells for a set time period (say 1 minute) after an entry so that it couldn't be accidentally scanned twice.
I have a excel document with 5 tabs (sheets) and, because different people work with a different tab (sheet), I want to lock each tab (sheet). I want the tab to be visible on the tab bar but when you click on it you will not be able to see the content of that tab (sheet) until you provide the right password (each tab with a different pasword).
How to protect or lock their spreadsheets after certain date.
My question is how to protect or lock workbooks before certain date?
Example:
I have individual woorkbooks for each week in a calendar year saved in one folder.
Is there any way that I can protect them from either being opened or if opened than not be able to save anything in them until one week before the calendar week they are designed for (or whatever date I wish to set)?
I have a workbook(1), contains a worksheet that once filled with user entered parameters, generates data through a VBA module (macro). This module(1) is a proprietary algorithm, and the author has locked the VBA project to protect the module from being tampered with/modified (and rightly so).
I take this data and use it as input in my own algorithms to generate more data. By locking "VBA project" in that workbook(1), I can't see anyway to add my own module(2) to that particular workbook(1). I can create another workbook(2), and write a module(2) there that reads the data from the first workbook(1), but then I'm left with TWO workbooks to do a single task, both have to be opened, and its just not a very elegant way of doing things.
I've contacted the author of the proprietary module(1), and he has no problem modifying the lock protection in the workbook(1) so that only his module(1) is locked, but VBA is not really his forte, and he doesn't know how to do it, or if it is even possible. I've checked many Excel Help facilities, and could not find an answer.
1. Is there any way to lock a SINGLE VBA module within a workbook?
2. Also, is there only ONE VBA project allowed per workbook? If I could add an unprotected VBA project, I could add my own module.
I may not be understanding the entire VB architecture properly, I'm using manuals from 1993, and On-line MS Help is not that "help"ful, so any assistance would be greatly appreciated. Mainly I just want to find out if this is possible or if its not, in which case I'll be forced to work around it in a inelegant fashion. It just seems logical that you should be able to lock just one module.
