Could anyone please simplify this VBA code?

J

justanotheruser

Board Regular
Joined
Aug 14, 2010
Messages
96
Hi all,

The following code is used to show/hide columns based on the value of cell E15. I need to apply the following logic to unhide all the columns from E:P and then hide specific columns depending on E15's value to 20 sheets.

Code: 
If Target.Address(False, False) = "E15" Then
    If Target.Value Like "####_CYF1" Then
    Sheets("1 P&L Entity").Columns("E:P").EntireColumn.Hidden = False
    Sheets("1 P&L Entity").Columns("E:F").Hidden = True
    
    ElseIf Target.Value Like "####_CYF2" Then
    Sheets("1 P&L Entity").Columns("E:P").EntireColumn.Hidden = False
    Sheets("1 P&L Entity").Columns("E:J").EntireColumn.Hidden = True
    
    ElseIf Target.Value Like "####_CYF3" Then
    Sheets("1 P&L Entity").Columns("E:P").EntireColumn.Hidden = False
    Sheets("1 P&L Entity").Columns("E:J").EntireColumn.Hidden = True
    
    ElseIf Target.Value Like "####_CYF4" Then
    Sheets("1 P&L Entity").Columns("E:P").EntireColumn.Hidden = False
    Sheets("1 P&L Entity").Columns("E:M").EntireColumn.Hidden = True
    
    Else
    Sheets("1 P&L Entity").Columns("E:P").EntireColumn.Hidden = False
    
    End If
    End If

So for example, if E15 was 2011_CYF1, then columns E to P should be unhidden on the sheet called 1 P&L Entity, and then columns E:F should be hidden. However, is it possible to get this code to do it instead of just for one sheet, "1 P&L Entity" to a list of sheets so that I don't need multiple lines of code where the sheet name has changed? i.e. Columns E to P should be unhidden on sheets "1 P&L Entity", "2 Balance", "Sheet3", "Sheet 4" etc.

Unfortunately I can't do it for all the sheets in the workbook as some of them shouldn't have the columns hidden, so it is possible to put all the sheet names in one argument (is that the right word?) of code, instead of copying down multiple times and just changing the sheet name.

This is what I'd rather not do:

Code: 
 If Target.Value Like "####_CYF1" Then
    Sheets("1 P&L Entity").Columns("E:P").EntireColumn.Hidden = False
    Sheets("1 P&L Entity").Columns("E:F").Hidden = True
    Sheets("2 Balance").Columns("E:P").EntireColumn.Hidden = False
    Sheets("2 Balance").Columns("E:F").Hidden = True
    Sheets("Sheet3").Columns("E:P").EntireColumn.Hidden = False
    Sheets("Sheet3").Columns("E:F").Hidden = True
    Sheets("Sheet4").Columns("E:P").EntireColumn.Hidden = False
    Sheets("Sheet4").Columns("E:F").Hidden = True

