Google Answers Logo
View Question
Q: Excel 2002 - Custom Views ( Answered 5 out of 5 stars,   2 Comments )
Subject: Excel 2002 - Custom Views
Category: Computers > Software
Asked by: vodguy-ga
List Price: $5.00
Posted: 12 Aug 2006 07:38 PDT
Expires: 11 Sep 2006 07:38 PDT
Question ID: 755283
I have many Custom Views in a spreadsheet.  I want to be able to
create what, in effect, is a hyperlink in the worksheet such that I
can click on the link and have excel change to a particular view (as
opposed to using menus).  How can I do this?
Subject: Re: Excel 2002 - Custom Views
Answered By: maniac-ga on 17 Aug 2006 18:35 PDT
Rated:5 out of 5 stars
Hello Vodguy,

I assume you want to pick the view by name from a list, the following
answer implements that solution, but if you want another method,
please make a clarification request and I can make the change.

The macro "setView" is included at the end of the message. First, let
me explain how to add it to a workbook & then how to activate it from
a "list box" button.

[0] Make sure your workbook is open (or open it)

[1] Using the menu
  Tools -> Macro -> Visual Basic Editor
to bring up Visual Basic. In the upper left is a "project window" and
your workbook should be listed (and selected).

[2] With your workbook selected, use the menu
  Insert -> Module
and a code window should appear with a title like:
  vodguy.xls - module1(code)
[the exact format & names may vary slightly]

[3] Copy & paste the macro into this module window. Make any changes
you may want to make. For example, in my sample file there's a list of
view names in a column starting at $A$1 (row 1, column 1) and $B$1
(row 1, column 2) is open to have the index value of the "selected
view" (the first name is 1, second is 2, and so on). The first line
  Name$ = ActiveSheet.Cells(ActiveSheet.Cells(1, 2).Value, 1).Value
grabs the index value ($B$1) & uses that as the row number (the column
number is 1) to grab the name of the view. I assume you will move
these to some place out of the way - adjust the row / column numbers
to match. For example, if the view names start at $H$8 (going down)
and the reference is at $I$8, then this line should be
  Name$ = ActiveSheet.Cells(7+ActiveSheet.Cells(9, 8).Value, 8).Value

[4] Quit and return to Microsoft Excel (another menu item - title may
vary by Excel version)

At this point, make sure your view names are entered onto the
worksheet & you have an empty cell to use as the "index" value. At
this point, I used the menu
  Tools -> Customize
and selected the "Form" toolbar (and then OK).

The form toolbar should appear. The "list box" button can be
identified by hovering over the buttons, but on my system it looks
something like up / down arrows next to some text, all within a box.
Click on that button on the toolbar & then drag the cursor over a
convenient area of the worksheet for the list box button.

On my system, a rectangle appears (that can be resized) and if you
right click, and select control properties (or "format control) to
bring up a dialog box (with tabs). The values I set were on the
control tab (I left the rest alone):
  Input range - select the range of cells with the view names in them
  Cell link - the cell I referred to as the "index cell"
then OK

There's also an "Assign macro" menu item when you right click, use
that to select setView as the macro to run when this control is

Click outside the rectangle to deselect the control.

At this point, a left click on the control will bring up the list of
names - if wrong, use right click / bring up the menu & fix it. When
you select one of the values, it should activate the view that matches
that name. If the name doesn't match the name of a view, you will get
an error message / be offered to debug the macro.

Please make a clarification request if any part of the answer is
unclear, the macro does not work for you, or if you are unable to make
the changes needed for your set up. I would be glad to help.

Good luck with your work.


Sub setView()
'   Macro created on August 17, 2006 by Maniac
Name$ = ActiveSheet.Cells(ActiveSheet.Cells(1, 2).Value, 1).Value

End Sub

Request for Answer Clarification by vodguy-ga on 17 Aug 2006 22:36 PDT
Hi ... thanks for the work.   I really wanted to do this without the
form, though (if I wanted to use a form I could merely use
View->Custom Views).

A function that I can put in a cell that would say SetView("MyView4") 
... and once entered it would appear as MyView4 in the cell, with a
'hyperlink' - meaning if they click on the cell, then SetView would
change the current view to MyView4.

Recognizing this is probably a little more work than expected (I was
hoping for a pre-existing function, or a simple macro), and since I
don't know how to change the price ... I will offer to compensate by
adding a tip so that the answer totals $15.00

Clarification of Answer by maniac-ga on 18 Aug 2006 13:33 PDT
Hello Vodguy,

I am not sure what you mean by a "form" - the form toolbar that I
referred to in the answer allows you to add a button (or other
controls) to your worksheet.

Take a look at
for the worksheet example I was using.

It now has the solution implemented in four different methods:
 o the original list box (on the right) - click / move the cursor up
and down to select one of the three views I defined (AAA, BBB, or
 o using buttons (on the left) - click on the "View A" button to bring
up view AAA, "View B" to bring up view BBB, or "View C" to bring up
view CCC
 o using an object (in this case, a picture w/ text) to key the macro;
click on the AAA below the buttons to illustrate this one
 o using a worksheet macro that triggers on each cell activation. The
three items AAA, BBB, CCC near the bottom trigger this - the
Worksheet_SelectionChange macro has the code to trigger this.

I reviewed the Excel documentation for hyperlinks - they only select a
specific cell on a specific worksheet. There is no hook to run a macro
from a hyperlink.

I hope one of these method are suitable for your solution. Please make
another clarification request if needed if you have problems accessing
the file or if the changes you need for your worksheet are not clear.

vodguy-ga rated this answer:5 out of 5 stars and gave an additional tip of: $10.00
Good solution and extra work put it (beyond searching) ... I
appreciate the example that I could download and modify.

Subject: Re: Excel 2002 - Custom Views
From: brittonl-ga on 12 Aug 2006 14:56 PDT
Could you use a simple recorded macro and assign it to a button to
give similar functionality?
Subject: Re: Excel 2002 - Custom Views
From: vodguy-ga on 16 Aug 2006 20:07 PDT
Sure ... that would be OK.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy