How can I set up a dashboard in Excel from which different worksh.

[Guest]

I have a pricing model with about a dozen worksheets in Excel 2003, so that
there are more worksheet tabs than can be seen without scrolling. How can I
set up a "dashboard" which lists all the worksheets and allows the user to
click a button to go to that worksheet?

 

[Tom Ogilvy]

right click on the lower left corner of the workbook (the vcr controls) and
you get a list of worksheet names. Select one and your there.

 

[Guest]

I realise that you can use the 'VCR' controls to scroll through the various
tabs but I want the spreadsheet to present a dashboard when opened which
presents all the worksheet options and from which the user can navigate to
any of them by pressing a command button. Essentially I am asking "how do I
program a command button to move the user from one part of a workbook to
another?"

 

[Chip Pearson]

Use Application.GoTo. E.g,.

Private Sub CommandButton1_Click()
Application.GoTo Worksheets("Sheet1").Range("A1"),True
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Richard Bancroft" <[email protected]>
wrote in message

 

[Guest]

Create an index page and on there create hyperlinks to the relevant tab.

(Select cell, right click to bring up menu, click on Hyperlink then select
Place in this Document, select tab and cell reference)

 

[Guest]

Thanks Chip, I have got the button to work

Use Application.GoTo. E.g,.

Private Sub CommandButton1_Click()
Application.GoTo Worksheets("Sheet1").Range("A1"),True
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Richard Bancroft" <[email protected]>
wrote in message

 

[Guest]

Thanks, that would be one way of doing it, but I am trying to get to grips
with VBA as much as get the result. I have now set up a 'dashboard' i.e. a
user form with command buttons which when clicked will bring up any of the
worksheets. However 1) I can only access it via Tools/Macro/Macros. How can I
get the user form to appear on an index page when the workbook is opened, or
better still, to float like a toolbox on any worksheet? and 2) Although I can
click on a command button on the form and navigate to any worksheet, I have
to close the user form before I can move the cursor to the worksheet. How can
I access the worksheet but keep the user form open?

 

[Guest]

Eureka! Re below 2) I've figured out that by changing the user form to Show
as 'modeless' I can access the worksheets without closing the dashboard - any
help with query 1) would still be appreciated

 

[Dave Peterson]

You could have an auto_open sub just show the form:

sub auto_open()
userform1.show False
end sub

But you may want to add a button that reshows the userform if it's closed by
mistake.