Skip to main content

Excel Macro - Some Useful VBA Codes


To Protect All Sheets at once( but want to use Auto filters, sorting, pivot tables, formatting cells, etc)


Sub Protectallsheets()
Dim S As Worksheet
For Each S In ThisWorkbook.Sheets
S.Protect Password:="give your password", DrawingObjects:=False, Contents:=True, Scenarios:= _
      False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
      AllowFormattingRows:=True, AllowInsertingHyperlinks:=True, AllowSorting:=True, _
      AllowFiltering:=True, AllowUsingPivotTables:=True
Next
End Sub


Tips: - If you want some of your sheets to not be protected in that worksheet then put the following codes above the line “Next”

Sheetsheet number.Unprotect Password:="give your password"
Sheetsheet number.Unprotect Password:="give your password"
Sheetsheet number.Unprotect Password:="give your password"


To Un Protect All Sheets at once

Sub UnProtectAllsheets()
Dim S As Worksheet
For Each S In ThisWorkbook.Sheets
S.Unprotect Password:="give your password"
Next
End Sub
Labels:

Comments

Post a Comment

Popular posts from this blog

How to Calculate Detention of Vehicles considering the complex Date & Time in excel

Some times it is difficult to calculate some cases which deals with Date & Time and calculating the Detention charges is one of them. Please download the excel file (and do remember that it has been prepared on the basis the following basis.... which can be changed with the help of formulas) https://drive.google.com/file/d/0Bw1qNoQ8YzHZY3E5VVl1SUduWm8/view?usp=sharing
2 comments
Read more

Sai Sampurna Padhi - A Sweet Bubbly Girl fighting for her life at Cuttack

Hello Everyone, Today I want to share with you all about a girl named "Sai Sampurna Padhi" Some details of this girl: - Name                   : - Sai Sampurna Padhi Age                       : - 2.5 Years Residence           : - Bidanasi, Cuttack, Odisha Father's Name    : - Linku Padhi Mother's Name   : - Chandrika Mohapatra Father's Occupation    : - Auto Driver at Badambadi Bus Stand, Cuttack This girl is admitted to SCB Medical College because she is suffering from a very dangerous disease called "Blood Cancer" and as its at Stage-I doctors has prescribed to go for Bone Marrow transplant as soon as possible so that this can be cured. But the operation cost around 20-25 Lacks which they can't arrange. For the time being, every week she has need of blood, plasma, test & medicine which is less as it is a government ho...
Post a Comment
Read more

Tally Trial Balance Consolidation Utility in excel

Hello Friends, Financial Year has ended recently and many of the accounts & finance professionals are ready for preparing the Balance Sheets. Many companies have operations at multiple locations and they prepare the accounts accordingly and at the end consolidate the data and prepare the Final Accounts. Normally the trial balances of each locations are consolidated and on that basis the Profit & Loss Accounts & Balance Sheets are prepared. Sometimes, the consolidation of Trial Balance is lengthy, tricky and difficult part for the accounts professionals although its a simple process of summarizing the transactions. So I am sharing an excel utility where upto 10 nos of Trial Balances (Tally Generated) can be consolidated within a minute. The Excel utility is self explanatory, but some basic points are mentioned here: - 1. You need to export Group Names of each locations from tally and paste in this file. 2. You need to export Trial Balance of each location from...
Post a Comment
Read more