VBA to set multiple sheets Zoom level at 50% while opening workbook

[Negi1984]

Hi All,

I have multiple sheet in a workbook for example : sheet name 1,2,3,4,5,6

Now I want every time when I open excel file it automatically set the zoom level for all the sheets except sheet no. 2 &3.
So except sheet no. 2 & 3 , all sheets should be at zoom level 50%.

Please suggest , how can I do this with the help of macro.

Regards,
Rajender

 

[My Aswer Is This]

Try this script:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu

Look up in left corner of window and double click on ThisWorkbook​

Paste this code in the VBA edit window

Now this should work for you

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'Modified  8/22/2018  7:11:48 AM  EDT
If ActiveSheet.Index <> 2 And ActiveSheet.Index <> 3 Then ActiveWindow.Zoom = 50
End Sub

 

[Negi1984]

Hi,

if its possible to set the above condition only while opening a file ?
I am asking because workbook contains map snapshot and user zooming it while working. if user select same sheet again then , again he needs to increase zoom level.
That's why I am looking for solution , if anybody open the workbook then it should open in default zoom.

 

[My Aswer Is This]

Try this:
Install as shown in previous post:

Private Sub Workbook_Open()
'Modified  8/22/2018  8:12:22 AM  EDT
Dim i As Long
    For i = 1 To Sheets.Count
        Sheets(i).Activate
        If ActiveSheet.Index <> 2 And ActiveSheet.Index <> 3 Then ActiveWindow.Zoom = 50
    Next
End Sub

 

[Negi1984]

Hi,

Thanks a lot. its working fine now.
Have a nice day to you.

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Hi,

Thanks a lot. its working fine now.
Have a nice day to you.

 

[Negi1984]

Hi ,

I am basic user of VBA macro and every time when I stuck in excel ,this is the site where I get the solution.
In order to learn more I am asking my team members if I can help them to automate Report .
I am also working in one of the macro based automation of report and probably needs support once again

But one issue is that , so many condition and thinking how to explain here .

 

[My Aswer Is This]

I always believe if you want to learn Vba and not just come here wanting a script written for you. And then you have no ideal what the script is doing you just know what it does you may never really learn how to write your own scripts.

So i think it's best to start out with small projects just like the one you just mentioned.

I was happy the first time I was able to write code to have the word George show up on the screen.

So I suggesting starting with small projects first and work into more complex projects.

But if your in a hurry and just want someone to write you a script to do a certain task that's OK also but if you cannot understand the code it may not help you to learn to do things on your own.

Like: Cells(1,1). value="George"

That seems simple to read in my opinion.

you should know what a cell is
And 1,1 means row 1 column 1

And so on.

Excel Vba uses a lot of simple terms in my opinion.

You should understand what a Workbook is a Sheet is a row is a column is

If you do not understand those terms you should start there.

Other terms like Activate seem easy to understand.


Or: Sheets("George").cells(1,1).Value="George" seems simple.

Now something like:

[A1]="George" may not jump out at you

[A1] is the same as saying Cells(1,1)

Now some people may want to say using one or the other is .00004 milliseconds faster.

So I would try starting with simple projects and write simple code and look and learn.

I always believe in writing simple code that maybe others can understand and learn from even if it is slower.

One or two seconds does not slow me down. I'm not in that big a hurry.
I believe in the old saying:

It's better to: Teach a man to fish then to fish for him.

Wishing you a nice day.

 

[Negi1984]

Hi ,

Thank a lot for your valuable suggestion. I also try to learn by pressing f8 key to understand what code is doing.
Mainly I found issue to understand Looping and array calculation. if you can suggest me any good references to learn that ?

I always believe if you want to learn Vba and not just come here wanting a script written for you. And then you have no ideal what the script is doing you just know what it does you may never really learn how to write your own scripts.

So i think it's best to start out with small projects just like the one you just mentioned.

I was happy the first time I was able to write code to have the word George show up on the screen.

So I suggesting starting with small projects first and work into more complex projects.

But if your in a hurry and just want someone to write you a script to do a certain task that's OK also but if you cannot understand the code it may not help you to learn to do things on your own.

Like: Cells(1,1). value="George"

That seems simple to read in my opinion.

you should know what a cell is
And 1,1 means row 1 column 1

And so on.

Excel Vba uses a lot of simple terms in my opinion.

You should understand what a Workbook is a Sheet is a row is a column is

If you do not understand those terms you should start there.

Other terms like Activate seem easy to understand.


Or: Sheets("George").cells(1,1).Value="George" seems simple.

Now something like:

[A1]="George" may not jump out at you

[A1] is the same as saying Cells(1,1)

Now some people may want to say using one or the other is .00004 milliseconds faster.

So I would try starting with simple projects and write simple code and look and learn.

I always believe in writing simple code that maybe others can understand and learn from even if it is slower.

One or two seconds does not slow me down. I'm not in that big a hurry.
I believe in the old saying:

It's better to: Teach a man to fish then to fish for him.

Wishing you a nice day.

 

[My Aswer Is This]

Well I'm not sure how much you know about Excel Vba.

Here is a simple loop that puts the numbers 1 to 10 in cells 1 to 10 of column A

Now your asking about arrays tells me you know more then I thought you did.
A new user of vba would normally not start out asking about arrays.

And I have never heard of Array calculations

What do consider to be a Array calculation?