Views available to the entire workbook, not just 1 worksheet

[Greg in CO]

Hi All!

I am reposting this with some additional information:

Here is the code for two of the macros which each trigger a custom view.
One (FULL_VIEW) will unhide all the rows on the worksheet. The other
(SUMMARY_VIEW) will hide all the rows except the summary data at the top.
The views and the macros have the same names/naming convention and when I
opened the VB Editor, the macros were located in the window for Module 7.

*************************** Code Starts Below

Sub SUMMARY_VIEW()
'
' SUMMARY_VIEW Macro
' Macro recorded 8/27/2008 by Greg Starr
'

'
ActiveWorkbook.CustomViews("SUMMARY_VIEW").Show
End Sub


Sub FULL_VIEW()
'
' FULL_VIEW Macro
' Macro recorded 8/27/2008 by Greg Starr
'

'
ActiveWorkbook.CustomViews("FULL_VIEW").Show
End Sub

**********************Code Ends Above

The project worksheet that reflects the lifecycle of a project...and it is
pretty large. I want to make it easy for my users to work with, so I wanted
to create views, recorded in macors, and then linked to Command Buttons.
Unfortunately, the views only apply to the sheet on which they were recorded.

I would like to have standard views so that when a new project is added and
the worksheet created, the command buttons on that new sheet trigger the
macros executing the views, but they only change on that sheet. This will be
a shared workbook, so there could be users changing views on different
worksheets simultaneously.

As it is now, if I create views on ProjectA, do macros, link them to Command
Buttons and then make a copy of the sheet as ProjectB, when I click on the
button on ProjectB, the view on ProjectA cahnges.

To me, this is much like flipping the lightswitch in the bathroom and having
the garage door open.

Is there a way to make standard views available for the entire workbook, but
when you use the buttons, they are applicable only to the active sheet where
the button sits?

Thanks!

 

[RyanH]

Unfortunately, I think the CustomViews property is what it is and can not be
specified to a particular worksheet unless recorded in that worksheet. But
you can use VBA to do the same thing. Since you already know which rows you
want to hide just put them into the code that is below. Then under your
Command Button Click event call which ever view you want. Put this code in a
Standard Module.

Public Sub CustomView1()

With ActiveSheet
' unhide all rows
.Cells.EntireRow.Hidden = False
' hide rows
.Range("1:5,10:15,20:25").EntireRow.Hidden = True
End With

End Sub

Public Sub CustomView2()

With ActiveSheet
' unhide all rows
.Cells.EntireRow.Hidden = False
' hide rows
.Range("2:12,25:35").EntireRow.Hidden = True
End With

End Sub

 

[Greg in CO]

Thanks Ryan! That worked great! It was short and simple!

I used the same code to create the "undo" button, to reverse the action of
the code you sent....I just changed the final arguement on the range to
FALSE...works great! I did test it on a different sheet..no problems!

Thanks again!