Hiding columns in different sheets dependent on the input in one cell on one sheet.

**acj06** · 09-23-2011, 10:41 AM

Hey All,

I have the following code which does what I want.: Hide columns dependent on the input in a cell. I want this to work in all the worksheets. I have only just started playing around with VBA and from reading a few posts this could be done with a loop function but I'm not sure my code is in the correct form...

  
Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Cells.Count = 1 And Target.Address = "$G$26" Then
  
      If LCase(Target.Value) = "5" Then
         Columns("O:AH").EntireColumn.Hidden = True
      ElseIf LCase(Target.Value) = "10" Then
        Columns("O:AH").EntireColumn.Hidden = False
        Columns("T:AH").EntireColumn.Hidden = True
      ElseIf LCase(Target.Value) = "15" Then
        Columns("T:AH").EntireColumn.Hidden = False
         Columns("Y:AH").EntireColumn.Hidden = True
      ElseIf LCase(Target.Value) = "20" Then
        Columns("Z:AG").EntireColumn.Hidden = False
        Columns("AD:AH").EntireColumn.Hidden = True
      ElseIf LCase(Target.Value) = "25" Then
     Columns("AD:AH").EntireColumn.Hidden = False
      
    
      Else
       Columns("B:M").EntireColumn.Hidden = False
        
       End If
      
   End If
End Sub

Any advice on how to get this to work across all the sheets would be greatly appreciated

**jaslake** · 09-23-2011, 10:54 AM

Hi acj06

Place the code in ThisWorkbook module as a Workbook_SheetChange event.

**Mordred** · 09-23-2011, 10:55 AM

Hi acj06 and welcome to the forum. If you want that particular procedure to work on other sheets, simply copy it and paste it into other sheet modules.

**acj06** · 09-23-2011, 11:01 AM

Thanks for the advice, but I want it to run it off the one cell in one sheet, so i enter a multiple of 5 in $g$26 in worksheet("Inputs") and the columns dissapear dependent on that input...Any advice?

**jaslake** · 09-23-2011, 12:24 PM

Hi acj06

Probably something like this

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    If Target.Cells.Count = 1 And Target.Address = "$G$26" Then
        If LCase(Target.Value) = "5" Then
            For Each ws In ActiveWorkbook.Worksheets
                ws.Columns("O:AH").EntireColumn.Hidden = True
            Next ws
        ElseIf LCase(Target.Value) = "10" Then
            For Each ws In ActiveWorkbook.Worksheets
                ws.Columns("O:AH").EntireColumn.Hidden = False
                ws.Columns("T:AH").EntireColumn.Hidden = True
            Next ws
        ElseIf LCase(Target.Value) = "15" Then
            For Each ws In ActiveWorkbook.Worksheets
                ws.Columns("T:AH").EntireColumn.Hidden = False
                ws.Columns("Y:AH").EntireColumn.Hidden = True
            Next ws
        ElseIf LCase(Target.Value) = "20" Then
            For Each ws In ActiveWorkbook.Worksheets
                ws.Columns("Z:AG").EntireColumn.Hidden = False
                ws.Columns("AD:AH").EntireColumn.Hidden = True
            Next ws
        ElseIf LCase(Target.Value) = "25" Then
            For Each ws In ActiveWorkbook.Worksheets
                ws.Columns("AD:AH").EntireColumn.Hidden = False
            Next ws
        Else
            For Each ws In ActiveWorkbook.Worksheets
                ws.Columns("B:M").EntireColumn.Hidden = False
            Next ws
        End If
    End If
End Sub

**acj06** · 09-26-2011, 05:12 AM

John,

The code works excellently! Thank you. I was wondering if i wanted to specify which worksheets to hide columns in, how would I tell excel which sheets to hide the columns in?

Thanks Again

Alex

**jaslake** · 09-26-2011, 09:03 AM

Hi acj06

Replace these lines of code

For Each ws In ActiveWorkbook.Worksheets

with this

