Speed up VBA code and include worksheet unprotect/protect

[Fwiz]

hi all,

i have a code snippet which seems to run slow, any way to improve this code and add in worksheet unprotect for each ws in array and then reprotect each ws once columns are hidden?


Application.ScreenUpdating = False

Dim Assets As Variant
Dim Asset As Variant
Dim ThisSheet As Worksheet
Set ThisSheet = ActiveSheet

Assets = Array("Combined", "T1", "IT2", "T3", "T4", "T5", "T6", "T7", "T8", "T9", "T10", "T11", "T12", "T13", "T14", "T15", "T16", "T17", "T18")

For Each Asset In Assets


If Range("AG11").Value = False Then
Columns("AG").EntireColumn.Hidden = True
Else
Columns("AG").EntireColumn.Hidden = False
End If
If Range("AF11").Value = False Then
Columns("AF").EntireColumn.Hidden = True
Else
Columns("AF").EntireColumn.Hidden = False
End If
If Range("AH11").Value = False Then
Columns("AH").EntireColumn.Hidden = True
Else
Columns("AH").EntireColumn.Hidden = False
End If
Next Asset
ThisSheet.Select

End Sub

 

[dmt32]

Hi,

Untested but see if following update to your does what you want:

Dim sh As Worksheet
    Dim i As Integer
    
    Application.ScreenUpdating = False
    For i = 1 To 20
        If i < 19 Then Set sh = Worksheets("T" & i) Else Set sh = Worksheets(Choose(i - 18, "Combined", "IT2"))
        With sh
            .Unprotect Password:=""
            .Columns("AG").EntireColumn.Hidden = Not CBool(.Range("AG11").Value)
            .Columns("AF").EntireColumn.Hidden = Not CBool(.Range("AF11").Value)
            .Columns("AH").EntireColumn.Hidden = Not CBool(.Range("AH11").Value)
            .Protect Password:=""
        End With
        Set sh = Nothing
    Next i
    Application.ScreenUpdating = True

add sheet password if required


Dave

 

[jkpieterse]

Or alternatively:

Sub foobar()
    Application.ScreenUpdating = False

    Dim Assets As Variant
    Dim Asset As Worksheet
    Dim ThisSheet As Worksheet
    Set ThisSheet = ActiveSheet

    Assets = Array("Combined", "T1", "IT2", "T3", "T4", "T5", "T6", "T7", "T8", "T9", "T10", "T11", "T12", "T13", "T14", "T15", "T16", "T17", "T18")

    For Each Asset In Worksheets(Assets)
        With Asset
            .Unprotect "YourPassWordGoesHere"
            .Columns("AF").EntireColumn.Hidden = Not .Range("AF11").Value
            .Columns("AG").EntireColumn.Hidden = Not .Range("AG11").Value
            .Columns("AH").EntireColumn.Hidden = Not .Range("AH11").Value
            .Protect "YourPassWordGoesHere"
        End With
    Next Asset
    ThisSheet.Select

End Sub

 

[Fwiz]

Thanks - I've tried adding in the last example, I'm getting an error in this part:

.Range("AF11").Value

states "Complile error, invalid or unqualified reference"

any ideas?

thanks

 

[jkpieterse]

Can you show us your current code? I tested the code I posted and there were no errors.