custom view to display specific toolbars

[pwrichcreek]

I want certain custom views to display only specific toolbars. For example,
I'd like one view, let's call it MAXROWS, to show a maximum number of data
rows, so I'd like that view to display the smallest number of toolbars
possible. When I switch out of MAXROWS to another view, let's call it NORM,
I'd like to see several toolbars.

I can't seem to get this to work using custom views alone. Whatever toolbar
options I customize for MAXROWS are retained when I switch to NORM or any
other view.

It seem as if I should be able to do this using VBA, but I don't have a clue
which EVENTS and METHODS I should be using. (Or, perhaps I can do it without
VBA, but have not yet stumbled onto all the right buttons to push?)

TIA,

Phil

 

[JLGWhiz]

You might be trying to pick apples from the orange grove. I believe the
toolbars are independent and would not be affected by the view status. You
would have to control them separately.

 

[pwrichcreek]

Thanks for your reply JLG.

I agree that the toolbars are independent of the view status -- using only
the EXCEL user interface -- and that's what I'd like to alter. It seems I
might be able to "do" a custom view using VBA. Likewise, it seems I might be
able to "do" a toolbar customization using VBA. If both of those are
"do"-able, it further seems that the VBA code for each might be synchronized
using a VBA event or events.

I gather that your opinion is that this synchronization cannot be achieved
even with VBA.

Thanks,

Phil

 

[Gord Dibben]

How much do you want to show?

Which toolbars to be shown for MAXROWS?

Which for NORM?

Would you also want to turn off other display optiions like formula bar, status
bar, scrollbars?

More info will assist in getting some code but here is a sample.

Sub maxrow()
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
With ActiveWindow
.DisplayHeadings = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
End With
With Application
.CommandBars("Standard").Visible = False
.CommandBars("Formatting").Visible = False
End With
End Sub


Gord Dibben MS Excel MVP

 

[pwrichcreek]

Thanks for your reply Gord.

The goal is to make as much of the screen real estate available for
displaying data. The only thing I want to display at the top of the screen is
the main command bar: File, View, etc. I want to retain the vertical and
horizontal scroll bars. Your code is exactly the kind of example I was hoping
for.

Can I get the code to execute when the user chooses MAXROWS from the custom
view list and also similar code to make the items visible when the user
chooses NORM? What EXCEL/VBA event(s) would trigger the code?

Thanks,

Phil

 

[Gord Dibben]

I don't know offhand if a CustomView is a trappable event or not.

Could you have a couple of buttons added to a Toolbar or on sheet?

One for MAXROWS macro and one for NORM macro?

In the meantime, I'll play around and see if we can trap the event of changing
custom views or someone else will jump in and let us know.


Gord

 

[jsky]

Phil,
I don't have much to offer and it looks like Gord may be able to help. But
I could not avoid asking if you are aware of the VIEW Ribbon (on Excel 2007)
and then "Full Screen" botton. It certainly maximizes the rows to view...but
admittedly it also removes the File, Edit, etc menu row. All you do is hit
escape to get it all back.