1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

disable x to close in excel sheet

Discussion in 'VBA Macros' started by narsing rao, Mar 13, 2018.

  1. narsing rao

    narsing rao Member

    Messages:
    116
    Hi all,

    i have excel sheet in that i have created form control button which save & close the excel.

    but few of my user not using the button but using X close at top right corner close option.

    i want my user to using this button so that in one instance they can save and close the excel , i want to disable this X option from excel.
  2. Belleke

    Belleke Active Member

    Messages:
    481
    Hi,
    I think you want something like this.
    Code (vb):
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then
            Cancel = True
            MsgBox "The X is disabled, please use the Close Command Button.", vbCritical
        End If
    End Sub
     
  3. narsing rao

    narsing rao Member

    Messages:
    116
    it doesnt worked .. i have created form control button named save & close to save and close excel sheet in one instance. it is created in excel sheet only there is no userforms used.i have attached the pic and assigned below code to button

    Code (vb):

    Sub SaveAndClose()
    ActiveWorkbook.Save
    ActiveWorkbook.Close
        'ActiveWorkbook.Close SaveChanges:=True
       Workbooks("Break_tr1.xlsm").Close
    Application.Quit
    End Sub
    [\code]

    Attached Files:

  4. Belleke

    Belleke Active Member

    Messages:
    481
    I tought that you had a userform
    Try this instead in ThisWorbook
    Code (vb):
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If CloseMode = 0 Then Cancel = True
    MsgBox "The X is disabled,please use the Save & Close button"
    End Sub
  5. narsing rao

    narsing rao Member

    Messages:
    116
    Hi Thanks for the code ..
    now the X disabled but when i click on Save & close button still its giving me the same message "The X is disabled,please use the Save & Close button"
  6. narsing rao

    narsing rao Member

    Messages:
    116
    and the excel sheet is not getting closed
  7. Belleke

    Belleke Active Member

    Messages:
    481
    Try this in the button code
    Code (vb):
    Sub SaveAndClose()
    If CloseMode = 0 Then Cancel = False
    'rest of your code
    end sub
  8. narsing rao

    narsing rao Member

    Messages:
    116
    Hi ..still i am geting same message " The X is disabled,please use the Save & Close button" and after clicking save & close button it shows same message and excel doesn't close
  9. Belleke

    Belleke Active Member

    Messages:
    481
    Hi, this works, tested this time
    Code (vb):
    Public myRao As Boolean
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Run from "ThisWorkbook" module!
    If myRao = True Then Exit Sub
    Application.DisplayAlerts = False
    Cancel = True
    MsgBox "The X is disabled,please use the Save & Close button", vbCritical, "Error"
    End Sub
    Sub SaveAndClose()
    'Run from "ThisWorkbook" module!
    myRao = True
    ActiveWorkbook.Close SaveChanges:=True
    Application.DisplayAlerts = False
    ActiveWindow.Close False
    End Sub
  10. narsing rao

    narsing rao Member

    Messages:
    116
    i am doing anything wrong...still i am getting same error ""The X is disabled,please use the Save & Close button" while clicking ion button

    Code (vb):

    Public myRao AsBoolean
    PrivateSub Workbook_BeforeClose(Cancel AsBoolean)
    'Run from "ThisWorkbook" module!If myRao = TrueThenExitSub
    Application.DisplayAlerts = False
    Cancel = True
    MsgBox "The X is disabled,please use the Save & Close button", vbCritical, "Error"
    EndSub

    i copied this in thisworkbook

    and remaining code in code Module.
  11. Belleke

    Belleke Active Member

    Messages:
    481
    All the code goes in ThisWorkbook module
    see attached (working example)

    Attached Files:

    narsing rao likes this.
  12. narsing rao

    narsing rao Member

    Messages:
    116
    Thanks a ton ...its working now ...solved :)
  13. Belleke

    Belleke Active Member

    Messages:
    481
    Thanks for the feedback
    Glad to help.

Share This Page