Either Userform exit button Or workbook close button

nightcrawler23

nightcrawler23

Well-known Member
Joined
Sep 24, 2009
Messages
721
I am using a userform in which i have an exit button which removes some sheets and exits after saving the workbook.
I am also using the same thing as an workbook before close event but when i exit using button it also displays the befoeclose event message

see the code below

Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
resp = MsgBox("Do you want to remove the RAO Sheets?", 4)
If resp = 6 Then
    removeSheets
    Workbooks("Motion Analysis.xls").Close savechanges:=True
End If
End Sub


Code: 
Private Sub CmdExit_Click()
If (Sheets("sheet1").Cells(1, 3) = 1) Then
    ans = MsgBox("The Data Will be Erased" & vbCrLf & "Have you saved The Data", 4, "Exit Application")
    If ans = 6 Then
        removeSheets
        Workbooks("Motion Analysis.xls").Close savechanges:=True
        Else
        MsgBox ("Please Save Your Data")
    End If
    Else
    removeSheets
    Workbooks("Motion Analysis.xls").Close savechanges:=True
End If
End Sub

I want that if exit btn is used the beforeclose event is disabled
help!!!!!!!!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Greetings Nightcrawler,

Not tested, but in a copy of your workbook, try:

In a Standard Module:

Add a Public Boolean at the top of the module:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Public</SPAN> bolSkipExit <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN></FONT>

In ThisWorkbook Module:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> bolSkipExit <SPAN style="color:#00007F">Then</SPAN><br>        resp = MsgBox("Do you want to remove the RAO Sheets?", 4)<br>        <SPAN style="color:#00007F">If</SPAN> resp = 6 <SPAN style="color:#00007F">Then</SPAN><br>            removeSheets<br>            Workbooks("Motion Analysis.xls").Close savechanges:=<SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

In the Userform's Module:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CmdExit_Click()<br><br>bolSkipExit = <SPAN style="color:#00007F">True</SPAN><br><br><SPAN style="color:#00007F">If</SPAN> (Sheets("sheet1").Cells(1, 3) = 1) <SPAN style="color:#00007F">Then</SPAN><br>    ans = MsgBox("The Data Will be Erased" & vbCrLf & "Have you saved The Data", 4, "Exit Application")<br>    <SPAN style="color:#00007F">If</SPAN> ans = 6 <SPAN style="color:#00007F">Then</SPAN><br>        removeSheets<br>        Workbooks("Motion Analysis.xls").Close savechanges:=<SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">Else</SPAN><br>        MsgBox ("Please Save Your Data")<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>    removeSheets<br>    Workbooks("Motion Analysis.xls").Close savechanges:=<SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Mark
 
Upvote 0
Thanks...the code works fine and i got what i desired.Thanks again


Code: 
[COLOR=#00007f]Option[/COLOR] [COLOR=#00007f]Explicit[/COLOR]

[COLOR=#00007f]Public[/COLOR] bolSkipExit [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]Boolean[/COLOR]

can u provide a link having some data or tutorial on how to use the above code...
 
Upvote 0
nightcrawler23 said:
Thanks...the code works fine and i got what i desired.Thanks again


Code: 
[COLOR=#00007f][COLOR=#00007f]Option[/COLOR] [COLOR=#00007f]Explicit[/COLOR]
 
[COLOR=#00007f]Public[/COLOR] bolSkipExit [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]Boolean[/COLOR]
[/COLOR]


can u provide a link having some data or tutorial on how to use the above code...
Click to expand...


Hi again,

I am not sure what you are asking, as the "above code" is simply the variable declaration.

The vba help files explain variable visibility, but in short, we placed the declaration in a Standard Module to make it easily accessable from any other module and made the variable Public so that the value is 'visible' from any part of the project.

Does that help at all?

Mark
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Close workbook with variable
Replies
2
Views
561
Ingemar
I
B
  • Solved
Set expiry date 10 days from opening workbook
2
Replies
11
Views
625
bptaw
B
M
  • Question
Macro that will transfer selected sheets from selected .xlsm workbook to my active .xlsm workbook
Replies
1
Views
324
frabulator
frabulator
D
  • Question
Issue with EntireRow.Delete
2
Replies
11
Views
804
RoryA
RoryA
B
  • Question
Delete sheets on password expiry
Replies
3
Views
332
bptaw
B

Forum statistics

Threads
1,214,979
Messages
6,122,552
Members
449,088
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top