VBA screen size

[nparsons75]

Hi,

When I open a file I need the page size to be set for optimum viewing. In each tab of the worksheet I use this vba code.

Private Sub Worksheet_Activate()
Range("$A$1:$aL$1").Select
ActiveWindow.Zoom = True
End Sub

Im not too familiar with VBA but to make this work, when the file is open i have to click a different tab and then back to the one i am using to activate the screen size. Is there a different code to use? Is it something to do with activate? (just a guess)...

Thanks,

 

[sykes]

You're correct, in that because your code has been placed in the "Worksheet_activate" event, in each sheet, this is why it fires, each time a sheet is activated.
I'm guessing that it doesn't fire, however, when the workbook first opens.
For this to happen on the first open sheet, it may work if you use this:

Private Sub Workbook_Open()
ActiveSheet.Range("$A$1:$aL$1").Select
ActiveWindow.Zoom = True
End Sub

This code will fire when the workbook first opens.

NB ALWAYS test on a copy of your workbook, first!
Open your VBA browser, select "This workbook" from the LH menu, then on the RH pane, and from the LH dropdown at the top, select "Workbook."
Now, from the RH dropdown at the top, select "Open."

You should see this:

Private Sub Workbook_Open()

End Sub

This is where any code goes, which you want to run, when the workbook opens.

Cope & paste in the code I've given you (not the 1st or last lines, as they're already there!).
Save the workbook, close it, and re-open.

Does it work?

 

[nparsons75]

You're correct, in that because your code has been placed in the "Worksheet_activate" event, in each sheet, this is why it fires, each time a sheet is activated.
I'm guessing that it doesn't fire, however, when the workbook first opens.
For this to happen on the first open sheet, it may work if you use this:

Private Sub Workbook_Open()
ActiveSheet.Range("$A$1:$aL$1").Select
ActiveWindow.Zoom = True
End Sub

This code will fire when the workbook first opens.

NB ALWAYS test on a copy of your workbook, first!
Open your VBA browser, select "This workbook" from the LH menu, then on the RH pane, and from the LH dropdown at the top, select "Workbook."
Now, from the RH dropdown at the top, select "Open."

You should see this:

Private Sub Workbook_Open()

End Sub

This is where any code goes, which you want to run, when the workbook opens.

Cope & paste in the code I've given you (not the 1st or last lines, as they're already there!).
Save the workbook, close it, and re-open.

Does it work?

Hi,

I do not see the option for workbook open.... This may be as I am adding the code to a specific sheet by right clicking and viewing code, is this incorrect?

 

[sykes]

The method you're using to access the VBA pane, is fine.

Did you follow my very explicit instructions?

Open your VBA browser, select "This workbook" from the LH menu, then on the RH pane, and from the LH dropdown at the top, select "Workbook."
Now, from the RH dropdown at the top, select "Open."

Once in the VBA explorer, make sure your VBA project explorer is open (normally down the left-hand side) by either selecting "View" then "Project explorer", or using CTRL+R.
The LH menu should say at the top "Project - VBAProject."
Underneath, it lists your opened projects (including the one in question, and your "Personal.xlsb" project
If it's not already expanded, double-click the "VBAProject ([Your workbook name here])
If it's not already expanded, double-click the "Microsoft Excel Objects" folder
Look at the bottom of the sheets list - it should say "ThisWorkbook"
Double-click it
Now go to the large VBA pane on the RHS; you should be able to select "Workbook" from the top LH dropdown, and "Open" from the top RH one.

Then carry on as per my previous post...