Workbook Enable Macro Protect / Unprotect The Sheet

Sep 4, 2009

I need a macro that I can have in any workbook enabling me to protect / unprotect the workbook that I have currently opened with a set password (let say "Password")

I would link this macro to a button in excel 2002. I have try the following but it doesnt work

Would it be possible that the button (first) works (second) understand whether or not protect / unprotected and do the opposite?

Sub Protect
Activeworkbook.protect password:="password", structure:=FALSE, Windows:=false
End Sub

ADVERTISEMENT

Code To UNPROTECT Then PROTECT Workbook When Macro Runs

Jul 24, 2014

Okay, I have this code and it works very well - but it only works if my WORKBOOK is unprotected. I know how to Unprotect then Protect an ActiveSheet, but I can't figure out how to Unprotect then Protect my workbook when the macro runs..

Sub BLM_RENAME_SHEET()
Dim WS As Worksheet
Application.ScreenUpdating = False
For Each WS In ActiveWindow.SelectedSheets

[Code] ......

View 9 Replies View Related

Macro To Unprotect Sheet - Paste Then Protect Sheet

Apr 8, 2014

I want a sheet to copy and paste into another worksheet, I have that code and its working fine, but what I also want to do is for the sheet to be protected,so the code would be, copy, unlock, paste, lock, save.

Here is the code I have so far. I know i have to include ActiveSheet.Unprotect "passowrd" ActiveSheet.Protect "password", but I dont know where to include that last part of the code.

View 5 Replies View Related

Macro To Unprotect Sheet Unlock Cells And Protect Worksheet With Same Password

Jan 23, 2012

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

this is the code I have used.

Sub PART_YEAR()

Sheets("2012 Calculator 1 week").Select
ActiveSheet.Unprotect "taado"
Range("D20:D23").Select
Selection.Locked = False
Selection.FormulaHidden = False
Sheets("2012 Calculator 2 week").Select

[Code]...

I am assuming I have superfluous code in there, in addition to my mistake with the reapplication of the password.

View 3 Replies View Related

Protected Sheet - Macro To Unprotect, Refresh Sql Query & Re-protect Fails

Feb 21, 2007

I have a protected worksheet containing an SQL query. I have two cells on the worksheet unlocked so that users can enter fresh parameters to feed the query.
Because I have to use MS Query to interrogate our SQL server (current IT decree), the query won't support parameters directly when the query cannot be represented graphically - so I have to manually edit the sql command text and then refresh the query - or at least that was why I wrote the code below

The problem: My macro won't unprotect the sheet before it calls the SQL.it therefore won't update the sheet to detail the DSN, command text and number of parameters (latter should be 0) - info just used to verify queryit won't run the query / return the data as the sheet is still protected Comment: I have had to remove sheet protection in order to allow the sql to execute and return data. If protection is removed, the query does execute and return data ( changes in the "parameter" cell contents do cause the query to be correctly modified)
I could comment out the debug info but the query still doesn't return data if the sheet is protected.

Activesheet And worksheets(ndx) where ndx has been defined As ActiveSheet.Name
I apologise If my post Is difficult To follow - especially the code.

Sub AgedStockParameters()
nmb = ActiveWorkbook.Name
ndx = ActiveSheet.Name
Workbooks(nmb).Activate
Worksheets(ndx).Activate
ActiveSheet.Range("A1").Select
WkshtQryCon = Workbooks(nmb).Worksheets(ndx).QueryTables(1).Connection
ActiveCell.Value = WkshtQryCon
Range("A2").Select
WkshtQryFld = Workbooks(nmb).Worksheets(ndx).QueryTables(1).CommandText
ActiveCell.Value = WkshtQryFld.......................

View 2 Replies View Related

Unprotect A Workbook With VBA And Re-protect It When Saved

May 13, 2008

I want the excel spreadsheet to "BE Protected" in standard form, and only be "Unprotected" when macros are enabled -- BUT DONT JUMP TO CONCLUSIONS, hear me out, it gets deeper than that

If the user does not enable macros, I want the book to be protected. If they do enable macros, it becomes unprotected.... (On load is good enough, no need to be real-time)..... But, they can save the book, so it would no longer be protected , which is where my question comes in ...

