Disable X on workbook opened by macro

[mwc0914]

My module opens a xlsx workbook that can be updated.
I want to disable the Close X on the opened workbook so the user must use a userform to close & save the opened workbook.
How do I disable the X on the opened workbook from my original module?

 

[Logit]

http://vbadud.blogspot.com/2009/05/disable-close-button-using-excel-vba.html

You can also search for something similar to "VBA Excel Disable X On Worksheet" to see other websites.

 

[Jaafar Tribak]

A simpler alternative to using the rather complex windows API approach is to remotely hook the close event of the newly opened workbook.

Something like this :

Code in the userform module.

Option Explicit

Private WithEvents wb As Workbook

[B][COLOR=#008000]'Button to open the new workbook[/COLOR][/B]
Private Sub CommandButton1_Click()
    Me.Tag = ""
    Set wb = Workbooks.Add
End Sub

[B][COLOR=#008000]'Button to close the opened workbook[/COLOR][/B]
Private Sub CommandButton2_Click()
    Me.Tag = "dummy"
    wb.Close
End Sub

Private Sub wb_BeforeClose(Cancel As Boolean)
    Cancel = Me.Tag = ""
End Sub

 

[Logit]

Jaafar Tribak

If the UserForm is set to Non-Modal and with or without your macro ... the user can still use the main window X / Close Button to exit the workbook.

If the UserForm is set to Modal and with or without your macro ... the user cannot use the main window X / Close Button to exit the workbook.

The macro doesn't seem to have any effect .... ???

 

[Jaafar Tribak]

Jaafar Tribak

If the UserForm is set to Non-Modal and with or without your macro ... the user can still use the main window X / Close Button to exit the workbook.

If the UserForm is set to Modal and with or without your macro ... the user cannot use the main window X / Close Button to exit the workbook.

The macro doesn't seem to have any effect .... ???

Hi,

If the UserForm is set to Non-Modal the code works well for preventing exiting the programmatically opened workbook (not the calling workbook)