CODEWITHSUNDEEP
excelvba
Arspit
Arspit

Reputation: 21

VBA loop through all worksheets in workbook

I have tried following VBA code, where I want to run this code for all available worksheets in active workbook, I think I am making small mistake and as I am beginner I am not able to find it out, please help to fix it up

 Sub ProtectFormulas()


 Dim strPassword As String

 Dim ws As Worksheet

 For Each ws In Sheets

 ws.Activate

 .Unprotect

 .Cells.Locked = False

 .Cells.SpecialCells(xlCellTypeFormulas).Locked = True
 .Cells.SpecialCells(xlCellTypeFormulas).FormulaHidden = True

 .Protect AllowDeletingRows:=True

 strPassword = 123456
 ActiveSheet.Protect Password:=strPassword

 Next ws

 End With

 End Sub

Any help would be appriciated by word of thanks.

Upvotes: 2

Views: 437

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

There are 3 issues with your code:

  1. There is no With block.

  2. The following 2 lines will error if there is no formula in one of the sheets:

    .Cells.SpecialCells(xlCellTypeFormulas).Locked = True
.Cells.SpecialCells(xlCellTypeFormulas).FormulaHidden = True

    Because if there is no formula then .Cells.SpecialCells(xlCellTypeFormulas) is Nothing and therefore nothing has no .Locked and no .FormulaHidden methods.

  3. You mix using Sheets and Worksheets. Note that those are not the same!

    • Sheets is a collection of all type of sheets (worksheets, chart sheets, etc)
    • Worksheets is a collection of only type worksheet

    If you declare Dim ws As Worksheet and there is for example a chart sheet in your file, then For Each ws In Sheets will error because you try to push a chart sheet into a variable ws that is defined as Worksheet and cannot contain a chart sheet. Be as specific as possible and use Worksheets whenever possible in favour of Sheets.

The following should work:

Option Explicit

'if this is not variable make it a constant and global so you can use it in any procedure
Const strPassword As String = "123456" 

Sub ProtectFormulas()
    'Dim strPassword As String
    'strPassword = "123456"  'remove this here if you made it global

    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        With ws 
            .Activate 'I think this is not needed
            .Unprotect Password:=strPassword 'unprotect probably needs your password too or this will not work once the worksheet was protected.
            .Cells.Locked = False
            
            Dim FormulaCells As Range
            Set FormulaCells = Nothing 'initialize (because we are in a loop!)
            On Error Resume Next 'hide error messages (next line throws an error if no forumla is on the worksheet
            Set FormulaCells = .Cells.SpecialCells(xlCellTypeFormulas)
            On Error Goto 0 ' re-enable error reporting! Otherwise you won't see errors if they occur!
            If Not FormulaCells Is Nothing Then 'check if there were formulas to prevent errors if not
                FormulaCells.Locked = True
                FormulaCells.FormulaHidden = True
            End If

            .Protect AllowDeletingRows:=True, Password:=strPassword 
        End With   
    Next ws
 End Sub

Upvotes: 2

Related Questions