I could use the -unprotect "password"- on load when macros are enabled but this will only work if the spreadsheet was originally protected and is never saved again. This is a problem because this workbook will be given to others and will be saved time and time again.

So, I had a vision :-) .... function 'before save' , protect the sheet using -protect "password"- , allow the sheet to save, and then unprotect back using 'unprotect "password"' so you can continue using - BUT, the problem with this is now I have created an endless loop in theory, because after it unprotects it would want to re-save again because it changed

View 12 Replies View Related

Protect And Unprotect Workbook Using VBA Code

Oct 31, 2011

i'm looking for a vba code to solve the following problem a .xlsm workbook, the protect workbook passwork is known as "111111", the VBA project password is "222222", and how to write vba code to insert a new sheet, suppose the workbook has three worksheet, now using vba code to unprotect workbook, and auto insert a newsheet, then protect workbook again, whcih the password remains as "111111" using a commondbutton to do this, everytime you click, insert a new sheet.

View 9 Replies View Related

Toggle "Protect/Unprotect Sheet" In Macro

Nov 27, 2009

Toggle "Protect/Unprotect sheet" in macro
Correct the following macro. It does not work.

View 2 Replies View Related

Protect / Unprotect Sheet

Jan 23, 2013

I am using the following code to protect/unprotect a sheet in a tool that i am working on, which seems to work great. It unprotects the sheet runs the code inbetween the two liens of code and then protects the sheet again.

VB:
ActiveSheet.Unprotect Password = "mypassword"
ActiveSheet.Protect Password = "mypassword"

The issue I am having is if i want to go in and edit the sheet without running the code it will not let me. When I go in and type the password to unprotect it says it is invalid. I have typed it exactly how the code reads. I have even tried including the quotes but no luck.

View 4 Replies View Related

Protect - Unprotect The Sheet

Jan 2, 2009

I have a worksheet where the users need to enter time. I have a macro that captures the time when the user hits Ctrl + t

What I want now is a code which protects the cell after entering the time with Ctrl + t.

I tried to do it, but my macro only works when I don't set any passwords to protect the sheet. How can I pass the password to the code and protect the sheet after the code to capture the time is being executed.

View 14 Replies View Related

Protect Or Unprotect Sheet By Various Users?

Jun 22, 2014

Is there is any way we can protect sheet in such a way that; if user - Mr. A inputs his password he can only go and is able to input his data in sheet 1.

I've a file which have multiple sheets say about 80 tabs are present. My colleague only works on one of the tab (sheet) enters data, that file is placed on our general sharing folder I want to know if it is possible that I can assign protection in such a way that when she enters her password she is able to input data only on the sheet in which she works and cannot roam around to other sheets?

Or is it possible that on first sheet there is username or password can appear? And if the data inputting person opens that file and enter her password option appear which can take her to that particular sheet? And if a guest open that file he/she can only view particular sheets which contain reports?

View 1 Replies View Related

Protect And Unprotect All Sheets And Workbook Using Userform Interface And Password

Jul 6, 2012

I am trying to create a way to protect and unprotect all sheets and the worbook by means of a Userform pop up interface.

I have managed to create the Userform but what the code will look like and where to place the VBA code that will make it work, Also i would like to automatically save the sheet/s as i switch between them. so that any updates made to the sheet will be automatically saved when i move to the next sheet and also when i protect or close the workbook.

View 3 Replies View Related

Enable Outline But Protect Sheet

Sep 15, 2007

I have a sheet that is several hundred rows long and so I have grouped like data so the user can expand or colapse the data they would like to see. There is nothing on the page the user will need to modify so I have protected the page so they cannot make changes. If I protect the sheet then I am no longer able to expand or colapse the groupings.

View 6 Replies View Related

Using Macro To Protect And Unprotect Worksheets

Oct 23, 2008

I am looking for a way to protect and unprotect all worksheets with a password through a macro. I have excel 2007.

View 2 Replies View Related

Macro: Protect & Unprotect Sheets

Nov 9, 2006

Is there an easy way (using a macro) to unprotect and protect sheets?

Most sheets in a certain workbook are protected except a few cells that are open for manual entry. Because I have links to other cells in other workbooks, I must unprotect those sheets/fields before I can edit the links. And I MUST ensure all sheets are protected again when I'm done with my work.

