Enusre new workbook displays ribbon and bars

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,825
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I use the following code to add back the ribbon and bars when I move off a particular worksheet:

Code:
Private Sub Worksheet_Deactivate()
    With Application
        .ScreenUpdating = False
        .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
        .DisplayFormulaBar = True
        .DisplayScrollBars = True
    End With
    With ActiveWindow
        .DisplayHeadings = True
        .DisplayWorkbookTabs = True
    End With
    Application.ScreenUpdating = True
    
End Sub

The problem is BEFORE I move off my particular worksheet, if I click Ctrl + N, the newly created workbook also hides the ribbon and bars, when actually I DON'T want them to be hidden.

It's as if clicking Ctrl + N does NOT activate the above code.

How can I make the above code run when Ctrl + N is pressed?

Thanks
 
Last edited:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Create a new macro in a standard module:

Code:
Sub MacroName()
Application.Run "ThisWorkbook.Workbook_Deactivate"
End Sub

Go to the developer tab, click macros, select your new macro, click options, type n into the box provided. Click ok.

Untested...but, in theory, now when you press CTRL + N it should create a new workbook & activate the macro, which will run the Workbook_Deactivate code.

Edit: Just tested...the macro keybind overrides the default function of CTRL + N....so I guess in the macro you can just add whatever code you need to add to create a new workbook so that it's basically doing the same thing, just in a roundabout way.
 
Last edited:
Upvote 0
Try this:

Code:
Sub MacroName()
Application.Run "ThisWorkbook.Workbook_Deactivate"
Workbooks.Add
End Sub

As I said in my previous post...bind it to CTRL + N and now it will run the deactivate code and then create a new workbook.
 
Upvote 0
Use the Workbook_Deactivate event (in the ThisWorkbook module) as well as your current Worksheet_Activate event.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top