+ Reply to Thread
Results 1 to 6 of 6

Private Sub Workbook_Open Event Problem

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-25-2012
    Location
    Porto, Portugal
    MS-Off Ver
    Excel 2010
    Posts
    201

    Private Sub Workbook_Open Event Problem

    Hi...
    I have a macro that verifies some data in the workbook after it opens. If this data is ok the Workbook Open event lets the workbook open, else it saves and closes it.
    Now the problem is: as you may or not know if you open excel, and through the open menu try to open the same file again but this time with shift key pressed on, this time Workbook Open Event will not run and all the workbook with all the macro will work.
    So what i though was to add some sort of code that (as the Workbook Open Event was disable but all macros still works in the file) make after 30 secondes or less run the piece of code that was in the Workbook Open Event and like this verifies the data again and if the data was not ok, it still closes the work book.
    The thing is i don't know how to make a code run automaticaly after workbook open event is disable. If i do i could make it count 30 seconds and after this run the macro.

    Any ideias?
    I though add the code to another key macros in the workbook so if the user trigger any of then and the data is not ok it automatically closes the workbook. The thing is that this approach will make this macro keep running everytime that the user pressed some of the buttons that contains this macro, and that's why i would like a timer function, because this procedure would only run once.

    Thank you.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,489

    Re: Private Sub Workbook_Open Event Problem

    In the workbook close event handler, hide all the worksheets except one (splash screen - which you would unhide first). In the workbook open event handler, unhide all the worksheets AND run your validation code. Probably a bit more to it to make it bullet proof but that's the gist.

    If the workbook open event isn't run, there's nothing to look at. If it is, it will validate the data.

    You need to make the worksheets "very hidden", not just "hidden". And you need to protect your VBA project with a password.

    I'm a bit bemused though ... if the data is wrong, you'll never be able to open the workbook.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    06-25-2012
    Location
    Porto, Portugal
    MS-Off Ver
    Excel 2010
    Posts
    201

    Re: Private Sub Workbook_Open Event Problem

    Good idea. If i hide all worksheets when the workboook close and just let menu visible when it opens with shift pressed on, workbook open will not work but if not works all sheets will remain hidden. Good idea. I'll just add a timer of 30s to run the code in the workbook open event so if i need to change something i'll be able to enter in vba mode.
    Now just for asking, is there some way to prevent workbook close event like there is for workbook open? Because if there is, a user also could stop workbook close event (if the code is correct) and like this keep all sheets visible when opening again without workbook event on.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,489

    Re: Private Sub Workbook_Open Event Problem

    You're welcome. Thanks for the rep.


    is there some way to prevent workbook close event like there is for workbook open?
    Not that I am aware of.

    Regards, TMS


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  5. #5
    Forum Contributor
    Join Date
    06-25-2012
    Location
    Porto, Portugal
    MS-Off Ver
    Excel 2010
    Posts
    201

    Re: Private Sub Workbook_Open Event Problem

    From what i'm searching there is no way from preventing it, because it comes from the close button.
    Thanks for the idea.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,489

    Re: Private Sub Workbook_Open Event Problem

    I have an idea how you might do it. But I'm not going to explain now

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] On Error run Private Sub Workbook_Open() <-- is this possible?
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-14-2014, 03:38 AM
  2. Private sub workbook_open code running workbook_open code from other project
    By marshall_massive in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-20-2013, 06:07 PM
  3. [SOLVED] Help with a Private Sub Workbook_Open
    By jshaw82 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-26-2013, 02:59 PM
  4. Explain - Private Sub Workbook_Open()
    By hedge in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-13-2009, 06:55 PM
  5. Private Sub Workbook_Open() event won't fire if workbook is hidden?
    By Mike Weaver in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-15-2006, 09:40 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1