Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Hide/Unhide Columns Based On Cell Value

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    Re: Hide/Unhide Columns Based On Cell Value

    Follow this pattern;
    Code:
    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, 12:52.

    Comment


    • #3
      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, 13:50. Reason: Auto Merged Doublepost

      Comment


      • #4
        Re: Hide/Unhide Columns Based On Cell Value

        Try this adaptation of Dave's code in the worksheet code module:
        Code:
        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

        Comment


        • #5
          Re: Hide/Unhide Columns Based On Cell Value

          thanks a lot. But I have used the below code & it worked:-
          Code:
          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

          Comment


          • #6
            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
            Code:
            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
            Code:
            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, 20:24.

            Comment


            • #7
              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

              Comment


              • #8
                Re: Hide/Unhide Columns Based On Cell Value

                See attached
                p45cal
                Attached Files

                Comment


                • #9
                  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?

                  Comment


                  • #10
                    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, 13:53. Reason: Auto Merged Doublepost
                    Reafidy

                    Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                    Comment


                    • #11
                      Re: Hide/Unhide Columns Based On Cell Value

                      yes, remove
                      Code:
                      .Activate
                      p45cal
                      Last edited by p45cal; September 5th, 2007, 18:08. Reason: Auto Merged Doublepost

                      Comment


                      • #12
                        Re: Hide/Unhide Columns Based On Cell Value

                        hey ...thanks a lot.
                        If I want to unhide F,G,I,J in case of Plant No. 2 and If I want to unhide F,G,I,J,L,M in case of Plant no. 3 then what mordification should be done in the code. As usual, Plant no. 1 will unhide only F,G nothing else. The code is pasted here:-
                        Code:
                        Private Sub Worksheet_Change(ByVal Target As Range)
                            If Not (Intersect(Range("E5"), Target) Is Nothing) Then
                                Dim lVal As Long
                                lVal = Range("E5")
                                With Sheets("help")
                                  .Columns("F:G").Hidden = Not (lVal = 1)
                                  .Columns("I:J").Hidden = Not (lVal = 2)
                                  .Columns("L:M").Hidden = Not (lVal = 3)
                                End With
                            End If
                        End Sub
                        In this connection, could you please tell me how can I copy the value of a cell of a work sheet in other worksheet?

                        Finally thanks a lot to help a novice like me to learn this.

                        Comment


                        • #13
                          Re: Hide/Unhide Columns Based On Cell Value

                          Change the code to:
                          Code:
                          Private Sub Worksheet_Change(ByVal Target As Range)
                              If Not (Intersect(Range("E5"), Target) Is Nothing) Then
                                  Dim lVal As Long
                                  lVal = Range("E5")
                                  With Sheets("help")
                                      .Columns("F:M").Hidden = False
                                    Select Case lVal
                                    Case 1
                                      .Range("I:J,L:M").Columns.Hidden = True
                                    Case 2
                                      .Columns("L:M").Hidden = True
                                    Case 3
                                      'do nothing
                                    Case Else
                                      MsgBox "Out of range, please enter valid number 1,2 or 3"
                                    End Select
                                  End With
                              End If
                          End Sub
                          re copying cell values,
                          Code:
                          Sheets("Sheet1").Range("A2")=Sheets("help").range("F5").value
                          will copy the value of cell F5 on sheet 'help' to cell A2 on sheet 'Sheet1'.
                          p45cal

                          Comment


                          • #14
                            Re: Hide/Unhide Columns Based On Cell Value

                            thanks a lot

                            Comment

                            Trending

                            Collapse

                            There are no results that meet this criteria.

                            Working...
                            X