Also, is it possible to password-protect that specific macro so I'm the only one who can run it to unprotect and protect?

View 9 Replies View Related

Password Protect/Unprotect Dialog Macro??? !

Mar 4, 2007

Stumbling over syntax on what should be easy. I want to password protect the active sheet using plain vanilla user input box; and then unprotect ALSO via an input box. Doesn't matter about masking the input with **** or whatever. Based on responses below, here is macro code I used:

To protect the sheet:

Sub ProtectSecurity()
Dim pword As String
pword = InputBox("Enter Password", "Password")
ActiveSheet.Protect pword
End Sub
---------------------------------
To Unprotect the sheet:

Sub UnprotectSecurity()
Dim pword As String
pword = InputBox("Enter Password", "Password")
ActiveSheet.Unprotect pword
End Sub

View 5 Replies View Related

Excel 2003 :: Unprotect And Protect Multiple Works Sheets With Macro?

Feb 25, 2008

I have an annual leave (vacation) work book with a summary page work sheet and separate work sheets for each month i.e. Jan, Feb, Mar.....to......Dec. In order to protect the formulas I have protected them by allowing only access to the input cells on each work sheet and the protect each work sheet.

When someone either joins or leaves the team I have to manually unprotect each sheet and protect again when I have completed the amendments to each of the 13 tabs.

macro code I would need to unprotect all the works sheets in one go (as I use the same password for all the sheets) and reset the passwords (protect) the sheets with more macro code.

I will be running the two macros from my own personal.xls file and ideally they would be fully automatic i.e. I would not need to input the passwords in to unlock or lock the work sheets as the password would be written in the code already

Excel version 2003

View 3 Replies View Related

Enable Macro & EnableEvents Problem To Protect Sheets

Mar 21, 2007

I am trying to crack away to protect my workbook, by forcing users the enable macros on launching.

What I am trying to do is if Workbook_BeforeSave runs a routine called DoIt. This calls HideAll, saves, then calls ShowAll al whilst not updating. (ie so the user is non the wiser and the computer does not asked to save again if no changes are made).

The reason I am trying this is so that if the user closes the document, it will not ask them again to save. Instead it has preserved the state in the HideAll place (ie all important worksheets are VeryHidden.

It is only by enabling Macros on open can the ShowAll make them visible again.

Below is the code I am using and it works, except it seems the Application.EnableEvents = False in the DoIt macro does not seem to be doing what it should.

very dearly love to get an answer as it is driving me up the wall!

Module 1:

Public bIsClosing As Boolean
Dim wsSheet As Worksheet

Sub HideAll()
Application.ScreenUpdating = False
For Each wsSheet In ThisWorkbook.Worksheets
If wsSheet.Name = "Warning" Or wsSheet.Name = "Authorise" Then
wsSheet.Visible = xlSheetVisible
Else
wsSheet.Visible = xlSheetVeryHidden
End If
Next wsSheet
Application.ScreenUpdating = True...............................

View 9 Replies View Related

Automatically Unprotect Worksheet Before Email Macro Then Protect After Email Is Sent

Mar 2, 2009

I currently have a button then when pressed automatically sends a summary report taken from the first page of Sheet 1.

Worksheet needs to be protected all the time, but Macro only works on an unprotected worksheet.

I was wondering what additional code and where to put in so that when
protected back again after Macro has been executed?

Here’s the Macro taken from [url]

View 11 Replies View Related

Forgotten Password To Unprotect Workbook/sheet

Feb 25, 2005

Is there a way to unprotect a workbook and/or worksheet in Excel 2002/2003 if
the original password is forgotten?

View 12 Replies View Related

Protect / Unprotect Worksheet Before VBA Runs

Oct 26, 2011

I have a file that needs to be locked down so that the end user does not have the ability to enter data where they shouldn't. The end users are very Excel illiterate, and have proven that I need to have high security in place. Here is my current VBA code - the purpose of which is to hide/unhide rows depending on data entries. (Disclaimer: this code may not be pretty as I am a VBA rookie, and I've begged borrowed and stolen from about 10 other threads on this board to get where I am, and yes it works).

Code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C7" Then Call Changeto1
If Not Intersect(Range("c9"), Target) Is Nothing Then
Rows("15:44").EntireRow.Hidden = True
Rows("15:" & Range("c9").Value + 14).EntireRow.Hidden = False
End If

