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


Joined
Dec 13, 2017
Messages
71
Reaction score
19
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
 
Ad

Advertisements

Joined
Dec 13, 2017
Messages
71
Reaction score
19
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
 
Joined
Feb 21, 2018
Messages
196
Reaction score
79
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.
 
Joined
Dec 13, 2017
Messages
71
Reaction score
19
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
 
Joined
Dec 13, 2017
Messages
71
Reaction score
19
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.
 
Joined
Dec 13, 2017
Messages
71
Reaction score
19
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.
 
Ad

Advertisements

Joined
Dec 13, 2017
Messages
71
Reaction score
19
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)
 
Joined
Feb 21, 2018
Messages
196
Reaction score
79
@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
 
Joined
Dec 13, 2017
Messages
71
Reaction score
19
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.
 
Ad

Advertisements

Joined
Dec 13, 2017
Messages
71
Reaction score
19
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.
 
Joined
Feb 21, 2018
Messages
196
Reaction score
79
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
 
Joined
Feb 21, 2018
Messages
196
Reaction score
79
@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
 
Joined
Dec 13, 2017
Messages
71
Reaction score
19
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.
 
Ad

Advertisements

Joined
Feb 21, 2018
Messages
196
Reaction score
79
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..
 
Joined
Dec 13, 2017
Messages
71
Reaction score
19
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.
 
Joined
Dec 13, 2017
Messages
71
Reaction score
19
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.
 
Ad

Advertisements

Joined
Feb 21, 2018
Messages
196
Reaction score
79
@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()
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top