Excel/Visual Basic - Buttons

    I have some data in an excel workbook.
    As opposed to having it in three separate sheets, what would be neat if i could press a button, one of three, and depending on which button was pressed, would hide/show data

    For example:

    I have row A as a title and rows B, C and D with associated Data
    Row E as a title and rows, F and G with associated Data.
    Row H as a title and rows, I and J with associated Data.

    I'd like a visual basic script whereby when i pressed a button, only the Row A and it's associated data was shown, and if i pressed a separate button, only B and it's associated data was shown, and so on for C

    This is the equivalent of "hide" and "unhiding" data but by using buttons....anyone lol, even have a clue what i am asking? :oops:



    This will open an inputbox to input option 1, 2 or 3 for your 3 selections. You can change the text to what you want. This is easier than assigning macros to buttons, as I tend to prefer userforms to assign buttons to.

    Sub Macro1()
    a = InputBox("Enter required destination", "Select")
    If a = "1" Then 'Show columns A-D
    Columns("A:D").EntireColumn.Hidden = False
    Columns("E:J").EntireColumn.Hidden = True
    End If
    If a = "2" Then 'Show columns E-G
    Columns("E:G").EntireColumn.Hidden = False
    Columns("A:D").EntireColumn.Hidden = True
    Columns("H:J").EntireColumn.Hidden = True
    End If
    If a = "3" Then 'Show columns H-J
    Columns("H:J").EntireColumn.Hidden = False
    Columns("A:G").EntireColumn.Hidden = True
    End If
    End Sub

    Original Poster

    Thanks for that

    I've edited it, for rows as opposed to columns and it works fine now
    What would be nice, if i could have this as the main, well ONLY option when the spreadsheet is opened......

    Sub Macro1()
    a = InputBox("Enter required destination", "Select")
    If a = "1" Then 'Show Rows 1-2
    Rows("1:2").EntireRow.Hidden = False
    Rows("3:6").EntireRow.Hidden = True
    End If
    If a = "2" Then 'Show Rows 3-4
    Rows("1:2").EntireRow.Hidden = True
    Rows("3:4").EntireRow.Hidden = False
    Rows("5:6").EntireRow.Hidden = True
    End If
    If a = "3" Then 'Show Rows 5-6
    Rows("1:4").EntireRow.Hidden = True
    Rows("5:6").EntireRow.Hidden = False
    End If
    End Sub


    Just change the first line to

    Private Sub Workbook_Open()

    make sure the macro is stored in the 'ThisWorkbook' macro sheet

    Original Poster

    Thank you very much
    Have the elementary version working now, will add the bulk data tomorrow at work
    Thanks again, very much appreciated.
    Post a comment
      Top Discussions
      1. Amazon fake review detector.11
      2. miikeyblue and shabbird's (but mostly shabbird's) Tuesday night pub quiz!563833
      3. HUKD Nintendo Switch owners thread941356
      4. SNES Classic launches 29th September 2017 - Check for stock and pre-order i…4691433

      See more discussions