[Code]...

The question is now....how can I unprotect the sheet so that this code works without allowing them to enter data where they shouldn't. I've been at this for days already.

View 7 Replies View Related

How To Protect / Unprotect Multiple Sheets

Jan 27, 2012

I have a workbook where I password protect each sheet. Is there any way to quickly unprotect all sheets, make changes then protect all sheets. I cannot seem to do this by grouping multiple sheets. the option to unpropect/protect is not available.

View 6 Replies View Related

Unprotect & Protect All Sheets With Password

Dec 21, 2006

I have a series of workbooks where multiple users are entering data. Most of the cells are locked, and the worksheets are password protected. From time to time, I have to make global changes to all the workbooks. I've recorded a macro to open all the workbooks, but now, I'm running into a problem because for speed, I'd like to unprotect the worksheets and "re-protect" them without having to enter the password 3 million times (exagerration, but not much). What is the proper syntax of code to have VBA enter the password automatically (it is the same for all sheets).

View 4 Replies View Related

Macros To Protect / Unprotect Worksheets And Workbooks

Jul 20, 2005

The first macro protects all sheets in a Spreadsheet and the second unprotects. The the next Macro Protects the enitre workbook and likewise the next one Unprotects. All using passwords.

VB:
Dim ws As Worksheet
Sub ProtectAll()
Dim S As Object

[Code].....

View 9 Replies View Related

InputBox / Ok And Cancel Button - Protect And Unprotect

May 25, 2014

I have this macro code that can protect and unprotect, It will show the input box and ask for a password. my problem is when I choose cancel, it still protect the file, the same thing with my unprotect code. here is my code below

Protect Code :

[Code]...

Unprotect Code :

[Code]...

View 5 Replies View Related

Protect / UnProtect All Workbooks With A Single Button Through VBA

May 5, 2008

I am having some 60+ workbooks in which the end-users are entering data. Each workbook is protected with different passwords.

We have the systems networked. The main compilation file is in the server D:MAIN folder. The workbooks for users are in D:Mainusers sub-folder. The files in users folder are only allowed to have access by the users.

Now, in the compilation sheet I have linked the data from all the individual files (which is in its subfolder). I am facing a trouble that the excel asks password for each and every workbook to update the links in the compilation workbook.

Any possibility to overcome this trouble? A VBA code is the solution? or any other alternative measure is there to solve?

View 14 Replies View Related

Creating Buttons To Protect/unprotect Worksheet

Jan 21, 2009

Create a button for protecting the worksheet and a button for unprotecting the worksheet

View 13 Replies View Related

Code To Protect And Unprotect Cell Ranges?

Jan 28, 2014

following code so as to have certain cell ranges protected and unprotected USING COMMANDBUTTON/PASSWORD

Code:
Private Sub CommandButton1_Click()
Dim Password As String
JamesPassword = "MyPassword" '

[Code]......

View 8 Replies View Related

Macro Disable/enable Still Appears After Macro Deleted From Workbook

Oct 3, 2006

I have a question concerning Macros and the Disable/Enable prompt. My boss has a spreadsheet which has been used for years and he recently wanted to and did remove the macros from the spreadsheet (they were no longer necessary), but the disable/enable prompt still appears when the spreadsheet is opened. I replicated this in a test spreadsheet with a simple insert line macro and received the same results. Is there a way to remove the macros and the disable/enable prompt once they are removed? I know about setting the security to low to not see the prompt, but I would think that once the macros are deleted, the prompt should not appear any more.

View 2 Replies View Related

Excel 2013 :: Speed Protect / Unprotect Sheets

Apr 29, 2014

I experience it takes a lot of time to protect/unprotect sheets in Excel 2013. However, only if the operation is made WITH PASSWORD. This was not the case in Excel 2007. Operation was fast independent with/witout password.

In attached file I have made an example where the time to protect/unprotect 25 sheets is investigated. Can make the test in Excel2013 to double check it is not my computer causing the time delay.

Result test - 25 sheets:
Protect - No password: 0,02 s
Unprotect - No password: 0,06 s
Protect - With password: 6,9 s
Unprotect - With password: 6,9 s

View 7 Replies View Related

Protect Or Unprotect Cells Based On Cell Input

Nov 21, 2006