Thanks in advance for your help! :)
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Even if I could just put it as
Code: 
 Sheets("1 P&L Entity", "2 Balance", "Sheet3", "Sheet4).Columns
or something like that, I'd really appreciate it! :)
 
Upvote 0
Something like this possibly...

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim wss() 'Array of worksheets
Dim ws As Worksheet
    If Target.Address(False, False) = "E15" Then
 
        'First E15 condition
        If Target.Value Like "####_CYF1" Then
            wss = Array("1 P&L Entity", "2 Balance") 'Use this to list the sheets to affect
            For ShtIdx = 1 To UBound(wss)
                Set ws = Sheets(wss(ShtIdx))
                With ws
                    .Activate
                    'Modify these for each set of columns to show/hide
                    .Columns("E:P").EntireColumn.Hidden = False
                    .Columns("E:F").Hidden = True
                End With
            Next ShtIdx
        End If
        'Second E15 condition
        If Target.Value Like "####_CYF2" Then
            wss = Array("2 P&L Entity", "3 Balance")
            For ShtIdx = 1 To UBound(wss)
                Set ws = Sheets(wss(ShtIdx))
                With ws
                    .Activate
                    'Modify these for each set of columns to show/hide
                    .Columns("E:P").EntireColumn.Hidden = False
                    .Columns("E:F").Hidden = True
                End With
            Next ShtIdx
        End If
    End If
Application.ScreenUpdating = True
End Sub

Note: I'm using Option Base 1
 
Last edited:
Upvote 0
Thanks for that code, it works for the sheet called "2 Balance" and "3 Balance" in your examples, but it doesn't work for the first sheet in the list - i.e. if I add 3 to the list in the Array, it will do it for the second and third sheet but not the first one - any ideas? :) Thanks again for your help!
 
Upvote 0
modifying slightly to use lbound instead of hard-coded lower bound of array. Shouldn't care what the Option Base is with this version.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim wss() 'Array of worksheets
Dim ws As Worksheet
    If Target.Address(False, False) = "E15" Then
        
        'First E15 condition
        If Target.Value Like "####_CYF1" Then
            wss = Array("1 P&L Entity", "2 Balance") 'Use this to list the sheets to affect
            For ShtIdx = LBound(wss) To UBound(wss)
                Set ws = Sheets(wss(ShtIdx))
                With ws
                    .Activate
                    'Modify these for each set of columns to show/hide
                    .Columns("E:P").EntireColumn.Hidden = False
                    .Columns("E:F").Hidden = True
                End With
            Next ShtIdx
        End If
        'Second E15 condition
        If Target.Value Like "####_CYF2" Then
            wss = Array("2 P&L Entity", "3 Balance")
            For ShtIdx = LBound(wss) To UBound(wss)
                Set ws = Sheets(wss(ShtIdx))
                With ws
                    .Activate
                    'Modify these for each set of columns to show/hide
                    .Columns("E:P").EntireColumn.Hidden = False
                    .Columns("E:F").Hidden = True
                End With
            Next ShtIdx
        End If
    End If
Sheets(1).Select
Application.ScreenUpdating = False
End Sub
 
Upvote 0
Thank you, works perfectly - is there a way to say if it is not one of the defined ones (i.e. not ####_CYF1, ####_CYF2 etc to then do .Columns("E:P").EntireColumn.Hidden = False?

Thanks again! :)
 
Upvote 0
You can do a generic 'reset' loop on all sheets before you parse what is in E15 ....

Code: 
    If Target.Address(False, False) = "E15" Then
        
[COLOR=blue]        For Each Worksheet In Worksheets
            With Worksheet
                .Columns("E:P").EntireColumn.Hidden = False
            End With
        Next
[/COLOR]        
        'First E15 condition

This would eliminate the need for these lines
Code: 
                    'Modify these for each set of columns to show/hide
[COLOR=blue]                    .Columns("E:P").EntireColumn.Hidden = False
[/COLOR]                    .Columns("E:F").Hidden = True
 
Upvote 0
Hi,

Thanks for your response. I tried your code, but there is a 1004 Runtime error, "Unable to set the Hidden property of the Range class". The code is a private sub in a sheet called "Title", which is not protected, but the workbook might be protected - although I think it is because it doesn't know which sheets to unhide the entire column bit for? The line in red is highlighted in the debugger.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
    Dim LR As Long, i As Long

Dim wss() 'Array of worksheets
Dim ws As Worksheet
    If Target.Address(False, False) = "E15" Then
    
          For Each Worksheet In Worksheets
            With Worksheet
            [COLOR="Red"]    .Columns("E:P").EntireColumn.Hidden = False[/COLOR]
            End With
        Next
        
        'First E15 condition
        If Target.Value Like "####_CYF1" Then
            wss = Array("1 P&L Entity", "2 Budget BS", "3 BS Assets") 'Use this to list the sheets to affect
            For ShtIdx = LBound(wss) To UBound(wss)
                Set ws = Sheets(wss(ShtIdx))
                With ws
                    .Activate
                    'Modify these for each set of columns to show/hide
                    .Columns("E:F").Hidden = True
                End With
            Next ShtIdx
        End If
        'Second E15 condition
        If Target.Value Like "####_CYF2" Then
            wss = Array("1 P&L Entity", "2 Budget BS", "3 BS Assets")
            For ShtIdx = LBound(wss) To UBound(wss)
                Set ws = Sheets(wss(ShtIdx))
                With ws
                    .Activate
                    'Modify these for each set of columns to show/hide
                    .Columns("E:I").Hidden = True
                End With
            Next ShtIdx
        End If
        'Third E15 condition
        If Target.Value Like "####_CYF3" Then
            wss = Array("1 P&L Entity", "2 Budget BS", "3 BS Assets")
            For ShtIdx = LBound(wss) To UBound(wss)
                Set ws = Sheets(wss(ShtIdx))
                With ws
                    .Activate
                    'Modify these for each set of columns to show/hide
                    .Columns("E:L").Hidden = True
                End With
            Next ShtIdx
        End If
        
        'Fourth E15 condition
        If Target.Value Like "####_CYF4" Then
            wss = Array("1 P&L Entity", "2 Budget BS", "3 BS Assets")
            For ShtIdx = LBound(wss) To UBound(wss)
                Set ws = Sheets(wss(ShtIdx))
                With ws
                    .Activate
                    'Modify these for each set of columns to show/hide
                    Columns("E:L").Hidden = True
                End With
            Next ShtIdx
        End If
        
          'Fifth E15 condition
       ' If Target.Value Like "####_BUD" Then
      '      wss = Array("1 P&L Entity", "2 Budget BS", "3 BS Assets")
     '       For ShtIdx = LBound(wss) To UBound(wss)
    '            Set ws = Sheets(wss(ShtIdx))
    '            With ws
    '                .Activate
                    'Modify these for each set of columns to show/hide
    '                .Columns("E:P").EntireColumn.Hidden = False
    '                      End With
    '        Next ShtIdx
    '    End If
        
         'Sixth E15 condition
     '   If Target.Value Like "####_NYF" Then
     '       wss = Array("1 P&L Entity", "2 Budget BS", "3 BS Assets")
     '       For ShtIdx = LBound(wss) To UBound(wss)
     '           Set ws = Sheets(wss(ShtIdx))
      '          With ws
      '              .Activate
                    'Modify these for each set of columns to show/hide
       '             .Columns("E:P").EntireColumn.Hidden = False
       '                   End With
      '      Next ShtIdx
      '  End If
         'Seventh E15 condition
         
        If Target.Value Like "####_LPR" Then
            wss = Array("1 P&L Entity", "2 Budget BS", "3 BS Assets")
            For ShtIdx = LBound(wss) To UBound(wss)
                Set ws = Sheets(wss(ShtIdx))
                With ws
                    .Activate
                    'Modify these for each set of columns to show/hide
                    .Columns("E:O").Hidden = True
                          End With
            Next ShtIdx
        End If
        
    End If

I commented out Like ###_BUD and ####_NYF as these should only do the .Columns("E:P").EntireColumn.Hidden = False -- so if the function you provided me can be fixed, then all that code will be able to be removed, lovely shortening of the code! The wss = Array will always remain constant, is there a way so that I only have to put that once as well?

Thanks again for your help, you're great! :)
 
Last edited:
Upvote 0
Let's try an even shorter version, easier to read imho:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim LR As Long, i As Long
    Dim wss()               'Array of worksheets
    Dim ws As Worksheet
 
    If Target.Address(False, False) = "E15" Then
    Application.ScreenUpdating = False
 
        For Each Worksheet In Worksheets
            Worksheet.Activate
            With Worksheet
                .Columns("E:P").EntireColumn.Hidden = False
            End With
        Next
 
        wss = Array("1 P&L Entity", "2 Budget BS", "3 BS Assets")
        Right4 = Right(Target.Value, 4)
        For ShtIdx = LBound(wss) To UBound(wss)
            Set ws = Sheets(wss(ShtIdx))
            ws.Activate
            ws.Columns("E:P").EntireColumn.Hidden = False
            Select Case Right4
            Case Is = "CYF1"
                ws.Columns("E:F").Hidden = True
            Case Is = "CYF2"
                ws.Columns("E:I").Hidden = True
            Case Is = "CYF3"
                ws.Columns("E:L").Hidden = True
            Case Is = "CYF4"
                ws.Columns("E:L").Hidden = True
'            Case Is = "_BUD"
'                ws.Columns("E:P").EntireColumn.Hidden = False
'            Case Is = "_NYF"
'                ws.Columns("E:P").EntireColumn.Hidden = False
            Case Is = "_LPR"
                ws.Columns("E:O").Hidden = True
            End Select
        Next ShtIdx
        Application.ScreenUpdating = False
    End If
Sheets("Title").Activate
End Sub
 
Upvote 0
Alternate version using If ...Then in leiu of Select Case

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim LR As Long, i As Long
    Dim wss()               'Array of worksheets
    Dim ws As Worksheet
 
    If Target.Address(False, False) = "E15" Then
    Application.ScreenUpdating = False
 
        For Each Worksheet In Worksheets
            Worksheet.Activate
            With Worksheet
                .Columns("E:P").EntireColumn.Hidden = False
            End With
        Next
 
        wss = Array("1 P&L Entity", "2 Budget BS", "3 BS Assets")
        Right4 = Right(Target.Value, 4)
        For ShtIdx = LBound(wss) To UBound(wss)
            Set ws = Sheets(wss(ShtIdx))
            ws.Activate
            ws.Columns("E:P").EntireColumn.Hidden = False
            If Right4 = "CYF1" Then ws.Columns("E:F").Hidden = True
            If Right4 = "CYF2" Then ws.Columns("E:I").Hidden = True
            If Right4 = "CYF3" Then ws.Columns("E:L").Hidden = True
            If Right4 = "CYF4" Then ws.Columns("E:L").Hidden = True
'            If Right4 = "_BUD" Then ws.Columns("E:P").EntireColumn.Hidden = False
'            If Right4 = "_NYF" Then ws.Columns("E:P").EntireColumn.Hidden = False
            If Right4 = "_LPR" Then ws.Columns("E:O").Hidden = True
        Next ShtIdx
        Application.ScreenUpdating = False
    End If
Sheets("Title").Activate
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Combining VBA Codes
Replies
7
Views
456
Joe4
Joe4
A
  • Solved
I need the same code to run on sheets 5 - 100 of my workbook
2
Replies
16
Views
674
FormR
FormR
A
  • Question Question
After User Enters A Value (Validation List), Not Automatically Advancing To Next Cell As Per "Select"
Replies
1
Views
401
Ark68
A
J
  • Question Question
VBA Macros running slow out of nowhere
Replies
4
Views
3K
Tanquen
T
R
  • Question Question
How to combine the functions of having the ability to select multiple items from a drop down list and hide columns based on values of said list?
Replies
0
Views
396
rgree05
R

Forum statistics

Threads
1,224,521
Messages
6,179,291
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top