Adjusting screen layout?

P

Peter Bernadyne

Hello,

I have designed several dedicated excel workbooks for use with specifi
Add-Ins I have assembled. On distributing these to users, I have com
across the somewhat vexing problem that everyone's excel layout i
different, which causes variations in what important features of
worksheet are visible and which are obscured, etc.

Obviously, I have designed the workbooks using my own settings and in
way that makes everything legible without requiring the user to scrol
(either horizontally or vertically) - making my workbooks appear almos
like self-contained applications. Ideally I would want these to appea
on everyone's machine just the way they do on mine.

Perhaps some of this depends on monitor size and display settings, bu
does anyone know if there is some simple code I could include in m
distributed workbooks that would standardize the way users view the
(like standardizing the view size to 100%, removing extra toolbars lik
drawing/reviewing, etc.) and then have all these put back into plac
exactly as they were once the user closes out the workbooks?

Thanks in advance to any help anyone can give.

-Pete
 
D

Don Lloyd

Hi Peter,

The simplest method that I've found, which works well, is to use the Zoom
function.

For example:

In your own settings, on your main worksheet, ensure that that the visible
columns fit the screen exactly.

Call the following code (or include it in) the Workbook Open Event.

Sub ZoomAdjust
Dim Zm,Sh
Sheets("Your Main Sheet").Activate
'Insert your visible column range below
Range("C1:T1").Select
ActiveWindow.Zoom = True
Zm = ActiveWindow.Zoom
For Each Sh In Worksheets
Sh.Activate
Sh.Unprotect
ActiveWindow.Zoom = Zm
ActiveSheet.Protect contents:=True, DrawingObjects:=True,
userinterfaceonly:=True
Next
Sheets("Your Main Sheet").Activate
End Sub

Yoy may. or may not, need the UnProtect & Protect features

At one time (Excel 97) zooming sometimes caused problems, but over time this
appears to have resolved itself.

Regards,
Don
 
S

Silas Mercer

Don,

Thank you very much, this worked very nicely.

That said, do you know if there is a feasible way to adjust the user'
settings so as to remove all extra toolbars (in order to give space t
the screen) and then place them back the way they were o
Workbook_Close rather than using the zoom feature?

I only bring it up because I have some small text in the workbook
which I fear might become illegible should the zoom require
shrinkinge to 80% or something.

If not, I thank you for your help anyway, this is definitely somethin
I could incorporate.

Best Regards,

-Pete
 
D

Don Lloyd

Hi Peter,
Although I use code to do as you wish, I am by no means an expert !

However, the following should get you going on the right track.
I trust that the Sub Names are self explanatory.

Sub HideUserTbars()
Static UserBars As New Collection
Dim Tbar
For Each Tbar In Application.CommandBars
If Tbar.Type <> 1 And Tbar.Visible = True Then
UserBars.Add Tbar
Tbar.Visible = False
End If
End Sub

Sub ShowUserTbars()
Dim Tbar
For Each Tbar In UserBars
Tbar.Visible = True
Next
End Sub

Hope that helps,
Don
 
D

Don Lloyd

Hi Peter,
Hold. The code I supplied won't work. I told you I wasn't an expert !
I was being clever and tried to split the code I use into two parts.

Here is the full code - as given, it rquires a specific cell for use as a
flag.
In the example i've used A1

Sub HideShowUserTbars()
Static UserBars As New Collection
Dim Tbar
If Range("A1") = 0 Then
For Each Tbar In Application.CommandBars
If Tbar.Type <> 1 And Tbar.Visible = True Then
UserBars.Add Tbar
Tbar.Visible = False
End If
Next
Range("A1") = 1
Else
For Each Tbar In UserBars
Tbar.Visible = True
Next
Range("A1") = 0
End If
End Sub

Regards,
Don
 
Top