PDA

View Full Version : Solved: Workbook_Open view in 100% zoom



U_Shrestha
05-29-2008, 02:23 PM
Hi,

Can someone please give me a code that will always open the workbook in 100% zoom? Some users change the worksheet zoom to 75% and that messes things up for other computers. Thanks.

grichey
05-29-2008, 02:26 PM
ActiveWindow.Zoom = 100

U_Shrestha
05-29-2008, 02:33 PM
Perfect!!

Thank you very much :)

grichey
05-29-2008, 02:33 PM
More specifically: Put this in your WorkBook module
Private Sub Workbook_Open()
ActiveWindow.Zoom = 100

End Sub

U_Shrestha
05-29-2008, 02:36 PM
Yeah, that's what I did.

Thanks again :thumb

U_Shrestha
06-02-2008, 01:26 PM
Sorry, the code didn't work. I want all the worksheet in the workbook to open in 100% zoom. I even tried recording following code from macro, didn't work. Can someone help? Thanks.
Sheets(Array("Sheet1", "Sheet2", "Sheet5")).Select
Sheets("Sheet1").Activate
ActiveWindow.Zoom = 100
Sheets("Sheet5").Select

grichey
06-02-2008, 04:09 PM
Let me know if this doesn't do it, but this works fine for me...

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets


ActiveWindow.Zoom = 100

Next ws

End Sub

grichey
06-02-2008, 04:10 PM
again loaded into you ThisWorkbook code...

U_Shrestha
06-03-2008, 06:39 AM
Hi Gavin,

Thanks for getting back to me. I used your code but it didn't change the zoom to 100% in Sheet2 and Sheet3, it only changed in Sheet1. Thanks.

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
ActiveWindow.Zoom = 100
Next ws
End Sub

mdmackillop
06-03-2008, 07:13 AM
It might upset your users though!
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ActiveWindow.Zoom = 100
End Sub

U_Shrestha
06-03-2008, 07:25 AM
That does it!! Thanks mdmackillop.

grichey
06-03-2008, 07:29 AM
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Select

ActiveWindow.Zoom = 100
Next ws
End Sub

grichey
06-03-2008, 07:32 AM
The code by mdmac is funny and would really irritate me!! Actually it would probably make me visibly angry haha. If you use what I posted, the users will at least have the option of changing their zoom.

What by the way does the zoom mess up?

mdmackillop
06-03-2008, 08:16 AM
The code by mdmac is funny and would really irritate me!! Actually it would probably make me visibly angry
Agreed!

