VBA to run in Protected Workbook

[andrewlau2881]

I have code that opens all the workbooks in a folder and hides columns based on whether the first cell in the column is blank, or not. It works perfectly as intended, unless the Workbook has protected sheets. I have password protected the sheets as they will require other users to enter data and I don't want them messing with the formatting. I know there is some code that will unprotect the sheet, and then protect it again before moving on to the next one. I just have no idea where to add the code.

As always, thank you for any help you can give me.

Sub m()

Const path As String = "folder address" '<<<Change<<<'



Dim f As String
Dim p As Long, i As Long

Application.ScreenUpdating = False
   
f = Dir(path & "*.xlsm*")
Do While Len(f) > 0
    With Workbooks.Open(path & f)
        For i = 4 To IIf(.Sheets.Count > 105, 100, .Sheets.Count)
            With .Sheets(i)
                For p = 2 To 31
                    .Columns(p).Hidden = .Cells(1, p).Value = ""
                Next p
            End With
        Next i
        .Close True
    End With
    f = Dir
Loop

Application.ScreenUpdating = True

End Sub

 

[bobsan42]

try this:

Sub m()

Const path As String = "folder address" '<<<Change<<<'

Dim f As String
Dim p As Long, i As Long
Dim protMode As Boolean

Application.ScreenUpdating = False
  
f = Dir(path & "*.xlsm*")
Do While Len(f) > 0
    With Workbooks.Open(path & f)
        For i = 4 To IIf(.Sheets.Count > 105, 100, .Sheets.Count)
            With .Sheets(i)
                protMode = .ProtectionMode
                If protMode Then .Unprotect "password_here"
                For p = 2 To 31
                    .Columns(p).Hidden = .Cells(1, p).Value = ""
                Next p
                If protMode Then .Protect "password_here"
            End With
        Next i
        .Close True
    End With
    f = Dir
Loop

Application.ScreenUpdating = True

End Sub

I would recommend using .Worksheets instead of .Sheets (all worksheets are sheets, but not always all sheets are worksheets e.g. charts)

 

[andrewlau2881]

try this:

Sub m()

Const path As String = "folder address" '<<<Change<<<'

Dim f As String
Dim p As Long, i As Long
Dim protMode As Boolean

Application.ScreenUpdating = False
 
f = Dir(path & "*.xlsm*")
Do While Len(f) > 0
    With Workbooks.Open(path & f)
        For i = 4 To IIf(.Sheets.Count > 105, 100, .Sheets.Count)
            With .Sheets(i)
                protMode = .ProtectionMode
                If protMode Then .Unprotect "password_here"
                For p = 2 To 31
                    .Columns(p).Hidden = .Cells(1, p).Value = ""
                Next p
                If protMode Then .Protect "password_here"
            End With
        Next i
        .Close True
    End With
    f = Dir
Loop

Application.ScreenUpdating = True

End Sub

I would recommend using .Worksheets instead of .Sheets (all worksheets are sheets, but not always all sheets are worksheets e.g. charts)

Unfortunately, this didn't work. I have changed .Sheets to .Worksheets (thanks for that)

 

[andrewlau2881]

I have also tried the code below, after reading some other threads, but it still gives the same error message. From everything I have read, I feel as though the code is in the right place.

Sub m()

Const path As String = "insert path" '<<<Change<<<'



Dim f As String
Dim p As Long, i As Long


Application.ScreenUpdating = False
  
f = Dir(path & "*.xlsm*")
Do While Len(f) > 0
    With Workbooks.Open(path & f)
        For i = 4 To IIf(.Sheets.Count > 105, 100, .Sheets.Count)
            With .Sheets(i)
            
                ActiveSheet.Unprotect Password:="insert password"
                For p = 2 To 31
                    .Columns(p).Hidden = .Cells(1, p).Value = "0"
                Next p
                ActiveSheet.Protect Password:="insert password"
                
            End With
         
        Next i
        .Close True
    End With
    f = Dir
Loop

Application.ScreenUpdating = True

End Sub

 

[andrewlau2881]

I have also tried the code below, after reading some other threads, but it still gives the same error message. From everything I have read, I feel as though the code is in the right place.

Sub m()

Const path As String = "insert path" '<<<Change<<<'



Dim f As String
Dim p As Long, i As Long


Application.ScreenUpdating = False
 
f = Dir(path & "*.xlsm*")
Do While Len(f) > 0
    With Workbooks.Open(path & f)
        For i = 4 To IIf(.Sheets.Count > 105, 100, .Sheets.Count)
            With .Sheets(i)
           
                ActiveSheet.Unprotect Password:="insert password"
                For p = 2 To 31
                    .Columns(p).Hidden = .Cells(1, p).Value = "0"
                Next p
                ActiveSheet.Protect Password:="insert password"
               
            End With
        
        Next i
        .Close True
    End With
    f = Dir
Loop

Application.ScreenUpdating = True

End Sub

I have been testing this and have found that:

1. If there is only one worksheet that is protected, the code runs fine (unprotects --> runs --> protects again after)

2. When there is more than one worksheet that is protected, the code (unprotects the first one --> runs --> does not protect again after --> code stops running and does not move onto the second worksheet)

So, it seems like whatever way the Unprotect then Protect is working is limiting it to only running through one worksheet.

 

[bobsan42]

Unfortunately, this didn't work. I have changed .Sheets to .Worksheets (thanks for that)

You could be more specific about the details - what didn't work?.

I have been testing this and have found that:

1. If there is only one worksheet that is protected, the code runs fine (unprotects --> runs --> protects again after)

2. When there is more than one worksheet that is protected, the code (unprotects the first one --> runs --> does not protect again after --> code stops running and does not move onto the second worksheet)

So, it seems like whatever way the Unprotect then Protect is working is limiting it to only running through one worksheet.

While you're looping through the sheets you're always unprotecting the Active sheet (which does not change in your code).
And you're protecting the sheets regardless of whether it was protected or not.