Excel/Visual Basic - Buttons - HotUKDeals
We use cookie files to improve site functionality and personalisation. By continuing to use HotUKDeals, you accept our cookie and privacy policy.
Get the HotUKDeals app free at Google Play

Search Error

An error occurred when searching, please try again!

Login / Sign UpSubmit

Excel/Visual Basic - Buttons

£0.00 @
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 h… Read More
boydent999 Avatar
9y, 7m agoPosted 9 years, 7 months ago
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:
Tags:
boydent999 Avatar
9y, 7m agoPosted 9 years, 7 months ago
Options

All Comments

(4) Jump to unreadPost a comment
Comments/page:
banned 1 Like #1
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
#2
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
banned#3
Just change the first line to

Private Sub Workbook_Open()

make sure the macro is stored in the 'ThisWorkbook' macro sheet
#4
Genius :)
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

You don't need an account to leave a comment. Just enter your email address. We'll keep it private.

...OR log in with your social account

...OR comment using your social account

Thanks for your comment! Keep it up!
We just need to have a quick look and it will be live soon.
The community is happy to hear your opinion! Keep contributing!