+ Reply to Thread
Results 1 to 15 of 15

enable/disable stuff on open/close

  1. #1
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    240

    enable/disable stuff on open/close

    I have two subs to disable some stuff ON ALL WORKSHEETS whenever workbook opens and enable them back ON ALL WORKSHEETS when the workbook is closed. I also have a CommandButton1, by clicking on which, the workbook should be saved without any alerts and excel application should quit. Also when the user clicks on the X to quit, the workbook should get saved without alerts.

    Iam confused whether I should write this in ThisWorkbook or all Sheet modules.
    Also which method is correct to call a sub: CALL, RUN or just write the name of the sub?
    Will this affect any functionality of excel if the program is halted in between like computer freezing, killing the excel file or excel application process from task manager?
    Please advice.

    Please Login or Register  to view this content.
    Last edited by sabha; 12-02-2014 at 02:37 PM.

  2. #2
    Forum Contributor
    Join Date
    03-14-2013
    Location
    FL
    MS-Off Ver
    Excel 2013
    Posts
    123

    Re: enable/disable stuff on open/close

    I would put it in This Workbook as if a sheet gets deleted so will your macro. I always have a txt document with all my macros for safe keeping just in case. to quickly run your macro you can add a shortcut to hit and just hit something like ctrl+i to run the macro.

    when you disable stuff it should help with Excel crashing, although once excel crashes it has crashed and you need to restart and hope it autosaved something for you.

    hope this helps

  3. #3
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    240

    Re: enable/disable stuff on open/close

    Thanks for your reply
    How to put it ThisWorkbook and get it running on ALL WORKSHEETS. It will apply on the active worksheet if I am not mistaken...
    How often do you think excel will crash if the items mentioned in the code is disabled.
    What about the call methods? CALL, RUN or just write the name of the sub?
    And yes... quitting excel with X or by clicking a button without any alerts - the code should save the workbook and where/which module will that save line go?

  4. #4
    Forum Contributor
    Join Date
    03-14-2013
    Location
    FL
    MS-Off Ver
    Excel 2013
    Posts
    123

    Re: enable/disable stuff on open/close

    instead of With ActiveWidow you can use activeworkbook. that would make it apply to all sheets in your workbook.
    crashing excel really depends on what your doing and how much ram you have. if you try and copy a sheet that has 999999 rows and 200 columns most likly excel will always crash because it is too much for excel to handle. is the excel is an XLSM file (meaning macro capable) anytime you save it also saves the macros that you were working on in that workbook.

  5. #5
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    240

    Re: enable/disable stuff on open/close

    Yes, it is xlsm file. I tried active workbook but it gives error on the very first line .DisplayVerticalScrollBar = True
    Please Login or Register  to view this content.
    Also let me know about the last two questions:
    What about the call methods? CALL, RUN or just write the name of the sub?
    Quitting excel with X or by clicking a button without any alerts - the code should save the workbook and where/which module will that save line go?
    Thanks

  6. #6
    Forum Contributor
    Join Date
    03-14-2013
    Location
    FL
    MS-Off Ver
    Excel 2013
    Posts
    123

    Re: enable/disable stuff on open/close

    you can try using something like
    Please Login or Register  to view this content.

    you can google applying macro to all sheets in a workbook.

  7. #7
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    240

    Re: enable/disable stuff on open/close

    I did google and tried something but still not working. Here is my updated code

    In a new module
    Please Login or Register  to view this content.
    In ThisWorkbook
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    240

    Re: enable/disable stuff on open/close

    Please help...
    Thanks

  9. #9
    Forum Contributor
    Join Date
    03-14-2013
    Location
    FL
    MS-Off Ver
    Excel 2013
    Posts
    123

    Re: enable/disable stuff on open/close

    you can try

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    240

    Re: enable/disable stuff on open/close

    I tried that... it works only for sheet1 and not for 2 and 3

  11. #11
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    240

    Re: enable/disable stuff on open/close

    Awaiting a solution. Any idea?

  12. #12
    Forum Contributor
    Join Date
    03-14-2013
    Location
    FL
    MS-Off Ver
    Excel 2013
    Posts
    123

    Re: enable/disable stuff on open/close

    this code disabled all settings in all worksheets for me
    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    240

    Re: enable/disable stuff on open/close

    It is still not working.
    While opening the workbook, it disables status bar and formula bar for all sheets but not the headings, workbook tabs and scroll bars.
    Moreover, it gives me an error while closing.

    I dont know what is wrong in this code. I am attaching a copy of the excel file. Appreciate if someone can look into it.

    I want the status bar, formula bar, row/column headers, worksheet tabs and scroll bar to get disabled on file open and get enabled on file close.

    Thank you.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    08-20-2013
    Location
    Qatar
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: enable/disable stuff on open/close

    Try this


    With activewindow

    .displayheadings = false
    .displayscrollbars = false
    .displayworkbooktabs = false

    End with
    Last edited by junmacs; 12-19-2014 at 02:29 AM.

  15. #15
    Registered User
    Join Date
    08-20-2013
    Location
    Qatar
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: enable/disable stuff on open/close

    Try this


    With activewindow

    .displayheadings = false
    .displayscrollbars = false
    .displalyworkbooktabs = false

    End with

+ 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. Disable close workbook but still force users to close thru command button
    By rathig in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-18-2013, 07:29 AM
  2. Enable/disable copy paste (disable part not working)
    By timtim89 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-08-2012, 07:23 PM
  3. open-run macro, disable marcos, close email it
    By techexpressinc2 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-01-2011, 04:19 PM
  4. disable red X but keep enable file - close working
    By Rough in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-28-2008, 06:16 PM
  5. Suppress "Disable/Enable Macros" and Query Refresh dialog on open
    By Sharon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-18-2006, 05:25 PM

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