Adding Dashboard Functionality

[Vikki_61]

HI VBA community
What I have is a interesting task but unfortunity I am well
out of my Depth so if you can help that would be GREAT.
Attached is my excel dashboard report framework. What would be real nice
for this are three things
1. PREVENT USER SCROLLING: Some VBA code for the workbook that for all worksheets stops users from scolling outside of the set print area
once it is set on.
2. AUTO RANKING DISPLAY: For the Ranking display on the Dashboard
Worksheet 1 put the text from
worksheet 2 column B rows 5 to 12 in order of their percentage
in column I of worksheet 2 into Ranking Display on worksheet 1
in column I rows 21,23,25,27,29,31,and 33.(1st to 8th).With
the highest percentage being 1st and the lowest last -and incase
two percentages are the same allow for that (one could just be before
the other -that wouldnt matter so long as the code still worked) and as
the percentage in worksheet 2 changes so would the ranking auto update.
3. A DROPDOWN SELECTION:I have placed some hyperlinks on DASHBOARD worksheet1 however I think it would be nicer and if there was a dropdown list of the tabs to choose from a dropdown of all tabs from worksheet 2 to worksheet 9 with the name of those worksheets in the drop down. Then once selected it will go straigth there with no further interaction from the user.

My Dashboard demo test file is attached

Hope someone would like to help. Thanks in advance
Regards

Vikki_61

[Bob Phillips]

1. The simplest and best way IMO is to select every row and every column outside of the viewing area and hide themn (Format>Row>hide), and then protect the sheet. I have done it to THE DASHBOARD and 2. REGIONS COMPARED, you can do the rest.

2. A simple large formula should so it.

3. I agree, those hyperlinks don't work. If you make th forms toolbar visible, there is a combobox or listbox that you can use.

[Vikki_61]

Thankyou Sir XLD

1. Your thoughts ofr stop scolling is a great easy approach.

2. The formula for Rankins is also good and easy.

3. I have tried the Forms tool bar - but not sure if sure if that will give me what I want. Which is to save space the user can pick from a drop down list - then as soon as they pick their selection that go automaticaly to the tab name of their choice with out needing to do anythink else.

Thankyou and have a good weekend Sir XLD


Vikki_61

[mikerickson]

1)

Application.DisplayScrollBars = False

or if you want a touch more control

Sheets(1).ScrollArea = "$A$1:$P$30"
Sheets(1).ScrollArea = vbNullString:Rem to un-prevent

[Bob Phillips]

3. I have tried the Forms tool bar - but not sure if sure if that will give me what I want. Which is to save space the user can pick from a drop down list - then as soon as they pick their selection that go automaticaly to the tab name of their choice with out needing to do anythink else.

The listbox gave you that, although it does use real estate. But it is easily replaced with a combobox.

Thankyou and have a good weekend Sir XLD

You too, although it is probably half gone over there b y now.