Dynamic Dashboard

[GN0001]

There is a dashboard in Chandoo.org

This is the link

http://chandoo.org/wp/2010/03/16/excel-dashboard-tutorial-1/

A button is inserted into excel spread sheet and called "help". Is this a button (Form control)? When it is click, it is taken to a user form. I can see the code:

Sub HelpForm()

HelpForm.Show

End Sub


How is this button linked to the user form? Do we insert a form in Help Sheet or only with code, we make this form display? If what I describe is not clear, then I need to send you the file itself.


Thank you for your help.

Guity

 

[Luke M]

It looks like the workbook is using Jon Walkenbach's method discussed here


UserForms are not directly connected to a sheet, they are stored in the programming (like the modules). From VBA, you should be able to find the form (you can right click on it to view all the coding).


The button on the spreadsheet could be either a form, or ActiveX control. My guess is the former. It then calls the macro "HelpForm" which shows the UserForm HelpForm.


So, if you want to be able to display the UserForm from somewhere else, you just need some way of running the code line:

HelpForm.Show

 

[GN0001]

Hi Luke,


How do we create user form in Excel? Only with codes? I know the code: HelpForm.show, will display the user form. But how do we create the object user form? I know that we can create a user form with vb in visual basic. In this scenario, do we use code to create this object, then where this code is written? Or do we insert an object "User form"?


Regards,

Guity

 

[Radu]

Guity,


In order to create a user form, you will have to go to VBE, select Insert-User Form. This will create a blank user form. You will then have to insert controls and set control properties etc. You can also write code for each control in the form or for the form itself (double-click each item you want to write code for). All this will be stored the form's module.


Hope it helps,

Radu

 

[GN0001]

Hello Radu,


Thank you for the response. I need to work on that and it takes a while for me to see what is going on. Are you familiar with this dashboard posted in this website?


Regards,

Guity

 

[Radu]

Guity,


I gave it a quick look . What you could do, in terms of the help form:


1. if you're working on the template file: go to the HelpSheet, then replace/add help topics to your liking (hint: if you add another help topic, the form will be automatically updated to include it); the only trick is to respect the layout of the file, e.g. the topics in col A, and the text in col. B;

2. if you have created another file:

- export the form from the template (in VBE, in Project Explorer, right-click on the name of the form, choose Export file, and save it on your computer);

- create a HelpSheet in your file (the name must be exactly the same as the one in the template in order for it to work, otherwise you'll have to replace the references in the code);

- import the form: go to VBE, right click the name of the file (ex.: VBA Project and your file's name), choose Import File and then choose the FormHelp.frm file you have just saved. When you export the form file, the code will be also exported, and, respectively, when you import the .frm file the code will be imported with it;

- follow the steps from the first point.


As for the button (your original question), create the shape, then right-click it and choose "Assign macro". Then, in the Macro name window, choose the ShowForm macro (basically, clicking the form will trigger the macro, which will show the form).


Cheers,

Radu

 

[GN0001]

Thank you Radu, I will try it, but what is important for me at this step is to understand how the data is brought to that table in ch1 sheet. I do understand all the formulas and sumifs. Do you know anything about this dashboard?


Regards,

Guity

 

[Radu]

What do you mean about "knowing anything about this dashboard"? Try and be more specific, because the more specific you are, the faster we can help you.


Cheers,

Radu

 

[GN0001]

I am working on a dashboard posted in chandoo's website. During the process, the question are coming up.Please go this link:


http://chandoo.org/wp/excel-dashboards/

then to here:


http://chandoo.org/wp/2010/03/16/excel-dashboard-tutorial-1/


I want to be able to select data by product group and month and be able to choose the key performance indicator to report.I also want to be able to report on all product groups and for all periods.


I have done the first part but the second part when it is all, give me the problem and sumifs function returns zero. How to fix this problem? Please let me know if this is not clear.


I appreciate your help.


Guity

 

[Radu]

can you post the SUMIFS() formula you have created?


Thanks,

Radu

 

[GN0001]

There you go.

SUMIFS(INDIRECT($D$21),Product,$D$19,Period,$D$20,SalesPersons,$B24)

When I select All products for Cell D19, And/ or All periods for C20, it returns either #REF or #Value for the second case.

If you have an email address, I can send the file to you. That way you can play with drop down list to see how it works.

Regards,

Guity

 

[GN0001]

Radu,

I understood what is going on. When the case is AllProduct or AllPeriod, then it means we don't specify any product or any period, that means all product or all period....

It means criteria for this criteria range is zero, then this range is not considered at all.


Please let me know if this is NOT clear. I need someone to work with me for the rest, if you are interested in.

Regards,

Guity

 

[Radu]

Guity,


Please send me your file at radooku at gmail dot com. You could also share the file through Google Docs. That way, we'll be able to collaborate in real-time.


As for your formula, you're absolutely right. When the case is AllProduct or AllPeriod, you could add a little IF formula, referring to the whole range, e.g., something like:

IF(OR(C20=AllProduct;C21=AllPeriod);[show total];IF(AND(C20=AllProduct;C21<>AllPeriod);[show data for all products, but for the period in C21];IF(AND(C20<>AllProduct;C21=AllPeriod;[show data for the selected product, in all period];"")))