For Each ws In Array("Sheet1", "Sheet3", "Sheet5", "Sheet7")

**acj06** · 09-26-2011, 10:46 AM

John,

Everything was working a treat, I continued building my model...I went to change the &G&26 input and now i get the following error:

Run-time error '1004':

**acj06** · 09-26-2011, 10:50 AM

John,

Thanks for your help it is invaluable. The code was working perfectly, so i continued working in excel. Only now to find when I try and change the value in cell &G&26, i ge the following runtime error:

Run time error '1004'
unable to set the Hidden property of the range class

Do you have any suggestions on how to get around this problem. Thanks for the advice!

Alex

**jaslake** · 09-26-2011, 11:03 AM

Hi Alex

You probably should have gotten "Object Required". Try this revised code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Variant
    If Target.Cells.Count = 1 And Target.Address = "$G$26" Then
        If LCase(Target.Value) = "5" Then
            For Each ws In Array("Sheet2", "Sheet3", "Sheet4", "Sheet5")
                Sheets(ws).Columns("O:AH").EntireColumn.Hidden = True
            Next ws
        ElseIf LCase(Target.Value) = "10" Then
            For Each ws In Array("Sheet2", "Sheet3", "Sheet4", "Sheet5")
                Sheets(ws).Columns("O:AH").EntireColumn.Hidden = False
                Sheets(ws).Columns("T:AH").EntireColumn.Hidden = True
            Next ws
        ElseIf LCase(Target.Value) = "15" Then
            For Each ws In Array("Sheet2", "Sheet3", "Sheet4", "Sheet5")
                Sheets(ws).Columns("T:AH").EntireColumn.Hidden = False
                Sheets(ws).Columns("Y:AH").EntireColumn.Hidden = True
            Next ws
        ElseIf LCase(Target.Value) = "20" Then
            For Each ws In Array("Sheet2", "Sheet3", "Sheet4", "Sheet5")
                Sheets(ws).Columns("Z:AG").EntireColumn.Hidden = False
                Sheets(ws).Columns("AD:AH").EntireColumn.Hidden = True
            Next ws
        ElseIf LCase(Target.Value) = "25" Then
            For Each ws In Array("Sheet2", "Sheet3", "Sheet4", "Sheet5")
                Sheets(ws).Columns("AD:AH").EntireColumn.Hidden = False
            Next ws
        Else
            For Each ws In Array("Sheet2", "Sheet3", "Sheet4", "Sheet5")
                Sheets(ws).Columns("B:M").EntireColumn.Hidden = False
            Next ws
        End If
    End If
End Sub

**acj06** · 09-26-2011, 11:20 AM

John,

Thanks that works brilliantly!

Alex

**jaslake** · 09-26-2011, 11:29 AM

Hi Alex

You're welcome...glad I could be of help. If that satisfies your need, I'd appreciate it if you'll please mark your thread as "Solved".
To mark your thread solved do the following:
- Go to your first post on the thread
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solved
- Click Save

Thread: Hiding columns in different sheets dependent on the input in one cell on one sheet.

Thread Tools

Display

Hiding columns in different sheets dependent on the input in one cell on one sheet.

Re: Hiding columns in different sheets dependent on the input in one cell on one shee

Re: Hiding columns in different sheets dependent on the input in one cell on one shee

Re: Hiding columns in different sheets dependent on the input in one cell on one shee

Re: Hiding columns in different sheets dependent on the input in one cell on one shee

Re: Hiding columns in different sheets dependent on the input in one cell on one shee

Re: Hiding columns in different sheets dependent on the input in one cell on one shee

Re: Hiding columns in different sheets dependent on the input in one cell on one shee

Re: Hiding columns in different sheets dependent on the input in one cell on one shee

Re: Hiding columns in different sheets dependent on the input in one cell on one shee

Re: Hiding columns in different sheets dependent on the input in one cell on one shee

Re: Hiding columns in different sheets dependent on the input in one cell on one shee

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions