Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Hide/Unhide Columns Based On Cell Value

  1. #1
    Join Date
    3rd September 2007
    Posts
    19

    Hide/Unhide Columns Based On Cell Value

    Can anyone help me.
    I've attached one speadsheet :- "VBA.xls". The following conditions are to be done:-
    1. When D2=1, Column "F","G" are visible & Column "I","J","L","M" are hidden.
    2. When D2=2, Column "I","J" are visible & Column "F","G","L","M" are hidden.
    3. When D2=3, Column "L","M" are visible & column "F","G","I","J" are hidden.

    Thanks
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,713

    Re: Hide/Unhide Columns Based On Cell Value

    Follow this pattern;
    VB:
    Sub TryMe2() 
        Dim lVal As Long, bHide As Boolean 
         
        lVal = Range("D2") 
        bHide = (lVal = 1) 
        Columns("F:G").Hidden = bHide 
        Columns("I:J").Hidden = Not bHide 
        Columns("L:M").Hidden = Not bHide 
    End Sub 
    
    
    Last edited by Dave Hawley; September 4th, 2007 at 12:52.

  3. #3
    Join Date
    3rd September 2007
    Posts
    19

    Re: Hide/Unhide Columns Based On Cell Value

    hi

    Thanks Dave but its not working.I am affraid whether I explained properly or not? I am poor in VB.
    Auto Merged Post;

    Can anybody help me????
    Last edited by ss_bb_24; September 4th, 2007 at 13:50. Reason: Auto Merged Doublepost

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    28th January 2005
    Posts
    221

    Re: Hide/Unhide Columns Based On Cell Value

    Try this adaptation of Dave's code in the worksheet code module:
    VB:
    Private Sub Worksheet_Change(ByVal Target As Range) 
        If Not (Intersect(Range("D2"), Target) Is Nothing) Then 
            Dim lVal As Long 
            lVal = Range("D2") 
            Columns("F:G").Hidden = Not (lVal = 1) 
            Columns("I:J").Hidden = Not (lVal = 2) 
            Columns("L:M").Hidden = Not (lVal = 3) 
        End If 
    End Sub 
    
    
    p45cal

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    3rd September 2007
    Posts
    19

    Re: Hide/Unhide Columns Based On Cell Value

    thanks a lot. But I have used the below code & it worked:-
    VB:
    Private Sub Worksheet_Change(ByVal Target As Range) 
        If Target = Range("D2") Then 
            Select Case UCase(Target.Value) 
            Case "1" 
                Range("F:G").EntireColumn.Hidden = False 
                Range("I:J").EntireColumn.Hidden = True 
                Range("L:M").EntireColumn.Hidden = True 
            Case "2" 
                Range("F:G").EntireColumn.Hidden = True 
                Range("I:J").EntireColumn.Hidden = False 
                Range("L:M").EntireColumn.Hidden = True 
            Case "3" 
                Range("F:G").EntireColumn.Hidden = True 
                Range("I:J").EntireColumn.Hidden = True 
                Range("L:M").EntireColumn.Hidden = False 
            End Select 
        End If 
    End Sub 
    
    
    BUT I HAVE ONE MORE DOUBT ON THIS. IF THE TERGET CELL (AS HERE D2) IS NOT AT THE SAME SHEET THEN HOW CAN WE DO THIS SAME OUTPUT. ASSUME THE TERGET CELL (LIKE HERE D2) IS A2 IN SHEET 2. HOPE I EXPLANIED ALL OF YOU PROPERLY.

    PLEASE SUGGEST???
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    28th January 2005
    Posts
    221

    Re: Hide/Unhide Columns Based On Cell Value

    The code is in a worksheet module so I doubt it'll get called if it's on another sheet.
    p45cal

    ps. your statement
    VB:
    If Target = Range("D2") Then 
    
    
    will allow the code to work if the value of any cell you change on the sheet is equal to the value of what's already in cell D2.
    You could change it to
    VB:
    If Target.address = Range("D2").address Then 
    
    
    which, in this case, is easier than the Intersect route I took.
    Last edited by p45cal; September 4th, 2007 at 20:24.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    3rd September 2007
    Posts
    19

    Re: Hide/Unhide Columns Based On Cell Value

    I am a lerner of VBA so I cant understand & I applied the same but It did not work out.

    Could u please write the total code ?

    Hope I explained you the exact requirement. I have attached the excel sheet, please have a look.

    Thanks.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    28th January 2005
    Posts
    221

    Re: Hide/Unhide Columns Based On Cell Value

    See attached
    p45cal
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    3rd September 2007
    Posts
    19

    Re: Hide/Unhide Columns Based On Cell Value

    thanks a lot. Just a small clarification is needed. When we give value in the E5 cell in sheet 1 it hide the nesseccary column & opens the help sheet automatically. You can also check it. Is it possible to stop this automatically open of help.sheet?

    What do u say?

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,921
    Welcome to the forum,

    This is a Cross Post

    Please read the rules you agreed to. Rule 16 clearly spells out what you agreed to in terms of Cross Posting. After you have done so please ClickHere and Private Message me or one of the other Moderators: Richie(UK);Dave Hawley with your acceptance, or non-acceptance of the rules. Please also include a link to this Thread. You can copy this directly from the Address bar of your Browser. When this is done your thread will be re-opened.
    Auto Merged Post;

    PM Received.

    OP agrees to the rules. All ok.
    Last edited by Reafidy; September 5th, 2007 at 13:53. Reason: Auto Merged Doublepost

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Hide/Unhide Sheets Based On Cell Value
    By gmccreedy in forum EXCEL HELP
    Replies: 6
    Last Post: August 7th, 2014, 06:39
  2. Replies: 8
    Last Post: January 13th, 2009, 03:33
  3. Replies: 7
    Last Post: January 11th, 2009, 10:33
  4. Unhide/Hide Sheet Based On Value In Cell
    By caliskier in forum EXCEL HELP
    Replies: 6
    Last Post: April 3rd, 2008, 07:36
  5. Unhide/hide Multiple Columns Based On Date
    By miko68 in forum EXCEL HELP
    Replies: 8
    Last Post: July 31st, 2007, 18:15

Bookmarks

Posting Permissions

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