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
VBA limit file exit option to my command button
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA limit file exit option to my command button
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:
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:
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):
2) Use the Workbook_BeforeClose event in the ThisWorkbook module to cancel closing the workbook if blnCanClose has not been set to True:
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
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
Code: Select all
blnCanClose = True
ActiveWorkbook.Close SaveChanges:=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
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: VBA limit file exit option to my command button
Hi,
Thank you again for such prompt & precise replies.
Kind Regards
Mohamed
Thank you again for such prompt & precise replies.
Kind Regards
Mohamed