VBA limit file exit option to my command button

[MSingh]

Hi,

I have 2 questions:

1. Before navigating (by macro) to another worksheet i need to ensure that the user closes any visible userform/s otherwise the userform also "goes" to the other worksheet. Is there a way to prevent this or do i have to write the close cmd for each userform & each worksheet that uses a userform. It would be best if there is also a before close workbook event that also does same on exit.

2. How can i disable the user from exiting my workbook by clicking the "X" on the right hand top corner? The user would use my "Save & Exit" cmd.

Regards
Mohamed

[HansV]

By default, a userform is modal, i.e. the user cannot click in the workbook while the userform is active. Hence the user cannot activate another worksheet while the userform is open.
But if a userform is modeless (either because you have set its ShowModal property to False, or because you opened the userform using a line such as UserForm1.Show False), the user can click in the workbook while the userform is active. You could use the Worksheet_Deactivate event in the sheet module to close the userform:

Code: Select all

Private Sub Worksheet_Deactivate()
  On Error Resume Next
  Unload UserForm1
End Sub

Alternatively, you could hide all sheets except one when the userform is open.

To prevent the user from closing the workbook using the close button in the upper right corner, you can do the following:

1) Add the following declaration at the top of a standard module:

Code: Select all

Public blnCanClose As Boolean

and code like this to close the workbook (in a macro or in the On Click event procedure of a command button on a userform):

Code: Select all

  blnCanClose = True
  ActiveWorkbook.Close SaveChanges:=True

2) Use the Workbook_BeforeClose event in the ThisWorkbook module to cancel closing the workbook if blnCanClose has not been set to True:

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  If blnCanClose = False Then
    MsgBox "You can't close the workbook this way!", vbExclamation
    Cancel = True
  End If
End Sub

[MSingh]

Hi,

Thank you again for such prompt & precise replies.

Kind Regards
Mohamed