I am working on my spreadsheet and was wondering if anyone knows if cells can be Protected or Unprotected based on the value of another cell?

Here is what I am looking for:

If cell "A16" on the "Input Sheet" equals AR then Protect Cells J16, K16, L16, M16, N16, and O16

If the value of A16 on the Input Sheet DOES NOT equal AR then leave those cells unprotected.

Has anyone ever done anything like this? On my spreadsheet it would allow the user to skip the unnecessary cells as opposed to having to Tab through them. Not a big deal but definitely would be a time saver and a lot more user friendly.

If you have any questions or concerns just let me know. I will wait to see what the Excel Guru's come up with.

View 9 Replies View Related

Skip Enable Macro On Open Workbook

Sep 19, 2007

I need my users to always enable macros when they open my workbook. Is there a way I can tell excel to enable macros and skip this initial question for users?

View 4 Replies View Related

Minimize File Size Of Macro-enable Workbook?

Sep 9, 2012

I have a macro-enabled workbook, in sheet1 i have two columns Country and Codes, they are in column C and D respectively. I used vlookup formula in generating the codes of the countries pasted in column C, they are being looked-up in sheet2 in column C and D as well. I already have 100000+ rows in sheet 2 under column C and D that's why the file is now heavy. Is it possible to encode column C and D in sheet 2 but still can be looked-up? or is there any other way to minimize the file size.

View 7 Replies View Related

Run Macro In All Excel Macro Enable Workbook

Jan 27, 2014

I want to run a welcome macro in all excel workbooks. Whenever I open any macro enable workbook the welcome macro should run.

View 9 Replies View Related

Protect And Hide Sheet In Workbook?

Apr 26, 2009

I've stored all my on-line passwords (about 50 of them) in one sheet in an Excel workbook. I want to protect it so that if somebody somehow got access to it, they couldn't see the sheet, much less change anything.

I tried Tools/Protection/Protect Sheet and Tools/Protection/Protect Workbook, using a secret password. Then, nobody could change the data -- but they could still see it.

So then I hid the columns first, and then protected the sheet -- which did the trick. But that seems pretty cumbersome, because it involves two steps for me to open it up (unprotecting and then unhiding).

Is there a way to protect/hide in just one step so my sheet of passwords can't be seen by an intruder?

View 6 Replies View Related

Protect Sheet With Password In Workbook

Mar 15, 2012

I'm looking to password protect the sheets in my workbook (using vba) but I can't seem to join this up with allowing users to edit scenarios.

If I record the protection I get this:

Code:
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False

I normally use:

Code:
ActiveSheet.Unprotect Password:="password"

I just need to combine the two. Adding the password before 'DrawingObjects' or at the end doesn't seem to work.

View 5 Replies View Related

Password Protect Each Sheet Within Workbook?

May 23, 2012

I have 5 sheets in my work book.

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.

View 6 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

Macro To Protect All But One Sheet

Aug 21, 2008

Greetings I have a workbook with many sheets and i want a macro to protect all the worksheets with certain settings except for one sheet, sheet 3.

I have borrowed some code from this page that I cannot get to work and I would appreciate any help. the code should also have an if statement that unprotects sheet 3 but i don't know how to do that either. I just get syntax errors

Private Sub Workbook_Open()

Dim wSheet As Worksheet

For Each wSheet In Worksheets

wSheet.Protect Password:="secret", DrawingObjects:=True, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingRows:=True, AllowDeletingRows:= _
True _

.EnableSelection = xlUnlockedCells
.EnableOutlining = True
Next wSheet
End Sub

View 9 Replies View Related

Protect Sheet With Macro Buttons

May 9, 2006

I have two macro buttons on a page that hide and unhide columns. When I got to protect the sheet .. the buttons no longer work and ask me to DEBUG. Is there a way to have macro buttons performing tasks AND have the sheet still protected?

In the protect sheet window .. am I have ticked is

Select Locked Cells
Select Unlocked Cells

But these are ticked as a default.

View 2 Replies View Related

Macro To Prompt Protect Sheet Box On All Tabs?

Jun 20, 2014

Macro to Run all tabs in a workbook and prompt a pop up asking about sheet protection for each tab, such as Select locked cells, and select unlocked cells.

View 8 Replies View Related

ADVERTISEMENT