U_Shrestha
06-03-2008, 08:35 AM
The workbook that I use, circulates to serveral users; one of the user, always views the workbook in 75% (maybe because of his screen resolution set-up, not sure, and when other users open the workbook (including me) the view looks shrunk. So, I have to manually change the view to 100% each time I use it; I prefer viewing it in 100%.

U_Shrestha
06-03-2008, 08:48 AM
Hi Gavin,

I tried your code in a new workbook and it works great, however, my actual workbook has other lines also in the Workbook_open() module and currently it looks like below; it is giving me an error, not because of the way you wrote, but I also have other lines in the same code. How can this code be corrected? Thanks.

Private Sub Workbook_Open()
Application.ScreenUpdating = False
'code to zoom program to 100%
Dim ws As Worksheet
For Each ws In Worksheets
ws.Select
ActiveWindow.Zoom = 100
'always opens workbook on protected mode
ws.Protect "password", AllowFiltering:=True, userinterfaceonly:=True, _
Contents:=True, DrawingObjects:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True

Next ws
'Fill values for combo-box in Audit Issues Page.
With Worksheets("AuditIssues").ComboBox1
.Clear
.AddItem "Active Issues 0 to 30 days old"
.AddItem "Active Issues 31 to 60 days old"
.AddItem "Active Issues 61 to 90 days old"
.AddItem "Active Issues 91 to 120 days old"
.AddItem "Active Issues >120 days old"
.AddItem "All Active Issues"
End With
Application.ScreenUpdating = True
End Sub

grichey
06-03-2008, 09:33 AM
For starters try this
ws.Protect Password:="password"

You may want to move the protection and zoom bit to the very last in your code as well so you can do whatever you want up top while it's unprotected then lock it at the last.

U_Shrestha
06-04-2008, 06:18 AM
I tried couple of combinations, including the one below, I am getting "compile error in hidden module: This Workbook" when I open the workbook and it is not working.
Private Sub Workbook_Open()
Application.ScreenUpdating = False
'Fill values for combo-box in Audit Issues Page.
With Worksheets("AuditIssues").ComboBox1
.Clear
.AddItem "Active Issues 0 to 30 days old"
.AddItem "Active Issues 31 to 60 days old"
.AddItem "Active Issues 61 to 90 days old"
.AddItem "Active Issues 91 to 120 days old"
.AddItem "Active Issues >120 days old"
.AddItem "All Active Issues"
End With
'code to zoom program to 100%
Dim ws As Worksheet
For Each ws In Worksheets
ws.Select
ActiveWindow.Zoom 100
'always opens workbook on protected mode
ws.Protect "password", AllowFiltering:=True, userinterfaceonly:=True, _
Contents:=True, DrawingObjects:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
Next ws
Application.ScreenUpdating = True
End Sub

grichey
06-04-2008, 08:05 AM
I don't have your combo box but the rest looks to run.
Private Sub Workbook_Open()
Application.ScreenUpdating = False
'Fill values for combo-box in Audit Issues Page.
With Worksheets("AuditIssues").ComboBox1
.Clear
.AddItem "Active Issues 0 to 30 days old"
.AddItem "Active Issues 31 to 60 days old"
.AddItem "Active Issues 61 to 90 days old"
.AddItem "Active Issues 91 to 120 days old"
.AddItem "Active Issues >120 days old"
.AddItem "All Active Issues"
End With
'code to zoom program to 100%
Dim ws As Worksheet
For Each ws In Worksheets
ws.Select
ActiveWindow.Zoom = 100
'always opens workbook on protected mode
ws.Protect "password", AllowFiltering:=True, userinterfaceonly:=True, _
Contents:=True, DrawingObjects:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
Next ws
Application.ScreenUpdating = True
End Sub

U_Shrestha
06-04-2008, 08:35 AM
Hi Gavin,

I tried your code in a new workbook and works perfectly, when I try it in my workbook I get this error:

Run-time error '1004'
Method 'Select' of object'_Worksheet' failed

I am wondering what is causing this, I have some hidden sheets in the workbook, can this be causing this problem? I also noticed that even though it gives an error message, it still zooms the first 3-sheets in the workbook, and then maybe the hidden sheets come in the way, the last sheet does not zoom to 100%. Any ideas? Thanks.

U_Shrestha
06-04-2008, 08:40 AM
Can the code be modified to select and zoom 100% only the Sheet1, Sheet2, Sheet3 And Sheet5; maybe this way the hidden sheets won't come on the way.

I tried hiding sheet1 from the new workbook, and the code doesn't work,confirmed. Sample workbook attached. Thanks.

grichey
06-04-2008, 09:31 AM
yes the hidden sheet is the problem. I'm not sure how to fix that. I've tried a few things and so far have not been able to find a solution. I suggest you post a new question 'how to unhide sheet' or something to that effect with your code thus far. Below is my failed attempt.

Private Sub Workbook_Open()
Application.ScreenUpdating = False
'Fill values for combo-box in Audit Issues Page.
With Worksheets("AuditIssues").ComboBox1
.Clear
.AddItem "Active Issues 0 to 30 days old"
.AddItem "Active Issues 31 to 60 days old"
.AddItem "Active Issues 61 to 90 days old"
.AddItem "Active Issues 91 to 120 days old"
.AddItem "Active Issues >120 days old"
.AddItem "All Active Issues"
End With
'code to zoom program to 100%
Dim ws As Worksheet
Dim visVar As Integer

For Each ws In Worksheets
visVar = 0
ws.Select
'check for visible
If ws.Visible = xlSheetHidden Then
ws.Visible = xlSheetVisible
visVar = 1
End If
ActiveWindow.Zoom = 100
'always opens workbook on protected mode
If ws.ProtectContents = True Then
ws.Unprotect Password:="password"
End If

ws.Protect "password", AllowFiltering:=True, userinterfaceonly:=True, _
Contents:=True, DrawingObjects:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
If visVar = 1 Then
ws.Visible = xlSheetHidden
End If

Next ws

grichey
06-04-2008, 09:42 AM
This works for me!
Private Sub Workbook_Open()
Application.ScreenUpdating = False
'Fill values for combo-box in Audit Issues Page.
With Worksheets("AuditIssues").ComboBox1
.Clear
.AddItem "Active Issues 0 to 30 days old"
.AddItem "Active Issues 31 to 60 days old"
.AddItem "Active Issues 61 to 90 days old"
.AddItem "Active Issues 91 to 120 days old"
.AddItem "Active Issues >120 days old"
.AddItem "All Active Issues"
End With
'code to zoom program to 100%
Dim ws As Worksheet
Dim visVar As Integer

For Each ws In Worksheets
visVar = 0

'check for visible
If ws.Visible = False Then
ws.Visible = True
visVar = 1
End If

ws.Select
ActiveWindow.Zoom = 100
'always opens workbook on protected mode
If ws.ProtectContents = True Then
ws.Unprotect Password:="password"
End If

ws.Protect "password", AllowFiltering:=True, userinterfaceonly:=True, _
Contents:=True, DrawingObjects:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
If visVar = 1 Then
ws.Visible = False
End If

Next ws
Application.ScreenUpdating = True
End Sub

U_Shrestha
06-04-2008, 10:15 AM
Hello Gavin,

Is the new code changing the invisible sheets to visible sheets and then changing the zoom to 100%? Can the zoom be changed to 100% only in the visible sheets, (without touching the invisible sheets whether it is just hidden or very hidden)?

Your new tweak works perfectly on the sample sheet that I sent but in my actual sheet it is still giving "Run-time error '1004' Method 'Select' of object'_Worksheet' failed". Thanks again.

grichey
06-04-2008, 10:27 AM
Yes it's changing the invisible sheet to visible then back again. Try this modification.
Private Sub Workbook_Open()
Application.ScreenUpdating = False
'Fill values for combo-box in Audit Issues Page.
With Worksheets("AuditIssues").ComboBox1
.Clear
.AddItem "Active Issues 0 to 30 days old"
.AddItem "Active Issues 31 to 60 days old"
.AddItem "Active Issues 61 to 90 days old"
.AddItem "Active Issues 91 to 120 days old"
.AddItem "Active Issues >120 days old"
.AddItem "All Active Issues"
End With
'code to zoom program to 100%
Dim ws As Worksheet
Dim visVar As Integer

For Each ws In Worksheets
visVar = 0

'check for visible
If ws.Visible = True Then


ws.Select
ActiveWindow.Zoom = 100
'always opens workbook on protected mode
If ws.ProtectContents = True Then
ws.Unprotect Password:="password"
End If

ws.Protect "password", AllowFiltering:=True, userinterfaceonly:=True, _
Contents:=True, DrawingObjects:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
'If visVar = 1 Then
' ws.Visible = False
'End If
'ws.Visible = True
'visVar = 1
End If
Next ws
Application.ScreenUpdating = True
End Sub

U_Shrestha
06-04-2008, 12:28 PM
Hi Gavin,

The code worked perfectly in my actual workbook. Thank you very much!
:joy: