#Excel macro active sheet full#
#Excel macro active sheet code#
#Excel macro active sheet password#

#Excel macro active sheet password#

If you don’t set one, you won’t need a password to unprotect it. Password: This lets you specify a case-sensitive password for your sheet or book.Here is a short explanation of each of these options:

#Excel macro active sheet full#

However, the Protect method does allow a number of optional arguments that allow you to select what not to protect while protecting/locking the entire sheet.īelow is the full syntax of the Worksheet.Protect method: ws.Protect (Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)Īll these arguments are optional, but you can use them to change how the protection should be done on your sheets (I will cover more examples of these later in this article) When you just use the Protect method, as shown above, it will protect the entire sheet (and you won’t be able to make any changes). Here, I have first set the variable ws to the sheet I want to protect and then used ws.Unprotect Here, I have qualified the sheet I want to protect by using Worksheets(“Sheet1”).Īlternatively, you can first assign the sheet to a variable and then protect it as shown below in the code: Sub ProtectSheet() Here, ws is a variable that represents the worksheet that you want to protect.įor example, if you want to protect Sheet1, you can use the below code. Unprotect All Sheets When the Workbook is Openedīelow is the syntax you need to use to protect a sheet using VBA: ws.Protect.Automatically Protect All Sheets When Closing the Workbook.Automatically Protect All Sheets When Saving the Workbook.Protect Sheet But Allow Filtering and Sorting.Protect All Sheets Except the Active Sheets.

#Excel macro active sheet code#

VBA Code Examples to Protect Sheets in Excel.Think about how you use Excel it is possible to activate a worksheet several dozen times during the course of a session. Remember that a macro defined in this way is run every time the worksheet is activated, not just the first time. In this new subroutine enter the macro you want executed whenever the worksheet is activated.The following code should appear in the code window: In the code window, use the right pull-down list (there are two pull-down lists just below the title bar of the code window) to select the Activate option.

(You may need to first open the VBAProject folder, and then open the Microsoft Excel Objects folder under it.) The code window for the worksheet should appear.

In the Project window, at the left side of the Editor, double-click on the name of the worksheet that you want to affect.

Display the VBA Editor by pressing Alt+F11.

In order to create a macro that runs when a worksheet is activated, follow these steps: For instance, you could create a macro that defined a custom menu structure whenever a particular worksheet is displayed. One of those events is when a worksheet is activated.

Excel allows you to define macros that are executed whenever certain well-defined events occur in the Excel environment.