Creating A Macro To Lock A Password Protected Sheet

Feb 23, 2007

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.

View 9 Replies


Macro To Lock Cells In Protected Sheet Depending On Criteria

Dec 13, 2007

I have a Workbook that I use to log invoices and credit notes and it is password protected so that the cells that contain formulas are locked and therefore not selectable.

What I wanted to know is. Is it possible to lock other cells depending on what values are in certain cells?

Let me explain. I have Columns G & H which contain the heading 'Credit Note Number' & 'Credit Note Amount'. Now in columns I & J I have the heading 'Invoice Number' & 'Invoice Amount' respectively.

What I have done is to make a condional formatting so that If I put a value in cells in column G it will change the colour of the adjacent cells in columns I & J, likewise If I fill in cells in column I the conditional formatting will change the colour of adjacent cells in coulumn G & H. This helps me to see whether a row contains a credit note or an invoice.

What I would ideally like is for a Macro that would automatically locks cells G & H in a particular row that I am currently working on if I enter data into the cell in column I. Likewise if I am entering data in column G in the row I am currently working on for a credit note, then lock columns I & J in the row I am currently in.

Any Ideas how this can be done? Also can locking and unlocking of cells be done in VBA while the sheet is protected or would the macro need to temporarily unprotect sheet, lock the cells then protect sheet again?

PS: I have uploaded a Jpeg of what my conditional formatting does to try and illustrate what it is I need.

View 14 Replies View Related

Creating Password Protected Sign In System In Excel With VBA

Mar 10, 2014

I am trying to create simple password protected system in excel. What I have already done is I have a worksheet, which is not visible that holds all user names, passwords and user roles, such as admin and user. Now my problem, I need to create a UserForm that will prompt user to input their details prior to using the workbook. I have no problems with creating the form itself but I cannot really create a code to handle the task. Also, there will be only one system admin, which means that only admin should be able to create users, so that when admin will sign in, he should be able to see the button on the home screen, something like "Create New User". I cannot post my workbook because of the Corporate Copyrights and privacy, but what I can do is post all named ranges that contain password, usernames etc.

View 2 Replies View Related

Forgotten Password For Password Protected Sheet

Mar 21, 2007

I have a spreadsheet, which has certain worksheets that are password protected. I need to make changes to some of the formulas, and the VBA modules, however I cannot remember the password!! Is there a way of identifying the password??

View 3 Replies View Related

Override Password Protected Sheet?

Feb 15, 2013

I would like to copy a worksheet that is password protected. When I copy the whole sheet it says, cannot change part of a merged cell and nothing is copied.

Is there a way to override password protection without knowing the password?

View 2 Replies View Related

Locate Password Of A Protected Excel Sheet?

Aug 28, 2013

I read somewhere that password of a protected excel sheet is located in the sheet itself , hidden somewhere. So , it can be located by some "curious" ones.

Second question is , how can we find that password and prevent anyone from detecting it?

View 1 Replies View Related

Macros Not Working When Sheet Is Password Protected

Jan 9, 2007

I am trying to password protect some of the cells in a worksheet. However doing that starts giving errors with Macros. The Macros are no longer working and infact giving error. What can I do to stop that happening?

View 9 Replies View Related

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.

View 3 Replies View Related

Error Handler For Incorrect Password (Protected Sheet)

Feb 1, 2007

For some reason I thought this would be simple, but not as simple as I thought. I have a UserForm that enters data into a sheet, but before it is entered, the user is prompted for the sheet password (sheet is protected). What I tried to do is use an error handler to exit the sub if the password is incorrect, user hits Cancel, etc. Here is the code I am using:

View 10 Replies View Related

Export (Copy) Sheet From Password Protected Workbook

Aug 18, 2006

I was handed a workbook that is password protected. The worksheets are not password protected. Because the workbook is protected, I cannot add or delete worksheets.

I figured that I could get around this by copying the worksheets to another workbook, but I can't even do that. Naturally, no one remembers what the password is.

View 3 Replies View Related

Password Protected Sheet :: How To Unprotect A Sheet?

Jan 18, 2008

Does anyone know if there is a way to unprotect a sheet. The person who created this spreadsheet no longer works for our company and although we can see the spreadsheet, the formulas are hidden so we can't figure out where the data is being pulled in from. Any ideas?

View 9 Replies View Related

Macro To See If File Exists Or Password Protected

Nov 29, 2009

I have written a macro to select a range of cells one by one and open the excel files mentioned in them and execute a task. It works fine. But I want the system to see if the file exists in its folder or if the file is password protected (for opening). Either of the case the file should be skipped without a warning message and any attempt to open. I need a macro for that. Following is the way the cell contents are


