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
ADVERTISEMENT
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
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
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
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
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
Aug 2, 2014
I've written a little VBScript that generates several hundred Excel 2013 worksheets. In order to protect the users from themselves I protected certain cells. Everything works as expected, except when I discovered that the password I used to protect the worksheet doesn't work to unprotect it. If it matters, I'm not trying to unprotect programmatically, just opening Excel and going that route.
Here's a snippet of my code:
objWS.protect Password="abc123"
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
Jan 21, 2009
Create a button for protecting the worksheet and a button for unprotecting the worksheet
View 13 Replies
View Related
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
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
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
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
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
View 9 Replies
View Related
May 14, 2014
i want to hide and unhide headings from all sheets at once (Excel 2013)
View 8 Replies
View Related
Jul 9, 2014
I would like to mirror a range of cells between two sheets in the same workbook, so that if the data is manually input into one of the cells in range (E5:H11) in 'Sheet 1', it is automatically updated in the corresponding cell in (H33:K39) of 'Sheet 2', and vice versa.
Using Office 2013. T
View 6 Replies
View Related
Feb 28, 2014
Is there a macro available which will create a pre-defined name for a pivot sheet, which will contain the word "Pivot" followed by a underscore "_" and the name of the sheet on which the pivot is applied.
Example: I have data on a sheet called "Salary" and put a pivot on "Salary" on a new sheet, the new sheet should be auto named as "Pivot_Salary"
Am using Excel 2013
View 1 Replies
View Related
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
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
Dec 2, 2013
We have 3 PCs, all running MS Office 2013. On 1 of these machines, it is doing strange things with formatting. If you open a document or try to paste anything into certain documents, it decides everything is currency format and assigns all sorts of wrong formatting to the entire sheet, or the entire document. There may be some cells in the doc that are indeed currency, but only a small proprtion. If I open a new, fresh document and paste into that document, it does not do this, it seems to work normally, only applying currency formatting where it might be applicable. On some larger docs that have this issue, no matter what I do, it just continues to apply these strange settings.
View 2 Replies
View Related
Mar 25, 2014
I have a workbook with five sheets. One sheet is the Master and then one sheet each for four organizations. Each organization will populate data for their own sheet and I want Excel to automatically populate the Master with the info from each individual sheet.
I tried using and modifying the vba from this post [URL]). My workbook varies from the original in that the first seven rows are header rows so the first cell to be edited in each sheet is A8, I have 38 columns instead of 10, and I am using Excel 2013 instead of Excel 2003.
I figured out that I needed to change the vba to look like this to start copying from the first cell (A8) and to populate the master:
This seems to work, but until the first cell (A8) is populated in the organizations' sheets, it messes up the prior row on the Master (Row 7).
I've also noticed that undo becomes unavailable when switching between sheets and also copy/cut & paste between sheets doesn't work so I can't correct the way the script messes up the format initially (this I presume is due to the Copy Destination in the vba?).
View 1 Replies
View Related
