Amend Current Macro To Protect All Sheets...
Mar 4, 2007
I have a Macro to protect all the Months sheets in my Time sheet to stop users deleting formulas, This year I've added a new sheet with a yearly calender so users can track Hols etc but when I run the Macro it protects this sheet "2007-2008". How can I amend the Macro so it protect all sheets except "2007-2008". My Macro is below:
Sub ProtectAll()
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="KITCHENsink19591968", DrawingObjects:=True, Contents:=True, Scenarios:=False
Next ws
ActiveWorkbook.Protect Password:="KITCHENsink19591968", Structure:=True, Windows:=False
Application.ScreenUpdating = True
End Sub
View 4 Replies
ADVERTISEMENT
Feb 5, 2009
I have found and adapted a macro (below) that searches all worksheets for a specific value (entered by the user in a specfic cell) then displays a list of hyperlinks to the matching cell locations.
I now have a couple of sheets I don't want to be included in this macro ("Master List" and "Pivot Table") but I'm really struggling to find a way of amending the macro to exclude these. I've searched and tried a couple of ways but I get errors and I think I'm not knowledgable enough to know where to put each bit of code.
View 3 Replies
View Related
Mar 20, 2008
I have a workbook with some sheets password protected and other sheets not protected.
I have a macro that unprotects the sheets, but it requires the password to be typed multiple times.
I want to put the password into the macro so it doesn't require manual entry of the password lots of times.
Each sheet has the same password, so I would expect to enter it for the first sheet only.
What I have came from a simple macro recorder selecting each sheet in turn:
Sheets("wages").Select
ActiveSheet.Unprotect
Sheets("Comparative").Select
ActiveSheet.Unprotect
View 9 Replies
View Related
Sep 16, 2008
i use a macro to protect several sheets in a workbook.
When i protect the sheets, i only want to be able to cells and nothing else. How should i mdify the code below to do this? I tried record the macro and un checking the "select locked cells" box which is one of the only 2 boxes checked. It works, but then when i save the wb and re-open it it allows me to select locked cells.
Sheets("FF - WC").Select
ActiveSheet.PROTECT DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("FF - UR").Select
ActiveSheet.PROTECT DrawingObjects:=True, Contents:=True, Scenarios:=True
View 9 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
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
May 8, 2008
I tried this macro, written by Leith, to organize a workbook of over 100 worksheets, all named as a 3 digit number. (001, 002, 007, 004, 018, 12, etc.)
For some reason, there was no order to the sorting. Can the macro above be modified to sort my sheets in numeric order?
View 9 Replies
View Related
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
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
May 12, 2014
Macro below - it now works and selects all the data in spreadsheet and converts to Proper Case
However I also need a macro to select all except the last column of data and convert all except the last column to Proper Case
It is for Post / Zip code info. so wish to keep it in Upper Case.
What I have already is:
Code:
Sub Propercase()
Dim LastRow As Long, LastColumn As Long
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastColumn = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Dim r As Range, c As Range
Set r = Range("A1").Resize(LastRow, LastColumn)
[Code] .....
I was hoping I could easily amend the above to select all except last column.
View 3 Replies
View Related
Mar 31, 2014
I have a user form we are using to transfer data into an excel spread sheet. When transferring the date from a text field it is formatted as general, I need this to be a date format DDMMYYY. The cell needing formatting is N2
View 5 Replies
View Related
Nov 1, 2008
I have a workbook that has a sheet for every day of the month (1 to 31).
When it opens it automatically activates the sheet for the day corresponding today.
What I'm missing is how to enable users to only update that sheet.
Example today is the 1st, sheet 1 should be unprotected but all others protected and off course tomorrow it should be sheet 2.
View 9 Replies
View Related
Jan 17, 2009
I wanted and the solution is on two sheet which I grouped, make copied and move into a new book. This is fine, but then I want my code to lock/protect all of my sheet in my program document, this can't be done with my current code because the sheets are group.
View 9 Replies
View Related
Mar 15, 2008
I have a workbook with several Sheets. I wish to automatically protect all sheets with specific user permissions, i.e. Select Unlocked Cells, Insert Rows, Use Auto Filter and Edit Objects, as I would have available when using the manual approach. Also, I wish to invoke a Password in the process.
Additionally, I need this code to operate when the file is Saved or Closed... transparent to the user.
View 9 Replies
View Related
Jun 19, 2006
How to protect multiple sheets in a workbook and not having to protect them 1 by 1. I have copied and pasted all the different macro's/visual basic script that has been posted for that purpose, but I can't get it to work and I'm always getting an error. The error from visual basic says "compile error: invalid outside procedure" and then the visual basic screen shows the following:
Range ("AM52")
If Ans >= 0 Then
"+."
It does not like the "AM52" part because it is highlighted in the error message.
Also, the error I get from recording my own macro step by step is "Run time error 40036" from visual basic.
View 8 Replies
View Related
Dec 19, 2007
Is there anyway to prevent certain sheets from be deleted?
i know there is protect workbook, but that prevents the user from deleting any sheet
View 12 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
Feb 19, 2012
Is there a simple one line VBA code to protect all sheets when a workbook is opened?
View 5 Replies
View Related
Jul 29, 2003
I have a workbook with 7 tabs called "Sat" thro to "Sun". I'd like a macro which
a) protects the sheets as an array (as opposed to individually)
b) preferably uses a password
Using Excel 97 on Windows NT
View 9 Replies
View Related
Sep 12, 2006
I have a excel file with around 8 sheets in it and this file puted in the network to be shared with 8 persons So i need to make an individual password for each one to enter to his specific sheet and have full authorization to make anything in it and this done using userform where each one when open the file in the network , a user form open in first stage to select his name and put his own password with the ability to let each one to make his own password or change it from the userform
this file is shared so it may more one user open it and it may be there an save funtion at same time ( same time & same activities )
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
Mar 20, 2013
I have a workbook with 6 tabs. The workbook is designed to be shared but I want to password protect the last 4 sheets so that they can only be accessed by certain people. The people who have access need to be able to edit the sheet.
View 8 Replies
View Related
May 13, 2014
I have the following code (provided here some years ago) which works fine. But I now need to modify it to include an optional password and to allow sort of unprotected cells on manually chosen sheets.
[Code] ....
View 5 Replies
View Related
Sep 9, 2009
I'm trying to protect the contents of columns in multiple sheets in a workbook. I've tried to group the columns ( - months, i.e. sept, oct, nov 09 through to march 10) but when I go to Protection options the 'sheet' protection option is not available, all I can do is protect or unprotect the entire workbook.
I've also tried to protect the workbook, but individually set the specific columns in each sheet to be protected (repetitious but seemingly unavoidable), but this doesn't work well either as there's now a mix of a protected workbook and protected columns, both with individually set passwords to unprotect... which seems a bit silly.
There must be a way around this? - the columns are H, J, K and L, and the entire contents of the summary sheet '2009-10 Financial Year'... basically I'd like to be able to protect all of the contents of all of the cells here, ideally just with one password.
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
Apr 8, 2009
I need to change VBA to Password Protect all sheets to allow user to select protected. I have:
View 2 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
Nov 1, 2009
I have an Excel 2007 workbook which has five sheets in. I just wondered whether it is possible to hide all five of them when the workbook is first opened. Also I would like the user to be asked for a password when they wish to unhide a sheet, with the password being different for each of the sheets.
Is there also a way to do this which won't be affected by Excel disabling all Macros when the application initially opens.
View 6 Replies
View Related
Jan 13, 2008
how to rename the sheets according to the current time 1201pm, 1202pm etc. i need a macro.
View 9 Replies
View Related
Jan 14, 2014
I have a workbook that we are using to track some training, the spreadsheet has the date of the course at the top and the trainee's name in column A
Additional sheets will be added each time a class is scheduled labeled with the date of the class and in the same format as the ones included titled: "1.13.14" & "1.20.14".
I need to add a table to the workbook that will collect ever new hire from column A and the Date of their class from cell "B1" in each sheet.
Copy of SLC-BD Peer Training 1.xlsx
View 4 Replies
View Related
