VBA Excel: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Make SaveWarning a public Boolean in a separate module (not a worksheet or ThisWorkbook module) then, within your email generation code, set SaveWarning to True, run you email generation, then set SaveWarning to False.
-Chuck

Public SaveWarning As Boolean
 
Public Function EmailGeneration()
    SaveWarning = True
    ' ...
    ' your email generation code
    ' ...
    SaveWarning = False
End Function

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:

Thanks for the replies!

EoDawg:
my master xls sheet, actually opens another xls sheet (which has the annoying save code in)

Chuck:
WIll a public setting like this work across different xls workbooks and their vba projects?

It will work as long as it is declared within the code you are running and it is set to True. If you are running code from one workbook that has the Boolean value set to True, it will apply while you are running code that opens other workbooks and generates email for them. If you open another workbook and run code in that workbook, you should first run code in that workbook (in a separate module) similar to the code in the snippet before running any other code (SetSaveWarning True) and after running the other code (SetSaveWarning False).
-Chuck

Public Function SetSaveWarning(ByVal Set As Boolean)
   SaveWarning = Set
End Function

                                              

1:
2:
3:

it dosnt like "set" comment?!

Set must be a reserved word. Try this.
-Chuck

Public Function SetSaveWarning(ByVal SetSavE As Boolean)
   SaveWarning = SetSavE
End Function
                                              

1:
2:
3:

Chuck, really sorry but i dont quite get it....
ive had a play and i cant get it to work.

MasterSheet (list of users and information plus button to set it off. Each user has a new templateSheet created for them and saved)

Public SaveWarning  as boolean - module1

Sub Run (kicks off the emails) - module2
SaveWarning  = true

TemplateSheet (I use this as i cant get VB code to generate VB code!)

'within the workbook
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        If SaveWarning = False Then
            Select Case ContinueYesNo.ContinueYesNo _
                        ("PROCESS NOT COMPLETED CORRECTLY!" & vbCrLf _
                        & vbCrLf _
                        & "Please note that you should not save this file yourself." & vbCrLf _
                        & vbCrLf _
                        & "As per the instructions, there are buttons found at the top of the page, which need to be pressed to complete this process correctly." & vbCrLf _
                        & vbCrLf _
                        & "NB. These buttons will automatically save this spreadsheet for you and send it back to us.")
                Case vbYes: ACEend: Cancel = False 'means the user does save it
                Case vbNo: ACEend: Cancel = True 'means the user doesnt save it
            End Select
        End If
       
End Sub

'within a new module
Public Function SetSaveWarning(ByVal SetSavE As Boolean)
   SaveWarning = SetSavE
End Function

Are these all in the same workbook?
-Chuck

no sorry,

TemplateSheet and MasterSheet  are sepeate xls workbooks all together.

OK, here is what you need to do.
-Chuck

' in any separate module in TemplateSheet
Public SaveWarning  as Boolean
 
Public Function SetSaveWarning(ByVal SetSave As Boolean)
   SaveWarning = SetSave
End Function
 
' in MasterSheet
Sub Run (kicks off the emails) 
    ' if MasterSheet is open and MasterSheet is the name of the workbook
    Application.Run "TemplateSheet.xls!SetSaveWarning", True
    ' ...
    ' existing code
    ' ...
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

Only way I can think of is to not store the code in the template but store it somewhere else in a text file ( give it whatever extension you like once you create it) then use that first before_save event to trigger some other vba code to manipulate the vbe editor and import the code from the secondary template.

Chip Pearson has a good section on writing code using code and it's not too hard once you get your head round it.
http://www.cpearson.com/excel/vbe.aspx
see
Adding A Procedure To A Module
&
Copy A Module From One Project To Another
& Especially
Creating An Event Procedure

I am not offering to write it for you as its 5:00 AM and I need sleep before I have to work in 2 hours hour so have a crack at it and if you get stuck sing out.

I only saw the first post as I had this open for hours while working on another post
Lots more info says my solution will be unnecessarily complicated & not eloquent for this problem as now defined.
Now I understand the reference to email that I couldn't make sense of.
You guys are good

I thought of a simpler solution using only code in your TemplateSheet workbook.
-Chuck

' in any separate module in TemplateSheet
Public SaveWarning  as Boolean
 
Public Function SetSaveWarning(ByVal SetSave As Boolean)
   SaveWarning = SetSave
End Function
 
' in the ThisWorkbook code in TemplateSheet
Private Sub Workbook_Open()
    SaveWarning = True
