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
ADVERTISEMENT
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
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
May 10, 2012
I wrote a macro to select multiple sheets by name and hide them, but I keep getting the following error message:
"Object variable or with block variable not set".
Below is the macro:
Sub HideSheets1A()
Dim ws As Worksheet
Application.DisplayAlerts = False
If ws.Name = "Variance Evaluation" Or "Investment" Or "Costs & Incentives" Or "Revenues Total" Then ws.Visible = False
End Sub
I use Excel 2003
View 5 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
Feb 23, 2008
I often need to use Paste Special . .. Values, using Excel 2003. So I recorded a Macro into "Personal.xls" and assigned a shortcut key combination "CTRL-Shift-V" so that it would always be available. I first select one or more cells and hit CTRL-C to copy to clipboard, click on another cell, then hit CTRL-Shift-V to run the following macro:
VB : Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
It often works . . . and it often fails. When it is working it seems to keep working over and over. When it is failing it keeps failing. So it is almost as if there are two "modes" of operation, which for lack of terms, for this post I will coin the terms "fail mode" and "fixed mode". Once I am in one of the two modes, it stays there for a while. When it is in the "fail mode", I always get the dreaded:
Run-time error 1004: "Pastespecial method of Range class failed".
Since this problem is very intermittent, I believe this is why there are so many posts in the Internet about this, and so many people are replying back "it works fine for me". I have seen many solutions offered but none work so far for just a simple shortcut key to do a PasteSpecial :Values. Some solutions "work" . . . such as adding the PasteSpecial button to the Toolbar, or pressing ALT-E-S-V <Enter> - but both of these bring up the Paste Special dialog box which I want to avoid.
I spent days trying the many solutions offered but so far all I have found is a trick to convert Excel from "fail mode" to "fixed mode". The trick is to add a line to the macro to first do a "Paste Special: Format:
VB:
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Then select and copy any cell/s, click on another cell, and run the macro just ONCE - it will paste the formatted value successfully and you are now in the "fixed mode". Now you can remove the PasteSpecial:Format line that you just added, to get back to the original macro with just the "Paste Special: Values" line:
VB : Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Now Excel is in the "fixed mode" and the original macro will once again start working properly. BUT only for a while !! Then some time later, usually within a day or a couple hours . . . Excel goes back into that "mode" where once again the macro fails repeatedly. I have been unable to isolate what causes the mode to return to failure.
Any simple way to either change the code so that it ALWAYS is successful, why this often is not successful ? I believe that the fact that adding in the Paste Special : Format code and running it just once to fix the problem, also could be a clue as to what causes this problem to "sometimes" occur.
View 9 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
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
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
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
Jul 12, 2013
I have a spreadsheet with over a hundred tabs, each of which need to be password protected (same password).
I found code on CFO.com that got me half way there. It protects all the spreadsheets, but it does not password protect the macro itself, meaning that anybody can step into the macro and unlock everything at the click of a button.
The code looks like this.
VB:
Sub ProtectAllSheets()
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="secret123"
Next ws
MsgBox "All Worksheets Protected"
[Code] ....
What can I do to prevent people from stepping into the macro, or prompt a password to actually use the macro itself?
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
Feb 9, 2014
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
View 12 Replies
View Related
Mar 20, 2014
So I am trying to pull data from multiple sheets. I've gone through the thread, but haven't found an answer yet (or didn't work hard enough). On Sheet1, yellow highlighted column, I am trying to look up the I.D. Code for 36 month residuals. As you can see, I have to use multiple conditions on different worksheets.
I have to use most of Sheet1 columns to find the answer. I just can't figure the formula out.
I don't have Excel 2007, only 2003 I have.
View 1 Replies
View Related
Mar 15, 2007
I convert text file into Excel. Then what I want is to look in column C for cell value “UNT:” (it is chopped word account: ) and copy value what is in the same row next column D into column K.
Then in column K copy down the value until next not empty cell.
My problem is that it works on Excel 97 and 200 but 2003 doesn’t work.
View 9 Replies
View Related
Jun 24, 2012
I need to calculate the average spend on a day of the week over the month, so all Monday's or all Tuesday's, etc. One sheet is one week so I need to average b16 on 6 sheets as an example.
I used =AVERAGE('WEEK1:WEEK6'!B18) to calculate average over the six Monday's. The issue is, as in other posts, how do I ignore the cells that have a zero or null value.
I've tried adjusting this which was in 1 post
=AVERAGE(IF($C$2:$CA$2=C62,IF($C$25:$CA$25"",$C$25:$CA$25
with this
=AVERAGE(IF(1+1=2,IF('WEEK1:WEEK6 '!B180,'WEEK1:WEEK6 '!B18))) which returns #REF!
this from another post
=SUM('WEEK1:WEEK7 '!B18)/COUNTIF('WEEK1:WEEK7 '!B18,"0") which returns #VALUE!
and this
=AVERAGE(IF('WEEK1:WEEK7 '!B180,'WEEK1:WEEK7 '!B18)) which returns #NAME?
The cells on each sheet are sum formulas for other cells on the sheet not just numbers on their own.
Using windows 7, excel 2003
View 3 Replies
View Related
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
Nov 27, 2009
Toggle "Protect/Unprotect sheet" in macro
Correct the following macro. It does not work.
View 2 Replies
View Related
Mar 15, 2013
Is it possible to protect an inserted text box from being moved, stretched, etc. while allowing data input into the cell/s? I'm using a 2003 version of excel.
I've attached the sheet in question. The yellow text boxes are what I want to lock, yet still allow data input.
excel tip example.xls‎
View 2 Replies
View Related
Feb 24, 2014
I have a sheet witch has a number of tick boxes and depending on the response a number of hidden rows may open to allow further info to be recorded, how do I protect the sheet in excel 2003 as unlocking certain cells & protecting the sheet will not work.
View 1 Replies
View Related
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
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 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
