Results 1 to 4 of 4
  1. #1
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hello all,

    I want to hide or unhide some columns on a totalsheet depending on a value in a cell on that sheet.
    This cell is the 'result' of cells from other sheets. So I was thinking; if the user returns to the totalsheet a procedure is started that wil check the value of the cell and it will hide or unhide the columns.

    Is this prossible, or has someone an beter sollution?


    Greetings,

    Patrick Schcouten
    The Netherlands
    Greetings,

    Patrick Schouten
    (The Netherlands)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can use the Worksheet_Activate event of the sheet where you want to hide columns.

    Right-click the sheet tab and select View Code from the popup menu.
    Copy the following code into the module that appears, and modify it as needed.

    Code:
    Private Sub Worksheet_Activate()
      Dim f As Boolean
      ' Temporarily suspend event handling
      Application.EnableEvents = False
      ' Modify the condition as needed
      f = Range("A1") > 5
      ' And modify the range as needed
      Range("B1,D1,G1,L1").EntireColumn.Hidden = f
      ' Resume event handling
      Application.EnableEvents = True
    End Sub
    Warning: running VBA code disables Undo.

  3. #3
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='siflaar' post='771920' date='23-Apr-2009 09:40']Hello all,

    I want to hide or unhide some columns on a totalsheet depending on a value in a cell on that sheet.
    This cell is the 'result' of cells from other sheets. So I was thinking; if the user returns to the totalsheet a procedure is started that wil check the value of the cell and it will hide or unhide the columns.

    Is this prossible, or has someone an beter sollution?


    Greetings,

    Patrick Schcouten
    The Netherlands[/quote]

    This should probably be in the Excel forum.

    It's certainly possible - the event procedure you're after is Worksheet_Activate().
    Something like this should work:
    [codebox]Private Sub Worksheet_Activate()
    Range("B:E").EntireColumn.Hidden=False
    Select Case Range("A1").value
    Case <100:
    Range("B:B").EntireColumn.Hidden=True
    Range("D").EntireColumn.Hidden=True
    Case <50:
    Range("C:C").EntireColumn.Hidden=True
    Case Else:
    Range("E:E").EntireColumn.Hidden=True
    End Select
    End Sub[/codebox]
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  4. #4
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hallo Hans,

    Thanks for the quick and correct response.
    It is working great.

    Greetings.
    Patrick Schouten
    The Netherlands
    Greetings,

    Patrick Schouten
    (The Netherlands)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •