Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorkbookBeforeSave Event - check required fields before saving
I am not very good with code, I am trying to Save a workbook, but prior to
saving I want it to check certain fields to ensure they are filled in. Below is the code I am using, but doesn't seem to work. Any Suggestions?? Linda Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, _ ByVal SaveAsUI As Boolean, Cancel As Boolean) a = MsgBox("Do you really want to save the workbook?", vbYesNo) If a = vbYes Then SaveAsUI = True If TextBox8 = "" Then MsgBox "Please enter Submitting Agency Name" Exit Sub End If If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Exit Sub End If If TextBox4 = "" Then MsgBox "Please enter Task Coordinator Name" Exit Sub End If If TextBox5 = "" Then MsgBox "Please enter Task Coordinator Telephone Number" Exit Sub End If If TextBox6 = "" Then MsgBox "Please enter Task Coordinator Email Address" Exit Sub End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorkbookBeforeSave Event - check required fields before saving
On Mar 25, 12:23*pm, LRay67 wrote:
I am not very good with code, I am trying to Save a workbook, but prior to saving I want it to check certain fields to ensure they are filled in. *Below is the code I am using, but doesn't seem to work. *Any Suggestions?? Linda Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, _ * * * * ByVal SaveAsUI As Boolean, Cancel As Boolean) * a = MsgBox("Do you really want to save the workbook?", vbYesNo) * * If a = vbYes Then SaveAsUI = True *If TextBox8 = "" Then * * MsgBox "Please enter Submitting Agency Name" * * Exit Sub * * End If If TextBox1 = "" Then * * MsgBox "Please enter Accounting Unit Code (7 Digits)" * * Exit Sub * * End If *If TextBox4 = "" Then * * MsgBox "Please enter Task Coordinator Name" * * Exit Sub * * End If *If TextBox5 = "" Then * * MsgBox "Please enter Task Coordinator Telephone Number" * * Exit Sub * * End If *If TextBox6 = "" Then * * MsgBox "Please enter Task Coordinator Email Address" * * Exit Sub * * End If *End Sub Try this .... Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, _ ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim bnSave as Boolean a = MsgBox("Do you really want to save the workbook?", vbYesNo) If a = vbYes Then bnSave = True If TextBox8 = "" Then MsgBox "Please enter Submitting Agency Name" Exit Sub End If If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Exit Sub End If If TextBox4 = "" Then MsgBox "Please enter Task Coordinator Name" Exit Sub End If If TextBox5 = "" Then MsgBox "Please enter Task Coordinator Telephone Number" Exit Sub End If If TextBox6 = "" Then MsgBox "Please enter Task Coordinator Email Address" Exit Sub End If If bnSave = True then ActiveWorkbook.Save End if End Sub Does that help?? Chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorkbookBeforeSave Event - check required fields before saving
Chris, I am not even getting the 1st message box "Do you really want to save
the workbook?" Nothing is working....Is there a particular place in the code sheet that I am to place the Private Sub App_WorkbookBeforeSave statement? Help -Thanks "cht13er" wrote: On Mar 25, 12:23 pm, LRay67 wrote: I am not very good with code, I am trying to Save a workbook, but prior to saving I want it to check certain fields to ensure they are filled in. Below is the code I am using, but doesn't seem to work. Any Suggestions?? Linda Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, _ ByVal SaveAsUI As Boolean, Cancel As Boolean) a = MsgBox("Do you really want to save the workbook?", vbYesNo) If a = vbYes Then SaveAsUI = True If TextBox8 = "" Then MsgBox "Please enter Submitting Agency Name" Exit Sub End If If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Exit Sub End If If TextBox4 = "" Then MsgBox "Please enter Task Coordinator Name" Exit Sub End If If TextBox5 = "" Then MsgBox "Please enter Task Coordinator Telephone Number" Exit Sub End If If TextBox6 = "" Then MsgBox "Please enter Task Coordinator Email Address" Exit Sub End If End Sub Try this .... Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, _ ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim bnSave as Boolean a = MsgBox("Do you really want to save the workbook?", vbYesNo) If a = vbYes Then bnSave = True If TextBox8 = "" Then MsgBox "Please enter Submitting Agency Name" Exit Sub End If If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Exit Sub End If If TextBox4 = "" Then MsgBox "Please enter Task Coordinator Name" Exit Sub End If If TextBox5 = "" Then MsgBox "Please enter Task Coordinator Telephone Number" Exit Sub End If If TextBox6 = "" Then MsgBox "Please enter Task Coordinator Email Address" Exit Sub End If If bnSave = True then ActiveWorkbook.Save End if End Sub Does that help?? Chris |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorkbookBeforeSave Event - check required fields before saving
On Mar 25, 1:11*pm, LRay67 wrote:
Chris, I am not even getting the 1st message box "Do you really want to save the workbook?" Nothing is working....Is there a particular place in the code sheet that I am to place the Private Sub App_WorkbookBeforeSave statement? * Help *-Thanks "cht13er" wrote: On Mar 25, 12:23 pm, LRay67 wrote: I am not very good with code, I am trying to Save a workbook, but prior to saving I want it to check certain fields to ensure they are filled in. *Below is the code I am using, but doesn't seem to work. *Any Suggestions?? Linda Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, _ * * * * ByVal SaveAsUI As Boolean, Cancel As Boolean) * a = MsgBox("Do you really want to save the workbook?", vbYesNo) * * If a = vbYes Then SaveAsUI = True *If TextBox8 = "" Then * * MsgBox "Please enter Submitting Agency Name" * * Exit Sub * * End If If TextBox1 = "" Then * * MsgBox "Please enter Accounting Unit Code (7 Digits)" * * Exit Sub * * End If *If TextBox4 = "" Then * * MsgBox "Please enter Task Coordinator Name" * * Exit Sub * * End If *If TextBox5 = "" Then * * MsgBox "Please enter Task Coordinator Telephone Number" * * Exit Sub * * End If *If TextBox6 = "" Then * * MsgBox "Please enter Task Coordinator Email Address" * * Exit Sub * * End If *End Sub Try this .... Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, _ * * * * ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim bnSave as Boolean * a = MsgBox("Do you really want to save the workbook?", vbYesNo) * * If a = vbYes Then bnSave = True *If TextBox8 = "" Then * * MsgBox "Please enter Submitting Agency Name" * * *Exit Sub End If *If TextBox1 = "" Then * * *MsgBox "Please enter Accounting Unit Code (7 Digits)" * * *Exit Sub End If If TextBox4 = "" Then * * *MsgBox "Please enter Task Coordinator Name" * * *Exit Sub End If If TextBox5 = "" Then * * *MsgBox "Please enter Task Coordinator Telephone Number" * * *Exit Sub End If If TextBox6 = "" Then * * *MsgBox "Please enter Task Coordinator Email Address" * * *Exit Sub End If If bnSave = True then * * *ActiveWorkbook.Save End if End Sub Does that help?? Chris- Hide quoted text - - Show quoted text - Aha! Place this in the "ThisWorkbook" area: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim bnSave As Boolean a = MsgBox("Do you really want to save the workbook?", vbYesNo) If a = vbYes Then bnSave = True If TextBox8 = "" Then MsgBox "Please enter Submitting Agency Name" Exit Sub End If If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Exit Sub End If If TextBox4 = "" Then MsgBox "Please enter Task Coordinator Name" Exit Sub End If If TextBox5 = "" Then MsgBox "Please enter Task Coordinator Telephone Number" Exit Sub End If If TextBox6 = "" Then MsgBox "Please enter Task Coordinator Email Address" Exit Sub End If If bnSave = True Then SaveAsUI = True End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet.Unprotect within WorkbookBeforeSave event fails if Save | Excel Programming | |||
how to have fields to be required | Excel Worksheet Functions | |||
Worksheet.Unprotect within WorkbookBeforeSave event fails if Save initiated by VB | Excel Programming | |||
Required fields | Excel Programming | |||
Required fields | Excel Discussion (Misc queries) |