none
Usea a Command Button on Sheet 1 to Open Sheet 2 and hide some columns and sort RRS feed

  • Question

  • Hi All,

    I have created a master sheet that holds all of my data (Detail Sheet).  List Sheet contains no data.  I have created a macro that will present a status view on the Detail Sheet and assigned a command button to it.  So that when the user goes to the Detail Sheet and clicks on the Status button the sheet hides columns and filters the data.  This works fine within Detail Sheet.  I would like to move this button to the List Sheet so that the user can activate the appropriate "View" for the status on Detail Sheet from List Sheet.  I would like to do this as I need to create several "status views" for different groups.  The Detail Sheet contains a massive amount of data.  I need probably 10 or more "views" to allow all the groups their own status view.  I would like to consolidate these on the List Sheet so that I don't confuse the user and take up room on the Detail Sheet. I have created the following code and assigned it to a command button on the List.  However it will not work from  the list sheet. 

    Private Sub CommandButton6_Click()

    ' Display view for weekly meeting and status

        Sheets("List Sheet").Select

        Sheets("Detail Sheet").Visible = True

        Sheets("Detail Sheet").Activate

        Cells.Select

            Columns("B:F").Select

            Selection.EntireColumn.Hidden = True

            Columns("J:K").Select

            Selection.EntireColumn.Hidden = True

            Columns("M:Q").Select

            Selection.EntireColumn.Hidden = True

            Columns("S:Y").Select

            Selection.EntireColumn.Hidden = True

            Columns("AA:AY").Select

            Selection.EntireColumn.Hidden = True

            Columns("BA:BA").Select

            Selection.EntireColumn.Hidden = True

            Columns("BH:AAA").Select

            Selection.EntireColumn.Hidden = True

        ActiveSheet.Range("$Z3").AutoFilter Field:=26, Criteria1:="Make"

        ActiveSheet.Range("$BE:$BE").AutoFilter Field:=57, Criteria1:="=N/A", _

            Operator:=xlOr, Criteria2:="="

    End Sub

    This does work when the command button is clicked on the detail sheet:

        Cells.Select

            Columns("B:F").Select

            Selection.EntireColumn.Hidden = True

            Columns("J:K").Select

            Selection.EntireColumn.Hidden = True

            Columns("M:Q").Select

            Selection.EntireColumn.Hidden = True

            Columns("S:Y").Select

            Selection.EntireColumn.Hidden = True

            Columns("AA:AY").Select

            Selection.EntireColumn.Hidden = True

            Columns("BA:BA").Select

            Selection.EntireColumn.Hidden = True

            Columns("BH:AAA").Select

            Selection.EntireColumn.Hidden = True

        ActiveSheet.Range("$Z3").AutoFilter Field:=26, Criteria1:="Make"

        ActiveSheet.Range("$BE:$BE").AutoFilter Field:=57, Criteria1:="=N/A", _

            Operator:=xlOr, Criteria2:="="

    I don't have a lot of experience with command buttons and would appreciate any and all help. 

    Thanks in Advance for the help and suggestion on what I have done wrong.

    Leah

    Wednesday, January 10, 2018 6:43 PM

Answers

  • In general, unqualified ranges  refer to the active sheet of the active workbook, unless your code resides in the worksheet's codemodule, in which case they refer to that sheet only and not the activesheet.  So, when you write your code, you should qualify the range parent fully.

    Private Sub CommandButton6_Click()
        'Unhide columns first
        Sheets("Detail Sheet").UsedRange.EntireColumn.Hidden = False
        'Hide specific columns
        Sheets("Detail Sheet").Range("B:F,J:K,M:Q,S:Y,AA:AY,BA:BA,BH:AAA").EntireColumn.Hidden = True
    End Sub

    Also, when you are autofiltering, it is better to not depend on Excel selecting the range for you, or assuming the sheet doesn't already have a filter:

        With Sheets("Detail Sheet")
            On Error Resume Next
            .AutoFilterMode = False
            On Error GoTo 0
            'Headers in row 2, data starts in column A, filter on Z
            .Range(.Range("A2"), .Cells(.Rows.Count, "Z").End(xlUp)).AutoFilter Field:=26, Criteria1:="Make"
        End With


    Wednesday, January 10, 2018 7:42 PM

All replies

  • In general, unqualified ranges  refer to the active sheet of the active workbook, unless your code resides in the worksheet's codemodule, in which case they refer to that sheet only and not the activesheet.  So, when you write your code, you should qualify the range parent fully.

    Private Sub CommandButton6_Click()
        'Unhide columns first
        Sheets("Detail Sheet").UsedRange.EntireColumn.Hidden = False
        'Hide specific columns
        Sheets("Detail Sheet").Range("B:F,J:K,M:Q,S:Y,AA:AY,BA:BA,BH:AAA").EntireColumn.Hidden = True
    End Sub

    Also, when you are autofiltering, it is better to not depend on Excel selecting the range for you, or assuming the sheet doesn't already have a filter:

        With Sheets("Detail Sheet")
            On Error Resume Next
            .AutoFilterMode = False
            On Error GoTo 0
            'Headers in row 2, data starts in column A, filter on Z
            .Range(.Range("A2"), .Cells(.Rows.Count, "Z").End(xlUp)).AutoFilter Field:=26, Criteria1:="Make"
        End With


    Wednesday, January 10, 2018 7:42 PM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Thursday, January 11, 2018 7:39 AM
  • Hello Leah12,

    What's current state of the thread? Does Bernie's suggestion work for you? If it has, I would suggest you mark the helpful answer to close the thread. If not, please follow up to let us know your current issue. Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 12, 2018 8:33 AM
  • Thanks for the help.   It is much appreciated.
    Monday, January 15, 2018 6:35 PM