message box question

ndendrinos

Well-known Member
Joined
Jan 17, 2003
Messages
1,694
Hello to all,
Is it possible to incorporate in this code a message box (yes/no) that would execute the code if "yes" is chosen and not run the macros if "no" is chosen but still save the workbook ? ... Thank you


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ActiveSheet.Name <> "Template" Then Exit Sub
Application.ScreenUpdating = False
gatti
sofka
resetform
Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

ACommandLineKindaGuy

Active Member
Joined
May 11, 2002
Messages
304
You mean like this?

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
If ActiveSheet.Name <> "Template" Then Exit Sub 
Response = MsgBox("Do you want to run these goofy sub names before saving?,vbYesNo)
If Response = vbYes then
    Application.ScreenUpdating = False 
    gatti 
    sofka 
    resetform
    Application.ScreenUpdating = True 
End If

End Sub
 

ndendrinos

Well-known Member
Joined
Jan 17, 2003
Messages
1,694
Thank you for your reply ... I get a "complile error" highlighted line is:
Response = MsgBox("Do you want to run these goofy sub names

While waiting for help I made a user form instead and that works the way I want BUT : for some reason I cannot figure out I have to click twice on "no" for the file to save and the form to hide. here is the code for the form:
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
gatti
sofka
resetform
UserForm1.Hide
Application.ScreenUpdating = True
End Sub

Private Sub CommandButton2_Click()
UserForm1.Hide
ActiveWorkbook.Save

End Sub

I would rather learn using your option if I can get past the compile error and if by clicking on no I still get to save the file.
Regards,
Nick
 

ndendrinos

Well-known Member
Joined
Jan 17, 2003
Messages
1,694
Should also add that when I chaged to"modeless" it worked BUT the form starts completly blank and about a seconf later the two buttons and the text box appear ... not nice.... since it's the first time I use "modeless" is this the way it is supposed to be ? or did I do something wrong ? (out of curiosity)
Thanks
 

ndendrinos

Well-known Member
Joined
Jan 17, 2003
Messages
1,694
ACommandLineKindaGuy ... got it going ... a " was missing .

So now my questions on clicking twice (not double clickling) on "no" to get the desired result using my form AND the business of the "modeless" form still stand ...
I sure would appreciate an answer on those two
Thank you
 

ACommandLineKindaGuy

Active Member
Joined
May 11, 2002
Messages
304
Sorry for fouling you up with that missing "...

The problem on the Userbox may be that you're hiding the userbox before saving the file -- try saving before hiding.

Take a look at Help for info on modeless--it just lets you move around the spreadsheet while the userform is open.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,391
Messages
5,571,855
Members
412,421
Latest member
Rimo86
Top