Results 1 to 9 of 9

Thread: Disable X (Close) in form
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2003
    Posts
    403
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Disable X (Close) in form

    Hi! Friends,

    I have a form where in I want to disable the X (Close). I have used following code in my macro

    Application.CommandBars("File").Controls("Close").Enabled = False

    It is not working. Can anybody help me please

    Prakash

  2. #2
    Board Regular
    Join Date
    Jul 2003
    Location
    Manchester (UK)
    Posts
    4,493
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hi,
    This link includes the code to do this:

    http://j-walk.com/ss/excel/faqs/xl97faq6.htm
    HTH

    Alan

    --------------------------------------------------------
    Vlookup not sufficient? Follow the link for latest version of FuzzyVLookup or RuleLookup .
    Alternatively to compare two worksheets try Compare Two Sheets
    --------------------------------------------------------
    There are 10 kinds of people - those who understand binary and those who don't

  3. #3
    Board Regular
    Join Date
    Nov 2003
    Posts
    403
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    I got the code

    Private Sub UserForm_QueryClose _
    (Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
    MsgBox "You can't close the form like that."
    Cancel = True
    End If
    End Sub

    Where to put this code, whether in the form or in the module.

    Regards,
    Prakash

  4. #4
    Board Regular
    Join Date
    Jul 2003
    Location
    Manchester (UK)
    Posts
    4,493
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,


    Place it in the Userform.
    HTH

    Alan

    --------------------------------------------------------
    Vlookup not sufficient? Follow the link for latest version of FuzzyVLookup or RuleLookup .
    Alternatively to compare two worksheets try Compare Two Sheets
    --------------------------------------------------------
    There are 10 kinds of people - those who understand binary and those who don't

  5. #5
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Quote Originally Posted by N Prakash
    Hi

    I got the code

    Private Sub UserForm_QueryClose _
    (Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
    MsgBox "You can't close the form like that."
    Cancel = True
    End If
    End Sub

    Where to put this code, whether in the form or in the module.

    Regards,
    Prakash
    ?The paragraph immediately above the code that you refer to explains where to place the code!
    ... using a UserForm_QueryClose event procedure in the code module for the UserForm.
    Richie

  6. #6
    Board Regular
    Join Date
    Nov 2003
    Posts
    403
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    I tried putting in the module sheet and also in the form. But it is not working. I do not know what mistake I am doing.


    Prakash

  7. #7
    Board Regular
    Join Date
    Jul 2003
    Location
    Manchester (UK)
    Posts
    4,493
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Prakash,

    If you double-click the userform, VBA editor will take you top the code window. If you then select 'Query Close' from the drop-down list to the top right of the code window, it will automatically insert the start & end of the sub as follows:
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    End Sub
    Paste your code into there (but exclude the 'Private Sub...' and 'End Sub' lines of course.
    It might be a good idea to remove the code from anywhere else you may have put it.
    HTH

    Alan

    --------------------------------------------------------
    Vlookup not sufficient? Follow the link for latest version of FuzzyVLookup or RuleLookup .
    Alternatively to compare two worksheets try Compare Two Sheets
    --------------------------------------------------------
    There are 10 kinds of people - those who understand binary and those who don't

  8. #8
    Board Regular
    Join Date
    Nov 2003
    Posts
    403
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi! Alan,

    I tried the procedure as you have explained. No doubt it dispalys the message box, but it will not disable the X button of my form. If I close the message box, then I can close the form by clicking on the X button. I have seen some of the message boxes and dialogue boxes where in the X button is disabled.

    Regards,
    Prakash

  9. #9
    Board Regular
    Join Date
    Nov 2003
    Posts
    403
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Alan,

    Sorry it is working fine. Thanks for all the help.

Some videos you may like

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
  •