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
activated.
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.
--Maniac
Sub setView()
'
' Macro created on August 17, 2006 by Maniac
'
Name$ = ActiveSheet.Cells(ActiveSheet.Cells(1, 2).Value, 1).Value
ActiveWorkbook.CustomViews(Name$).Show
End Sub |
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
http://homepage.mac.com/mhjohnson/maniac-ga/vodguy.xls
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
CCC).
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.
--Maniac
|
