Excel Code to run ActiveWindow.View in normal view, only runs manually.

FrancisM · Oct 29, 2019

I have placed this line of code in various positions on work sheets codes, & I have only been able to get it to run on one sheet.
ActiveWindow.View=xlNormalView
It will run on the other sheets if the run button is clicked in the code window. I need it to run automatically. I even tried to do it as a call, no change.
here is the full code:

Code:

Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    Application.DisplayFormulaBar = False
    Application.DisplayStatusBar = Not Application.DisplayStatusBar
    ActiveWindow.DisplayWorkbookTabs = False
    Application.ScreenUpdating = False
      'Turns scroll bars off for the indicated sheets
      With ActiveWindow
      .DisplayHorizontalScrollBar = False
      .DisplayVerticalScrollBar = False
    Application.ScreenUpdating = True
           ActiveWindow.View = xlNormalView
       Range("F13").Select
       End With
End Sub

FrancisM · Oct 29, 2019

I played around with the code & found if I had it on the 1st sheet, the other sheets were effected the same way. However, that only worked if the workbook was opening. If I went from worksheet to worksheet, it did not work.
Here is the code:

Code:

Private Sub Worksheet_Activate()
ActiveWindow.View = xlNormalView
    Application.ScreenUpdating = False
      Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
       Application.DisplayFormulaBar = False
         Application.DisplayStatusBar = Not Application.DisplayStatusBar
           ActiveWindow.DisplayWorkbookTabs = False
             Application.ScreenUpdating = True
               'Turn scroll bars off for the indicated sheets.
      With ActiveWindow
      .DisplayHorizontalScrollBar = False
      .DisplayVerticalScrollBar = False
 Range("K12").Select
 End With
End Sub

AmjiBhai · Oct 31, 2019

First of all Replace your existing code in all worksheet VBA (viewCode) as follows

Private Sub Worksheet_Activate()
If MsgBox("Normal View", vbYesNo) = vbYes Then
Do_Normal_View
Else
GetBack
End If
End Sub

Next step is Press Alt+F11
Insert a Module ...A new Module1 will appear....paste the following code in there...

Sub Do_Normal_View()

ActiveWindow.View = xlNormalView
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = Not Application.DisplayStatusBar
ActiveWindow.DisplayWorkbookTabs = False
Application.ScreenUpdating = True
'Turn scroll bars off for the indicated sheets.
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
Range("K12").Select
End With

End Sub

Sub GetBack()

ActiveWindow.View = xlPageLayoutView
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = Not Application.DisplayStatusBar
ActiveWindow.DisplayWorkbookTabs = False
Application.ScreenUpdating = True
'Turn scroll bars off for the indicated sheets.
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
Range("K12").Select
End With

End Sub

Next you can go ahead and play around....make any changes and see the effects...and so on.

FrancisM · Oct 31, 2019

AmjiBhai said:
First of all Replace your existing code in all worksheet VBA (viewCode) as follows

Private Sub Worksheet_Activate()
If MsgBox("Normal View", vbYesNo) = vbYes Then
Do_Normal_View
Else
GetBack
End If
End Sub

Next step is Press Alt+F11
Insert a Module ...A new Module1 will appear....paste the following code in there...

Sub Do_Normal_View()

ActiveWindow.View = xlNormalView
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = Not Application.DisplayStatusBar
ActiveWindow.DisplayWorkbookTabs = False
Application.ScreenUpdating = True
'Turn scroll bars off for the indicated sheets.
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
Range("K12").Select
End With

End Sub

Sub GetBack()

ActiveWindow.View = xlPageLayoutView
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = Not Application.DisplayStatusBar
ActiveWindow.DisplayWorkbookTabs = False
Application.ScreenUpdating = True
'Turn scroll bars off for the indicated sheets.
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
Range("K12").Select
End With

End Sub

Next you can go ahead and play around....make any changes and see the effects...and so on.

I tried your code & there was no difference. Is it possible I missed something? Here is the code that I used:

Code:

Option Explicit
Private Sub Worksheet_Activate()
 If MsgBox("Normal View", vbYesNo) = vbYes Then
Do_Normal_View
Else
GetBack
End If
End Sub

I followed your directions & created a new Module. Module 4

Code:

Sub Do_Normal_View()

ActiveWindow.View = xlNormalView
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = Not Application.DisplayStatusBar
ActiveWindow.DisplayWorkbookTabs = False
Application.ScreenUpdating = True
'Turn scroll bars off for the indicated sheets.
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
Range("K12").Select
End With

End Sub


Sub GetBack()

ActiveWindow.View = xlPageLayoutView
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = Not Application.DisplayStatusBar
ActiveWindow.DisplayWorkbookTabs = False
Application.ScreenUpdating = True
'Turn scroll bars off for the indicated sheets.
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
Range("K12").Select
End With

End Sub

FrancisM · Oct 31, 2019

AmjiBhai said:
First of all Replace your existing code in all worksheet VBA (viewCode) as follows

Private Sub Worksheet_Activate()
If MsgBox("Normal View", vbYesNo) = vbYes Then
Do_Normal_View
Else
GetBack
End If
End Sub

Next step is Press Alt+F11
Insert a Module ...A new Module1 will appear....paste the following code in there...

Sub Do_Normal_View()

ActiveWindow.View = xlNormalView
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = Not Application.DisplayStatusBar
ActiveWindow.DisplayWorkbookTabs = False
Application.ScreenUpdating = True
'Turn scroll bars off for the indicated sheets.
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
Range("K12").Select
End With

End Sub

Sub GetBack()

ActiveWindow.View = xlPageLayoutView
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = Not Application.DisplayStatusBar
ActiveWindow.DisplayWorkbookTabs = False
Application.ScreenUpdating = True
'Turn scroll bars off for the indicated sheets.
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
Range("K12").Select
End With

