Hide Columns with VBA

0
votes

I have an excel file with 2 sheets, and in sheet1 on cell B5 I have a value that changes automatically according other cell. And my goal is to hide columns Y:AN or AQ:BF on sheet2 if B5 on sheet1 is 3 or 2 respectively. I applied the code below, but doesnt work when I protect sheet2.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "B5" Then
    Select Case Target.Value
        Case "3"
            Columns("Y:AN").EntireColumn.Hidden = True
        Case "2"
            Columns("AQ:BF").EntireColumn.Hidden = True
        Case Else
            Union(Columns("Y:AN"), Columns("AQ:BF")).EntireColumn.Hidden = False
    End Select
End If

End Sub

2
excelvba
Either unprotect/protect programmatically, or change the protection to be UserOnlyInterface:=True. – BigBen
If you change the value from 3 to 2 it will not unhide columns "Y:AN". Is that intentional? – Tim Williams
You say cell B5 I have a value that changes automatically according other cell. Does that mean B5 contains a formula? If so, B5 changing won't fire the Change event. You'll need to use the Calculate event. There are many posts about that on SO – chris neilsen
If the event does fire, and the code is in Sheet1 (which it must be to respond to Sheet1!B5 changing) then your code will hide columns on Sheet1. You'll need to qualify them with the required worksheet reference – chris neilsen
Also, your code will error if >1 cells are changed at once, eg with a Copy/Paste – chris neilsen

2 Answers

0
votes

use unprotect method in your code and protect after prcedure

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "B5" Then
    thisworkbook.worksheets("").unprotect  ' enter sheet name, if protected by password .unprotect("password")
    Select Case Target.Value
        Case "3"
            Columns("Y:AN").EntireColumn.Hidden = True
        Case "2"
            Columns("AQ:BF").EntireColumn.Hidden = True
        Case Else
            Union(Columns("Y:AN"), Columns("AQ:BF")).EntireColumn.Hidden = False
    End Select
    thisworkbook.worksheets("").protect   ' enter sheet name, if protected by password .protect("password") //check docs
End If
0
votes

protect method concerns worksheets not sheets. You need add fragment

worksheets(NAME).protect(password)

for example

Private Sub Worksheet_Calculate() 
dim ws as worksheet
dim password as string
password = "qwerty"  ' add password
set ws=thisworkbook.worksheets("Sheet2")
If Range("B5").Value = "2" Then 
Sheet2.Columns("AQ:BF").EntireColumn.Hidden = True 
ElseIf Range("B5").Value = "3" Then 
Sheet2.Columns("Y:AN").EntireColumn.Hidden = True 
Else Sheet2.Columns("AQ:BF").EntireColumn.Hidden = False
Sheet2.Columns("Y:AN").EntireColumn.Hidden = False 
End If 
ws.protect(password )
End Sub