And following is the VBA code I wrote. It it almost stops when reaching a non-existing file name

View 2 Replies View Related

Running A Password Protected Macro Remotely

Jan 26, 2007

I have a macro in a workbook (good start) as follows:

Sub UnhideSheets()

' Set to Auto Calc mode

With Application
.Calculation = xlAutomatic
End With

' Asks for a password (must be "unhider")

Dim mypass, response As String

mypass = Application.InputBox("This macro requires you to enter a password to proceed." & vbCrLf & "Please enter password below.")

' If password is correct

If LCase(mypass) = "unhider" Then

' Unhides all sheets

Dim i As Integer
For i = 1 To Sheets.Count
Sheets(i).Visible = True
Next i

' Then tells you that all is OK

response = MsgBox("All sheets unhidden", 0, "Macro run")

' Otherwise


' Tells you that there is a problem (Unauthorised to continue)

response = MsgBox("Sorry, you are not authorised to use this macro.", 16, "Macro stopped")

' hides the sheet again

Sheets("BSG Only!").Visible = xlHidden

' And ends the "if"

End If

End Sub
This unhides the "very hidden" sheets that make the book work and is password protected because I want to limit the people who can see this data.

What I would like to do, however, is call this macro remotely from another procedure in a separate book, which can only be accessed by those who are authorised to see the hidden data.

Essentially, then, my question is: "How do I call the protected procedure without the user having to enter the password each time, as they must be authorised to have access to the procedure that calls the protected procedure?"

View 3 Replies View Related

Password Protected Macro Workbook - Remove Protection

Jun 6, 2014

In my office, our colleague locked his macros before he left. Now, I would like to continue in that but I have no idea, how to get into macro - to study it and change it as needed now.

How to open it?

locked one.xlsm

View 2 Replies View Related

Macro To Open Protected Workbooks Prompting To Enter Password?

Jul 25, 2012

I am having an issue with a macro to open password protected workbooks. The macro works just fine in opening the files and whatnot, but for some reason it prompts me to enter the password again once the file has been opened. Funny thing is I can either hit OK or hit Cancel and it goes to the next file. All files open correctly, I was just curious as to why this is happening and how to correct it?

View 1 Replies View Related

Exl2K User: Sheet Is Password Protected But User Can Change Color In Unlock Cells

Jun 16, 2006

find attached zip file . if you open the file then you can understand the problem.

I need to change cells color and text color as per user choice in Password protected sheet : (user is useing excl2000)

Kindly open attahced file i putted the note inside the file.

View 3 Replies View Related

Excel 2010 :: Macro Runs On Protected Sheet But Changes Protect Sheet Options?

Feb 24, 2011

I am using Excel 2010 and I have a password protected workbook with password protected sheets that uses several macros. Most of them, in order to run, have to un-protect the sheet and then re-protect it again. This has been accomplished easily enough by adding ActiveSheet.Unprotect Password:= "mypassword" and ActiveSheet.Protect Password:= "mypassword" to the appropriate places in the script. All of my macros, which do various things like sorting and moving data, deleting blank rows, displaying dialog boxes containing warning messages etc. run fine.

My problem is this: when I password protect the sheets manually, I have checked the following options in the "Protect Sheet" dialog box. Under "Allow users of this worksheet to" I have checked 1)Select unlocked cells and 2)Format cells. After entering my password and closing the dialog box my sheet is protected, but I can edit cells in the manner my allowances permit. However, once I run any of the macros that un-protect and re-protect the sheet, I remain able to select and edit unlocked cells (practically, for my purposes, this means that I can input data which will appear in the default font size and color of the sheet) but I cannot format cells (which, practically, for my purposes would allow me to occasionally change the font color and size of the data). Naturally, after running a macro, the other cell-formatting options are unavailable to me as well. Is there any way to get my manual selections to remain in place after running a macro that functions as mine do? Or is there any way to make my manual selections the default settings for a protected sheet?

View 4 Replies View Related

How To Lock Each Tab With Different Password

Oct 25, 2011

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).

View 2 Replies View Related

Password Lock Hidden Column / Row

Feb 22, 2008

Is there a way to lock a column hidden so that it can't be unhidden? Maybe with a password? I tried saving the file with a password but I could still go in and "unhide" that column.

View 3 Replies View Related

Protected Sheet Will Not Allow Macro To Run

Mar 27, 2009

I have protected several sheets in my workbook to protect the forumlas and data for being tempered with by the users.

