Adjusting screen layout?
 

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

--
Message posted from http://www.ExcelForum.com

Adjusting screen layout?
 

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

"Peter Bernadyne " wrote in
message ...
Hello,

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

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

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

Thanks in advance to any help anyone can give.

-Peter


---
Message posted from http://www.ExcelForum.com/

Adjusting screen layout?
 

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

--
Message posted from http://www.ExcelForum.com

Adjusting screen layout?
 

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

"Silas Mercer " wrote in
message ...
Don,

Thank you very much, this worked very nicely.

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

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

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

Best Regards,

-Peter


---
Message posted from http://www.ExcelForum.com/

Adjusting screen layout?
 

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

"Silas Mercer " wrote in
message ...
Don,

Thank you very much, this worked very nicely.

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

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

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

Best Regards,

-Peter


---
Message posted from http://www.ExcelForum.com/