End Sub

Next you can go ahead and play around....make any changes and see the effects...and so on.

I found a workable solution, however it affect all workbooks. So If there is a VBA solution, that would be ideal. I right clicked on status bar & clicked view shortcuts, & macro recording. This removed those 2 from the status bar & left zoom.

FrancisM · Oct 31, 2019

Problem solved. This is the only solution I did not try. Place this line Active.view = xlNormalView on Private Sub Workbook_Open(). I wish I had tried this earlier. [U]AmjiBhai[/U] I want to thank you for working with me & taking time out of your busy schedule.

EvanDavis · Oct 31, 2019

FrancisM said:
@AmjiBhai I

Type @ then the persons name and they will be tagged

FrancisM · Nov 1, 2019

FrancisM said:

I tried your code & there was no difference. Is it possible I missed something? Here is the code that I used:

Code:

Option Explicit
Private Sub Worksheet_Activate()
If MsgBox("Normal View", vbYesNo) = vbYes Then
Do_Normal_View
Else
GetBack
End If
End Sub

I followed your directions & created a new Module. Module 4

Code:

Sub Do_Normal_View()

ActiveWindow.View = xlNormalView
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = Not Application.DisplayStatusBar
ActiveWindow.DisplayWorkbookTabs = False
Application.ScreenUpdating = True
'Turn scroll bars off for the indicated sheets.
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
Range("K12").Select
End With

End Sub


Sub GetBack()

ActiveWindow.View = xlPageLayoutView
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = Not Application.DisplayStatusBar
ActiveWindow.DisplayWorkbookTabs = False
Application.ScreenUpdating = True
'Turn scroll bars off for the indicated sheets.
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
Range("K12").Select
End With

End Sub

@AmjiBhai I don't know why I could not get your code working before. But it does work. Was wondering, about removing the MsgBox. Also different worksheets have different ranges (M12,A1,G20)

AmjiBhai · Nov 1, 2019

FrancisM said:
@AmjiBhai I don't know why I could not get your code working before. But it does work. Was wondering, about removing the MsgBox. Also different worksheets have different ranges (M12,A1,G20)

ok! replace
If MsgBox("Normal View", vbYesNo) = vbYes then

with

if ActiveWindow.View <> xlNormalView then

FrancisM · Nov 1, 2019

AmjiBhai said:
ok! replace
If MsgBox("Normal View", vbYesNo) = vbYes then

with

if ActiveWindow.View <> xlNormalView then

Thank you for being so understanding with me. What about the 3 cell ranges? The range depends on the sheet.

AmjiBhai · Nov 1, 2019

I am away... Will get back to you shortly...

FrancisM · Nov 1, 2019

FrancisM said:
Thank you for being so understanding with me. What about the 3 cell ranges? The range depends on the sheet.

Understood. I just noticed that Page Layout View is part of the code. I don't need this view, just normal.

AmjiBhai · Nov 1, 2019

FrancisM said:
Understood. I just noticed that Page Layout View is part of the code. I don't need this view, just normal.

So.... Please feel free to make necessary changes.... And let me know whenever you need help

FrancisM · Nov 1, 2019

AmjiBhai said:
So.... Please feel free to make necessary changes.... And let me know whenever you need help

@AmjiBhai When I comment out GetBack it goes to Pagebreak preview. I even tried a second normal view & it still goes to page break.

AmjiBhai · Nov 1, 2019

FrancisM said:
@AmjiBhai When I comment out GetBack it goes to Pagebreak preview. I even tried a second normal view & it still goes to page break.

Replace the following section of code in all worksheets
Private Sub Worksheet_Activate()
If MsgBox("Normal View", vbYesNo) = vbYes Then

With the following code

Private Sub Worksheet_Activate()
If 1=1 Then

FrancisM · Nov 1, 2019

AmjiBhai said:
Replace the following section of code in all worksheets
Private Sub Worksheet_Activate()
If MsgBox("Normal View", vbYesNo) = vbYes Then

With the following code

Private Sub Worksheet_Activate()
If 1=1 Then

I am still getting page break preview.

AmjiBhai · Nov 1, 2019

FrancisM said:
I am still getting page break preview.

The reason is obviously that you are not replacing all occurances of

Private Sub Worksheet_Activate()
If MsgBox("Normal View", vbYesNo) = vbYes Then

Note: if you have e.g three sheets then you need to do the above-mentioned replacement thrice..

FrancisM · Nov 1, 2019

AmjiBhai said:
The reason is obviously that you are not replacing all occurances of

Private Sub Worksheet_Activate()
If MsgBox("Normal View", vbYesNo) = vbYes Then

Note: if you have e.g three sheets then you need to do the above-mentioned replacement thrice..

Will get back to you.

FrancisM · Nov 1, 2019

AmjiBhai said:
The reason is obviously that you are not replacing all occurances of

Private Sub Worksheet_Activate()
If MsgBox("Normal View", vbYesNo) = vbYes Then

Note: if you have e.g three sheets then you need to do the above-mentioned replacement thrice..

@AmjiBhai You are brilliant. That was the problem ( old code on a sheet). Now I will wok on finding a common cell, for all the sheets.

AmjiBhai · Nov 1, 2019

FrancisM said:
@AmjiBhai You are brilliant. That was the problem ( old code on a sheet). Now I will wok on finding a common cell, for all the sheets.

You can have different cells selections in three different sheets.... All you need to do is insert the following just before the End Sub.

Range("A1").Select
You may change A1 to your desired cell address in respective sheet

This has to done three times if e. g you have three sheets.. Goto every sheet vba viewcode
Private Sub Worksheet_Activate()

Excel Code to run ActiveWindow.View in normal view, only runs manually.

Silly Fool