Macro for Form Controls

[jade]

Hello everyone,


I am given the task to create a dynamic dashboard within a few weeks and I want to give the user the capability to have some sort of form control to choose which groups of users to compare.


I was able to do the following Excel - but it was only with three checkbox form controls. There will definitely be more than 3 users to compare.


(Sorry I am very inexperienced with Macros and VBA) But is there a macro I could perform to create form controls for the rest of the users?


How might I go about creating a form control for the different types of users? (Users in different groups)


http://cid-9a617fc298848959.office.live.com/view.aspx/.Public/23200Success.xlsx


Thanks!!


Jade

 

[Clarity]

Hi,


I think the data validation function could do what you want.


Data Validation will allow you to let the user select a value from a list (that you specify). The selection that they then make from the list can be used to drive the data used for your chart etc.


Happy to expand if needed.

 

[Hui]

Jade

Also have a look at using Tables for your data

http://chandoo.org/wp/2009/09/10/data-tables/

Tables have very good sorting and data retrieval properties which may help you without VBA

 

[jade]

Thanks Clarity and Hui for the insight!


I found so much on pivot tables, that I completely dismissed using regular tables.

I used the "Totals Row" function, but for some reason, I can't seem to chart the data in that row. Is there something additional I must do to chart the data in the Totals Row?


About Data Validation, I found this: http://www.contextures.com/xldataval02.html


I sort of understand how I can create drop downs for different categories.

Do you have an example of how to format the data points under each category? (i.e. When selecting a specific category through the drop down, having the datasets appear. (Somewhat like a show/hide function)


Thanks again for all your help!


Jade

 

[Hui]

Can you post a sample of your data somewhere with an explanation of what your trying to achieve

 

[jade]

Hi Hui,


I uploaded two files here:


http://cid-9a617fc298848959.office.live.com/browse.aspx/.Public?uc=2&lc=1033


One is a working form control with radar chart. One is a table as you suggested.


Thanks for your help!


Jade

 

[Hui]

Jade

2 Questions

Table.xls

When you say a chart based on the Totals Row do you mean Row 51 vs Row 2

Did you want an option to select Products in A1?

CheckBoxFormControl.xls

When you say you want to compare Group A x C, A x D OR Group B x C etc

Does A x C mean vs or Multiply

Are you set on Spider Charts or will Line/Column Charts work

Are you interested in ratio's of A/C etc

 

[jade]

Hi Hui,


Yes, I would like a chart based on the values in Row 51 (titles from Row 2 would be nice).

Only in columns A to F.


That would be superb if there was an option to select between the 6 products in A1 and have the data change accordingly. Maybe Data Validation comes into play here..?


When I said comparing Group A x C, I meant A vs. C not A multiplied by C. (Sorry about that)


And I am pretty set on using Radar Charts.


I thought about using line column charts to compare between different user types. Having a side by side comparison. (Something like http://chandoo.org/wp/wp-content/uploads/2009/12/target-vs-actual-charts-excel-1.png ) If that's possible, that would be 'excel'lent.


Thanks!

Jade

 

[Hui]

Jade

Your idea is close, but you have locked yourself in by hard coding your buttons

The general approach to dashboards is

1. Collect data in a data table

2. Have tools to allow users to extract

3. Extract, Summarise and maybe sort

4. Display


In your case you have made it restrictive by using Check boxes

You would be better to give users 2 drop downs where they can select say Group A in one and Group B etc in the other


Then you will use those values to lookup and summarise the data and then chart


I would use Column Charts in preference to Radar charts, but that's your choice

Definitely have a read through http://chandoo.org/wp/excel-dashboards/

 

[jade]

Is there a tutorial on how to create a drop down for different groups of data?


That would definitely beat hard coding buttons, I agree.


Thanks again!


Jade

 

[jade]

I was looking at some examples from http://chandoo.org/wp/2010/01/04/sales-dashboards/


and specifically, the "Excel based Sales Dashboard by Aires (Option 02)" has a dropdown option for different sets of data. but still, i'm not so clear on how Aires was able to create it without hard coding the formulas..

 

[Hui]

Aires is using Data Validation, with hidden rows of data below Row 27

To see what he has done, Unprotect the sheet on the Review Tab

Click on a Drop Down and then select Data Validation Data Validation from the data Tab

To unhide the rows below 27 use the following macro

[pre]

Sub UHide()
Cells.Select
Selection.EntireRow.Hidden = False
End Sub

[/pre]

 

[jade]

Looks like there's a lot of hardcoding here too.

But it's an option. Thanks!