Sat 12 Dec 2015

Tips & Tricks 121 - Macro to Protect / Un-protect All or Selective Sheets

By |Saturday, December 12th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , |0 Comments

There may be scenarios where you want the facility to protect or unprotect all sheets in one go with added option to choose sheets where to perform this operation. Below is the code to perform this.

1. Save your file as .xlsm
2. ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the below code in this
6. ALT+F8 to display Macro Window
7. Run your Macro from here

'******* Protect / Un-protect Sheets *******

Sub ProtectAll()
    Dim Ws As Worksheet
    Dim Pass As String, Answer1 As String, Answer2 As String
    Answer1 = MsgBox("Do you want to Protect Selective Sheets", vbQuestion + vbYesNoCancel)
    Select Case Answer1
        Case vbCancel: Exit Sub
        Case vbYes
            Pass = InputBox("Input Your Password to Protect Sheets", "Password")
            For Each Ws In Worksheets
                Answer2 = MsgBox("Do you want to Protect " & Ws.Name, vbQuestion + vbYesNoCancel)
                Select Case Answer2
                    Case vbYes: Ws.Protect Password:=Pass, UserInterFaceOnly:=True
                    Case vbCancel: Exit Sub
                End Select
            Next Ws
        Case vbNo
            Pass = InputBox("Input Your Password to Protect Sheets", "Password")
            For Each Ws In Worksheets
                Ws.Protect Password:=Pass, UserInterFaceOnly:=True
            Next Ws
    End Select
End Sub



Sub UnProtectAll()
    Dim Ws As Worksheet
    Dim Pass As String, Answer1 As String, Answer2 As String
    Answer1 = MsgBox("Do you want to Unprotect Selective Sheets", vbQuestion + vbYesNoCancel)
    Select Case Answer1
        Case vbCancel: Exit Sub
        Case vbYes
            Pass = InputBox("Input Your Password to Unprotect Sheets", "Password")
            For Each Ws In Worksheets
                Answer2 = MsgBox("Do you want to Unprotect " & Ws.Name, vbQuestion + vbYesNoCancel)
                Select Case Answer2
                    Case vbYes: Ws.Unprotect Password:=Pass
                    Case vbCancel: Exit Sub
                End Select
            Next Ws
        Case vbNo
            Pass = InputBox("Input Your Password to Unprotect Sheets", "Password")
            For Each Ws In Worksheets
                Ws.Unprotect Password:=Pass
            Next Ws
    End Select
End Sub

 

Sun 25 Oct 2015

Article 28 - How to Unhide all Tabs (Worksheets)

By |Sunday, October 25th, 2015|Categories: Articles, VBA|Tags: , , , , , , , , |0 Comments

If you have many hidden worksheets and you want to hide them in one go, Excel doesn't provide an options to do so. There are many ways to to accomplish the same.

Option 1 - Use Custom Views

1. Unhide all of your sheets (you may use Option 2 or Option 3 below for the same also)
2. Views tab > Custom Views > Add and give this view a name (for example "ShowAll")

(more…)

Sat 19 Sep 2015

Tips & Tricks 109 - Macro to Change between A1 and R1C1 Notations

By |Saturday, September 19th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , , |1 Comment

Sometimes, you find that all of a sudden your column headings are changed into numbers and your formulas are Rs and Cs. And you need to change them back to alphabetical columns.

The option to do it very simple -

File > Excel Options > Formulas > Uncheck R1C1 Reference Style

1

But if that happens very often, you can use a macro which you can click and toggle between A1 and R1C1 style effortlessly. The macro code to do it one line only -

Sub ChangeReferenceStyle()
Application.ReferenceStyle = xlA1 + xlR1C1 - Application.ReferenceStyle
End Sub

Credit - Rick Rothstein (http://blog.contextures.com/archives/2009/12/04/excel-vba-switch-column-headings-to-numbers/)

The above link also talks about how to add the macro to QAT so that it is always available to you.