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?

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.

--
Regards,
Tom Ogilvy

"Richard Bancroft" wrote in
message ...
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?

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?"

"Richard Bancroft" wrote:

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?

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"
wrote in message
...
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?"

"Richard Bancroft" wrote:

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?

Thanks Chip, I have got the button to work

"Chip Pearson" wrote:

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"
wrote in message
...
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?"

"Richard Bancroft" wrote:

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?

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)

"Richard Bancroft" wrote:

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?"

"Richard Bancroft" wrote:

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?

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?

"cdb" wrote:

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)

"Richard Bancroft" wrote:

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?"

"Richard Bancroft" wrote:

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?

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

"Richard Bancroft" wrote:

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?

"cdb" wrote:

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)

"Richard Bancroft" wrote:

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?"

"Richard Bancroft" wrote:

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?

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.

Richard Bancroft wrote:

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?

"cdb" wrote:

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)

"Richard Bancroft" wrote:

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?"

"Richard Bancroft" wrote:

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?

--

Dave Peterson