End Sub
 
 
' in the button code that saves the workbook in TemplateSheet 
Private Sub MyButton_Click()
    ' replace MyButton will be the actual name of your button
    ' 
    ' change SaveWarning 
    SaveWarning = False
    ' ...
    ' existing code to save and email the workbook
    ' ...
    ' change SaveWarning back
    SaveWarning = False
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:

chuck, nearly there!

it all works fine and the code now doesnt popup errors when im generating the xls sheets.
however when i open one of those xls sheets, it doesnt pop-up the error when i save it as savewarning has been set to true in the workbook open function.
However if i change the below if statement to true,then it would happen when i generate the emails?


also, where/why/how is this function used in the master sheet?
Public Function SetSaveWarning(ByVal SetSave As Boolean)
   SaveWarning = SetSave
End Function

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
If SaveWarning = False Then
   msgbox "your an idiot"
end if

                                              

1:
2:
3:
4:
5:

Sorry, I was in a bit of a hurry and left in some code that did not belong and got the True/False thing turned around. Please use this code and let me know how it works.
-Chuck

' in any separate module in TemplateSheet
Public SaveWarning  as Boolean
 
' in the ThisWorkbook code in TemplateSheet
Private Sub Workbook_Open()
    SaveWarning = False
End Sub
 
' in the button code that saves the workbook in TemplateSheet 
Private Sub MyButton_Click()
    ' replace MyButton will be the actual name of your button
    ' 
    ' change SaveWarning 
    SaveWarning = True
    ' ...
    ' existing code to save and email the workbook
    ' ...
    ' change SaveWarning back
    SaveWarning = False
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:

ok
what should the savewarning be here:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If SaveWarning = True Then
  msgbox "idiot your not meant to save this"
End If

End Sub

Should it not be what you originally posted?
-Chuck

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        If SaveWarning = False Then
            Select Case ContinueYesNo.ContinueYesNo _
                        ("PROCESS NOT COMPLETED CORRECTLY!" & vbCrLf _
                        & vbCrLf _
                        & "Please note that you should not save this file yourself." & vbCrLf _
                        & vbCrLf _
                        & "As per the instructions, there are buttons found at the top of the page, which need to be pressed to complete this process correctly." & vbCrLf _
                        & vbCrLf _
                        & "NB. These buttons will automatically save this spreadsheet for you and send it back to us.")
                Case vbYes: ACEend: Cancel = False 'means the user does save it
                Case vbNo: ACEend: Cancel = True 'means the user doesnt save it
            End Select
        End If
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

Current code below.
The error STILL happens when im generating the emails, so thats not good.
However it is working correctly when i open them.

-----Master XLS used to open, create and save new versions of the template file -------
 
Module 1
Public SaveWarning As Boolean
 
RunModule
Sub RunEmailGenerator
SaveWarning = True
'code to open template sheet
'code to modify template sheet
'code to save temaplte sheet
SaveWarning = false
End sub
 
-----Template XLS used by the master sheet -------
under "this workbook"
 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
If SaveWarning = False Then
  Msgbox "Idiot, you shouldnt save this"
End If
End Sub
 
Private Sub Workbook_Open()
    SaveWarning = False
End Sub
 
module1:
Public SaveWarning As Boolean
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:

hey
im getting runtime error 1004

the macro 'ACE Spreadsheet Template V2.4.xls!SetSaveWarning' cannot be found

but it is there and that xls file IS open?

code: Application.Run "ACE Spreadsheet Template V2.4.xls!SetSaveWarning", True

do i need to specify the project/module name that the fuction sits within maybe?

got it!
the spaces " " in the file name were causing it issues?!? so i removed them to make ACESpreadsheetTemplateV2.4 and all works fine
cheers for the awesome help :)

awesome solution! thanks so much :)

You are welcome. You could also surround the file name with quotation marks like those shown in the snippet.
Good luck on your project.
-Chuck

Application.Run """ACE Spreadsheet Template V2.4.xls""!SetSaveWarning", True

                                              

1:

There are spaces in the file name so need to add single quotes '  '  round the file name
eg
Application.Run "'ACE Spreadsheet Template V2.4.xls'!SetSaveWarning", True

PS
Does your email generator add text to the body or just subject?

I gotta be quicker ......

Nice solution Chuck

sirplus

"Does your email generator add text to the body or just subject?"
it does indeed, plus attachments

Nice -Is it on this site somewhere?