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
    530
    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 04:59 AM.

  2. #2
    Board Regular
    Join Date
    Nov 2015
    Posts
    496
    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 05:27 AM.

  3. #3
    Board Regular
    Join Date
    Nov 2015
    Posts
    496
    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
    29,967
    Post Thanks / Like
    Mentioned
    0 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 06:17 AM.

  5. #5
    Board Regular
    Join Date
    Apr 2013
    Posts
    530
    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
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com