Enusre new workbook displays ribbon and bars

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Enusre new workbook displays ribbon and bars

  1. #1
    Board Regular
    Join Date
    Apr 2013
    Posts
    574
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Enusre new workbook displays ribbon and bars

     
    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 by tiredofit; Jun 19th, 2017 at 05:59 AM.

  2. #2
    Board Regular
    Join Date
    Nov 2015
    Posts
    508
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Enusre new workbook displays ribbon and bars

    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 by DushiPunda; Jun 19th, 2017 at 06:27 AM.

  3. #3
    Board Regular
    Join Date
    Nov 2015
    Posts
    508
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Enusre new workbook displays ribbon and bars

    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.

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    30,640
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Enusre new workbook displays ribbon and bars

    Use the Workbook_Deactivate event (in the ThisWorkbook module) as well as your current Worksheet_Activate event.
    Last edited by RoryA; Jun 19th, 2017 at 07:17 AM.

  5. #5
    Board Regular
    Join Date
    Apr 2013
    Posts
    574
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Enusre new workbook displays ribbon and bars

      
    Cheers!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com