When I try to run my macro, I get an error message stating:

Runtime '1004'
You can not use this command on a protected sheet

And the debug is pointing to this part of my code

View 14 Replies View Related

Lock A Sheet But Still Make Some Edits/run Macro

Oct 18, 2009

Some of the sheets in my spreadsheet contain some data and graphs with a few macros that are run when a button is pressed. At the bottom I have some comments with a macro that is run to timestamp when these comments are updated or edited.

What I would like to do is lock the entire sheet so that the data and graphs can't be tampered with, apart from the comments part at the bottom because I'd like to be able to have the comments editable. So far I have managed to do that, but when I edit a comment and the timestamp code is run it creates a run-time error 1004. I could just remove the timestamp code I guess but I'd like to keep it there... figuring that there must be a solution.

How can I work around this problem ? Either by being able to keep the sheet locked and still have the timestamp code run, or have it temporarily unlock the sheet - run the code - and then re-lock the sheet.

View 10 Replies View Related

How Do I Get Use Macros When The Doc. Is Password Protected

Nov 10, 2009

How do i get use macros when the excel doc. is password protected?

View 9 Replies View Related

How To Password Protected All Sheets At Once

May 15, 2014

i want to protect my all sheets at once

View 3 Replies View Related

Password Protected VBA Module

Sep 16, 2013

Password protected VBA module causes Excel to crash on debugs. Macros work fine if I open VBE and enter my password.

I'm using Range.("This range does not exist").Select to force a debug.

If I run my macro without entering my module password, the debug window appears and Excel crashes.

If I run my macro after entering my module password, the VBE window opens and it debugs normally.

I'm 90% sure the password protection is the problem. I want to keep my code password protected, but I don't want my project to crash on an error.

View 3 Replies View Related

Macro Not Working When Sheet Protected?

Sep 19, 2013

I have certain cells which I want the formulas hidden to the end user and protected so they can't update them.

I've locked and hidden these cells and protected the sheet however my Macro's fail to work thereafter.

Protect the sheet for the user interface only. This has to be done in VBA:

- Activate the sheet.

- Activate the Visual Basic Editor (Alt+F11).

- Activate the Immediate window (Ctrl+G).

- Type


- Press Enter.
- Type

ActiveSheet.Protect UserInterfaceOnly:=True

- Press Enter.

The sheet is now protected for the end user, but VBA code can freely manipulate it.This works well however when I go to another page and do the same it stops working on the original sheet.I would like to know if there is an easy way to:

protect/unprotect all sheets in one go and have the macros still work?

When I protect pages individually I want the following to be allowed:

Select unlocked/locked cells
Use autofilter
use pivottable

I will first save which cells are un/locked & hidden.

View 9 Replies View Related

Exit Macro If Sheet Protected....!

Mar 6, 2007

I'd like my button-triggered macro to exit and quit IF the sheet it is on is protected when someone presses the button. Here's the idea:

IF the active sheet protected THEN
Display "Can't do this on protected sheet" (and exit macro after click OK)
run the rest of the macro.

View 4 Replies View Related

Macro Wont Run With Sheet Protected

Aug 22, 2007

I have a sheet for data entry. On that sheet a button to run macro to scale the graphs on sheet 2. If I protect sheet 2 though because I am thinking of letting others use this program. It gives me an error 400.

View 9 Replies View Related

Apply Macro To Protected Sheet

Aug 5, 2008

I am creating a series of macros for a workbook, and at the beginning of each macro I unprotect all sheets and at the end of each macro I protect the sheets once more. The issue I am having is that even with these macros in place, I continue to receive an Excel alert message saying:

"The cell or chart that you are trying to change is protected and therefore read-only. To modify a protected cell or chart, first remove protection using the Unprotect Sheet command (Review tab, Changes group). You may be prompted for a password."

The macro will then run. Everything works as it should, but I do not want this error message to pop up. I have tried inserting

Application.DisplayAlerts = False

into the code, but it does not stop the message.

View 9 Replies View Related

How To Unlock Worksheet (Password Protected)

Sep 13, 2013

What if you forgot the password in a protected sheet? is there a way to unlock it?

View 1 Replies View Related

Linking To Password Protected Documents?

Dec 2, 2013

I have a master document which maintains employees vacation an lieu time. I want to create a document for each employee where they can view all of their own person information by linking it to the master. The only problem is is that the master document is password protected. They can't have the password or they could access the master document and other employees confidential information. Is there a way to update there own document without entering the password or codon so the password automatically enters without them seeing it.

View 2 Replies View Related

Copyrights 2005-15, All rights reserved