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...............................
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
I am looking for a code that would enable switching of sheets. For example, on Sheet1 (Master Sheet) I have got Industry1 to Industry3 and the companies within the Industries are on Sheet2 to Sheet4. When someone clicks Industry1 on Sheet1, it should navigate to Sheet2 (where it gives a list of companies within the industry), and when Industry2 on Sheet1 is clicked, it should navigate to Sheet3 and so forth..
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.
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.
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?
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?
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
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
1) Force user to enable macros. 2) keep three worksheets very hidden all the time. 3) passwrd protect vba
I have been using this code to force user to enable macros but this unhides all sheets other than "macros not enabled" sheet. I need three worksheets to remain very hidden all the time. How do I do this.
Option Explicit Dim ws As Worksheet Private Sub Workbook_BeforeClose(Cancel As Boolean) Worksheets("Macros Not Enabled").Visible = True For Each ws In Worksheets If ws.Name "Macros Not Enabled" Then ws.Visible = xlVeryHidden Next End Sub Private Sub Workbook_Open() For Each ws In Worksheets If ws.Name "Macros Not Enabled" Then ws.Visible = True Next Worksheets("Macros Not Enabled").Visible = xlVeryHidden End Sub
to avoid that the change of A1's value triggers a calculation event and gets me into a loop.
However, something in my project is setting EnableEvents to False and not resetting it. I've searched the entire project for all instances and each "=False" is swiftly followed by a "=True". The code is not causing an error
The only way to get around this is to have a user press a button to re-enable events, but I can't tell them to press the button because there are no events to trigger the message!
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.
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.
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.
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.
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.
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 )
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).
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.
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.
I have converted an Excel file from 2003 to 2007. The file now has a .xlsm extension as it contains macros. I have "enabled all macros" and "trusted access to the VBA project object model". Whilst open, I have tested the macro buttons and they work.
I then close the file and re-open it, and the macro's will not work. A message appears along the lines of "macros are anabled" or "macro is not found". I have double-checked the settings and everything appears normal.
