BeforeClose Event ref UserForm

[Mathew]

Good afteroon all,

I am hoping someone maybe able to help me out here, I seem to be going round in circles.

I have a BeforeClose (workbook) event which prevents the user from closing the file by clicking on the X,
(in any worksheet), and takes the user to a Userform which has an Exit button on it.

Now this seems to work fine, however I also wish, that when the user is returned to the userform to exit, that another
specific button on the userform now becomes deactivated/hidden/ or not visible.

Any help here would be most appreciated.

Cheers,
Mathew

 

[Dave Peterson]

I think I'd use a couple of public variables.

I put this in a general module:

Option Explicit
Public BeforeCloseFlag As Boolean
Public okToClose As Boolean
Sub testme()
BeforeCloseFlag = False
UserForm1.Show
End Sub

And in my Thisworkbook module:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
BeforeCloseFlag = True
UserForm1.Show

If okToClose = True Then
'do nothing, continue
Else
Cancel = True
End If

End Sub

And I had a 3 buttons on the userform--ok, cancel, and commandbutton1:

Option Explicit
Private Sub btnCancel_Click()
okToClose = False
Unload Me
End Sub
Private Sub btnOk_Click()
okToClose = True
Unload Me
End Sub
Private Sub UserForm_Initialize()
If BeforeCloseFlag = True Then
'Me.CommandButton1.Visible = False
Me.CommandButton1.Enabled = False
Else
'do nothing
End If
End Sub

seemed to work ok in light testing.

 

[Mathew]

Cheers Dave,
Interesting.
Cheers, Mathew
I think I'd use a couple of public variables.

I put this in a general module:

Option Explicit
Public BeforeCloseFlag As Boolean
Public okToClose As Boolean
Sub testme()
BeforeCloseFlag = False
UserForm1.Show
End Sub

And in my Thisworkbook module:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
BeforeCloseFlag = True
UserForm1.Show

If okToClose = True Then
'do nothing, continue
Else
Cancel = True
End If

End Sub

And I had a 3 buttons on the userform--ok, cancel, and commandbutton1:

Option Explicit
Private Sub btnCancel_Click()
okToClose = False
Unload Me
End Sub
Private Sub btnOk_Click()
okToClose = True
Unload Me
End Sub
Private Sub UserForm_Initialize()
If BeforeCloseFlag = True Then
'Me.CommandButton1.Visible = False
Me.CommandButton1.Enabled = False
Else
'do nothing
End If
End Sub

seemed to work ok in light testing.