0

I'm new to VBA in excel, and I'm trying to put together a dashboard for my leadership to view...most of my background is in database query languages and not programming in this sense. I'm trying to build three buttons in excel: Overview, Direct, Indirect are the names to be exact. When I click Overview, I want my "Dashboard" to show all of the metrics I used in my overview calculations (charts, numbers, etc). If I click it again, I want it to hide them. The same with the other two buttons, If I click "overview" and my dashboard view is all of my overview metrics and graphs, and I click "Direct" I want all of my metrics, graphs to show my "Direct" metrics. The same goes for Indirect. So far I have one macro, it hides all objects (shapes, charts) in my active worksheet, but not the metrics (numbers) in the cells. It also hides the button that I assigned the macro too, so I can't unhide everything unless I open the VBA editor! I want each of my 3 "main" buttons to not hide when I click any of them, just my charts and metrics, and upon a second click of the button unhide.

Here's my sample code so far:

    Sub HideEachShape()
Dim sObject As Shape
For Each sObject In ActiveSheet.Shapes
sObject.Visible = False
Next
End Sub

Pretty basic I know, but I'm new to this.

DukeLuke
  • 315
  • 6
  • 26
  • You can use the application.caller and have something to check - give it a try. – Raystafarian Sep 19 '16 at 19:10
  • Are you using ActiveX controls or form controls? http://stackoverflow.com/questions/15455179/what-is-the-difference-between-form-controls-and-activex-control-in-excel-20 – Ralph Sep 19 '16 at 19:44
  • @Ralph Honestly, I have no idea, How can I check? I want to create a decent amount of these buttons for different loan types within each category i.e. under overview I would have mortgage, vehicle, etc. – DukeLuke Sep 19 '16 at 19:48
  • If you right-click such a button if it is a forms button it'll have an option to assign a macro. If it does not have that option in the right-click menu it is an activeX button. Forms buttons have my preference as they are more reliable. They are part of the Buttons collection of the worksheet they are on and can be accessed by their name. This changes the word Hide in the caption from Hide to Show: ActiveSheet.Buttons("Button1").Caption = Replace(ActiveSheet.Buttons("Button1").Caption, "Hide", "Show"). – jkpieterse Sep 20 '16 at 04:56
  • So if you've used the forms button, in the macro you assigned to it you can: Dim oCmd as Button Set oCmd = ActiveSheet.Buttons(Application.Caller) 'Now do stuff with the button – jkpieterse Sep 20 '16 at 04:57
  • i have the option to assign a macro, so it must be forms button – DukeLuke Sep 20 '16 at 13:05

0 Answers0