VBA will hide columns, but not unhide them

**JontyRhodes** · 01-15-2015, 07:44 PM

G'day,

I'm very new to VBA and am struggling with some (probably straight forward) code.

Cell A1 contains a user input number between 100 and 410 (Term 1 week 0 through Term 4 week 10 for attendance purposes)

I don't want the whole year's attendance sheet to show up; only the current week. I have entered the code below to test the first few weeks. It is successful in hiding the columns, but when I change the input number it won't unhide them. I'd greatly appreciate help in identifying what I'm doing wrong.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("ZZ1").Value = 100 Then
        Columns("CO:OF").EntireColumn.Hidden = True
    ElseIf Range("ZZ1").Value = 101 Then
        Columns("O:OF").EntireColumn.Hidden = True
    ElseIf Range("ZZ1").Value = 102 Then
        Range("K:N,Y:OF").EntireColumn.Hidden = True
    Else: Columns("K:OF").EntireColumn.Hidden = False
    End If
End Sub

Thanks in advance!

Marty

**kelleytr** · 01-15-2015, 08:17 PM

It seems like you should have your if conditions always doing two actions:

1. unhide all the columns that you want to be shown
2. hide the columns you do not want to be shown

Just a simple glance indicates that in order for you to hide columns you have to purposefully not enter 100, 101 or 102

T

**JontyRhodes** · 01-15-2015, 08:22 PM

Thanks T,

I did try something similar with a Hidden = False component to each If statement, but I'll give it another go...

**alansidman** · 01-15-2015, 08:26 PM

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html

(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

**JontyRhodes** · 01-15-2015, 08:49 PM

Thanks Alan,

I've read the rules now. Thanks for going easy on me...

Marty

**kelleytr** · 01-15-2015, 08:27 PM

Sure, if you want send me a workbook with what you have and I can code it for you. Then I can explain what I did. Hard to understand fully what you're trying to do from the initial post.

T

**JontyRhodes** · 01-15-2015, 08:43 PM

That would be amazing if you could get me started, T.
Can I just upload the file here, or will I get into trouble for that? (I'm about to read all of the rules...)

Also, I said A1 had the input, but it's actually ZZ1 in the file - calculated via a helper cell on the second worksheet. (I needed to standardise string length - Term 1 week 1 would be shorter than term 2 week 10 otherwise)

**kelleytr** · 01-15-2015, 08:57 PM

Yep, you can post workbooks in the forum. Click the Advanced Reply option.

In there you will see a section on managing attachments.

Make sure to remove any sensitive data - just in case.

Thanks,

T

**JontyRhodes** · 01-15-2015, 09:02 PM

Attendance.xlsm

Let's see if that worked... There's no sensitive information in here yet. Thanks again.

Marty

**mikerickson** · 01-15-2015, 09:08 PM

Perhaps

Private Sub Worksheet_Change(ByVal Target As Range)
    
        Columns("CO:OF").EntireColumn.Hidden = (Range("ZZ1").Value = 100)
    
        Columns("O:OF").EntireColumn.Hidden = (Range("ZZ1").Value = 101)
    
        Range("K:N,Y:OF").EntireColumn.Hidden = (Range("ZZ1").Value = 102)
    
End Sub

**JontyRhodes** · 01-15-2015, 09:19 PM

Thanks Mike,

Getting closer... That works for the complex one (102), but it doesn't seem to do anything for 100 and, strangely, it seems to hide O:X for 101. I cannot fathom why...

**kelleytr** · 01-15-2015, 09:45 PM

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    With Worksheets("Attendance & Organisation")
    
      'Unhides all columns
      .Range("AA:ZZ").EntireColumn.Hidden = False
    
      'Hides specified columns as provided by ZZ
      If .Range("ZZ1").Value = 100 Then
          .Range("CO:OF").EntireColumn.Hidden = True
      ElseIf .Range("ZZ1").Value = 101 Then
          .Range("O:OF").EntireColumn.Hidden = True
      ElseIf .Range("ZZ1").Value = 102 Then
          .Range("K:N,Y:OF").EntireColumn.Hidden = True
      End If
    
    End With
    
End Sub

**JontyRhodes** · 01-15-2015, 10:18 PM

Thanks. It still doesn't seem to want to unhide previously hidden columns though... I'll keep playing around with it. Thanks for your help.

Marty

**kelleytr** · 01-16-2015, 12:54 PM

Sorry - I made a typo...

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    With Worksheets("Attendance & Organisation")
    
      'Unhides all columns
      .Range("A:ZZ").EntireColumn.Hidden = False
    
      'Hides specified columns as provided by ZZ
      If .Range("ZZ1").Value = 100 Then
          .Range("CO:OF").EntireColumn.Hidden = True
      ElseIf .Range("ZZ1").Value = 101 Then
          .Range("O:OF").EntireColumn.Hidden = True
      ElseIf .Range("ZZ1").Value = 102 Then
          .Range("K:N,Y:OF").EntireColumn.Hidden = True
      End If
    
    End With
    
End Sub

VBA will hide columns, but not unhide them

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA will hide columns, but not unhide them

Re: VBA will hide columns, but not unhide them

Re: VBA will hide columns, but not unhide them

Re: VBA will hide columns, but not unhide them

Re: VBA will hide columns, but not unhide them

Re: VBA will hide columns, but not unhide them

Re: VBA will hide columns, but not unhide them

Re: VBA will hide columns, but not unhide them

Re: VBA will hide columns, but not unhide them

Re: VBA will hide columns, but not unhide them

Re: VBA will hide columns, but not unhide them

Re: VBA will hide columns, but not unhide them

Re: VBA will hide columns, but not unhide them

Re: VBA will hide columns, but not unhide them

Thread Information

Users Browsing this Thread

Similar Threads

Hide and Unhide Columns

Hide/Unhide columns

Hide and unhide columns

Hide/Unhide Columns

hide / unhide columns

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions