none
disable esc press when macro running and disable cancel button from save dialogue box in excel for a workbook when workbook close RRS feed

  • Question

  • I have a workbook where i have used login and passcode for entering in excel workbook. But if i do interrupt the macro before appearing userform then it will be open for view the numbers so  i would like disable the esc press till the completion of macro. I am looking one more help to disable the "Cancel" option while closing the workbook. I do need the workbook should close with save without appearing the Save dialogue box because if someone tries to interrupt the workbook then suddenly save dialogue box appears and if i do click on "Cancel" then again it is getting approachable for anyone.

    Request to you please help me out. Your kind help would be appreciated.

    Neilesh

    Wednesday, December 6, 2017 5:43 PM

All replies

  • You could protect the VBA code with a password. That way, the code cannot be viewed unless the user knows the password.

    You can set the password by selecting Tools > (projectname) Properties... > Protection tab in the Visual Basic Editor.

    Tick the check box, specify the password twice, then click OK.

    Warning: it's possible to find code on the internet to disable the protection, so a password won't keep out knowledgeable users. But it will keep out ordinary users.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, December 6, 2017 6:42 PM
  • Hi Neil.K.007,

    you had asked,"if i do interrupt the macro before appearing userform then it will be open for view the numbers so  i would like disable the esc press till the completion of macro."

    it looks like there is a time gap between opening the workbook and displaying the userform. so user can press the esc key.

    in this situation you can set Application.Interactive Property to False.

    this property is usually True . If you set the this property to False , Microsoft Excel will block all input from the keyboard and mouse (except input to dialog boxes that are displayed by your code).

    Application.InterActive = False

    then you can again set it to True after completing your operation.

    other thing you had asked,"I am looking one more help to disable the "Cancel" option while closing the workbook. I do need the workbook should close with save without appearing the Save dialogue box because if someone tries to interrupt the workbook then suddenly save dialogue box appears and if i do click on "Cancel" then again it is getting approachable for anyone."

    on this issue, you can try to save as your workbook first with the code. if workbook is not saved and someone tries to close then it will display the save as dialog box.

    then after you can try to use before_save event and try to use line below.

    Thisworkbook.Saved = True

    it will not show you the prompt. you also need to remember to save the workbook first from the code and then close it.

    Reference:

    How to suppress "Save Changes" prompt when you close a workbook in Excel

    Application.Interactive Property (Excel)

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 7, 2017 2:25 AM
    Moderator
  • Hi Deepak,

    Many thanks for your support, I have tried "Application.InterActive=False" in a module under Workbook Open and "ThisWorkbook.Saved=True" under Workbook before close, but still my workbook getting interrupt while open by pressing Esc and when i do click on "X" Close, still i am getting Saving dailogue box.

    Request to you please do help me out. Any help would be highly appreciated.

    Regards,

    Neilesh

    Thursday, December 7, 2017 5:40 PM
  • Hi Neil.K.007,

    as per the documentation, it should work.

    it is better if you can post your workbook with sample data in it.

    we will try to make a test with it and try to check the issue.

    if possible then we will try to correct it and try to provide you a suggestion to solve the issue.

    thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 8, 2017 5:45 AM
    Moderator
  • Hi Neil.K.007,

    I can see that you did not follow up this thread, after my last post.

    Is your issue solved?

    if yes I suggest you to post the solution and mark the suggestions as an answer which helped you to solve your issue.

    if your issue is still exist then try to refer my last suggestion and try to provide sample workbook.

    if you have any further questions then let us know about that.

    we will try to provide you further suggestions to solve the issue.

    I suggest you to take appropriate action for this thread will be helpful to other community members in future who will have same kind of issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 14, 2017 2:51 AM
    Moderator
  • Also Neil.K.007, there is a different way to cleanly handle user attempts to interrupt code with Esc or Ctrl+Break. As you haven't posted back maybe you've found and implemented it, if not say if you are still looking for a solution.
    Thursday, December 14, 2017 5:23 PM
    Moderator