Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Required Fields, an hilarious error!
This is the first time I have ever been stumped, was desperate, and laughed
until I had tears in my eyes! I'm using Excel 2003. In the spreadsheet, there are 10 cells, and the user must select 1 or more options by entering an X in the desired cell(s). If they SAVE the spreadsheet, at least one of the 10 cells must have data. I'm thinking, no problem, that's simple. I'm fairly new to Excel VBA, and coded the following. It works, but it won't let ME save my code without having an X in at least one of the 10 cells! Is there a way to save the code only? Your help would be GREATLY appreciated! For brevity sake, I only listed conditions for 2 of the cells. ======= Private Sub Workbook_BeforeSave(ByVal CheckCells As Boolean, Cancel As Boolean) Dim TheCells As Range Dim SomethingWasChecked As String If CheckCells = False Then SomethingWasChecked = "N" If Me.Worksheets("sheet1").Range("A19").Value < "" Then SomethingWasChecked = "Y" GoTo StopChecking End If If Me.Worksheets("sheet1").Range("A25").Value < "" Then SomethingWasChecked = "Y" GoTo StopChecking End If StopChecking: If SomethingWasChecked < "Y" Then Cancel = True MsgBox "Please checkmark at least one option or close file without saving!", vbCritical, "Missing Data!" End If End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Required Fields, an hilarious error!
Modifyu the code so that it checks if the active sheet name is Sheet1. then
change sheets to shet2 and save. Yo can export the VBA code to a BAS file and import the code using import. See www.cpearson.com on methods to import and export macros. It is under copying macros from one workbook to a 2nd workbook. "laavista" wrote: This is the first time I have ever been stumped, was desperate, and laughed until I had tears in my eyes! I'm using Excel 2003. In the spreadsheet, there are 10 cells, and the user must select 1 or more options by entering an X in the desired cell(s). If they SAVE the spreadsheet, at least one of the 10 cells must have data. I'm thinking, no problem, that's simple. I'm fairly new to Excel VBA, and coded the following. It works, but it won't let ME save my code without having an X in at least one of the 10 cells! Is there a way to save the code only? Your help would be GREATLY appreciated! For brevity sake, I only listed conditions for 2 of the cells. ======= Private Sub Workbook_BeforeSave(ByVal CheckCells As Boolean, Cancel As Boolean) Dim TheCells As Range Dim SomethingWasChecked As String If CheckCells = False Then SomethingWasChecked = "N" If Me.Worksheets("sheet1").Range("A19").Value < "" Then SomethingWasChecked = "Y" GoTo StopChecking End If If Me.Worksheets("sheet1").Range("A25").Value < "" Then SomethingWasChecked = "Y" GoTo StopChecking End If StopChecking: If SomethingWasChecked < "Y" Then Cancel = True MsgBox "Please checkmark at least one option or close file without saving!", vbCritical, "Missing Data!" End If End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Required Fields, an hilarious error!
This code fires when you're saving--but only if macros are enabled and if events
are enabled. So you could open that workbook with macros disabled, make your changes and save and close the workbook. Or you could turn off events first, save, then turn on events. Open the VBE Hit ctrl-g to see the immediate window type this and hit enter: application.enableevents = false Then back to excel and save your workbook. Then back to the VBE's immediate window and turn it back on again: application.enableevents = true (Remember that any user can do the same thing, too!) ===== Another option would be to look at the some indicator (application.username???) and process accordingly. laavista wrote: This is the first time I have ever been stumped, was desperate, and laughed until I had tears in my eyes! I'm using Excel 2003. In the spreadsheet, there are 10 cells, and the user must select 1 or more options by entering an X in the desired cell(s). If they SAVE the spreadsheet, at least one of the 10 cells must have data. I'm thinking, no problem, that's simple. I'm fairly new to Excel VBA, and coded the following. It works, but it won't let ME save my code without having an X in at least one of the 10 cells! Is there a way to save the code only? Your help would be GREATLY appreciated! For brevity sake, I only listed conditions for 2 of the cells. ======= Private Sub Workbook_BeforeSave(ByVal CheckCells As Boolean, Cancel As Boolean) Dim TheCells As Range Dim SomethingWasChecked As String If CheckCells = False Then SomethingWasChecked = "N" If Me.Worksheets("sheet1").Range("A19").Value < "" Then SomethingWasChecked = "Y" GoTo StopChecking End If If Me.Worksheets("sheet1").Range("A25").Value < "" Then SomethingWasChecked = "Y" GoTo StopChecking End If StopChecking: If SomethingWasChecked < "Y" Then Cancel = True MsgBox "Please checkmark at least one option or close file without saving!", vbCritical, "Missing Data!" End If End If End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Required Fields, an hilarious error!
"Another option would be to look at the some indicator (application.username???) and process accordingly." Yes, I've done this in the past, though *Application.username* might not be good if on a company network if your username (the same username that's to be found in the -Options- dialogue box -General -tab reached via the dropdown menus *Tools|Options...*) is common to other user's User Names there. I worked for a largish company where they installed MS Office and the Username was always the company name. Easy to solve, make sure your UserName is likely to be unique. Anyone can change the MSOffice username easily in this dialogue box. In the code you could add: If Application.UserName="YourUniqueName" then 'at the top of the sub 'your present code here ' ' End if 'at the bottom of the sub In the corporate environment I used *Environ("Username")* which is the same as the log on to the network/machine, which is something very likely to be unique to each user. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=134027 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Required Fields, an hilarious error!
Fundamental error:
If Application.UserName="YourUniqueName" then should read: If Application.UserName<"YourUniqueName" then -- p45cal "p45cal" wrote: "Another option would be to look at the some indicator (application.username???) and process accordingly." Yes, I've done this in the past, though *Application.username* might not be good if on a company network if your username (the same username that's to be found in the -Options- dialogue box -General -tab reached via the dropdown menus *Tools|Options...*) is common to other user's User Names there. I worked for a largish company where they installed MS Office and the Username was always the company name. Easy to solve, make sure your UserName is likely to be unique. Anyone can change the MSOffice username easily in this dialogue box. In the code you could add: If Application.UserName="YourUniqueName" then 'at the top of the sub 'your present code here ' ' End if 'at the bottom of the sub In the corporate environment I used *Environ("Username")* which is the same as the log on to the network/machine, which is something very likely to be unique to each user. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=134027 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Required Fields, an hilarious error!
P45cal: Thanks for your input!
"p45cal" wrote: Fundamental error: If Application.UserName="YourUniqueName" then should read: If Application.UserName<"YourUniqueName" then -- p45cal "p45cal" wrote: "Another option would be to look at the some indicator (application.username???) and process accordingly." Yes, I've done this in the past, though *Application.username* might not be good if on a company network if your username (the same username that's to be found in the -Options- dialogue box -General -tab reached via the dropdown menus *Tools|Options...*) is common to other user's User Names there. I worked for a largish company where they installed MS Office and the Username was always the company name. Easy to solve, make sure your UserName is likely to be unique. Anyone can change the MSOffice username easily in this dialogue box. In the code you could add: If Application.UserName="YourUniqueName" then 'at the top of the sub 'your present code here ' ' End if 'at the bottom of the sub In the corporate environment I used *Environ("Username")* which is the same as the log on to the network/machine, which is something very likely to be unique to each user. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=134027 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Required Fields, an hilarious error!
When I was developing a workbook, I'd change my username to:
Dave Petersonxxx (via tools|Options|General Tab (in xl2003 menus) Then I'd check for that username when I needed to save without the checks. But when I was testing my code, I'd change my username back to normal. Then I could see if the code broke. if lcase(application.username) = lcase("dave petersonxxx") then 'do nothing else 'do the work end if It worked nicely when I wanted to be a user sometimes and a developer other times. p45cal wrote: "Another option would be to look at the some indicator (application.username???) and process accordingly." Yes, I've done this in the past, though *Application.username* might not be good if on a company network if your username (the same username that's to be found in the -Options- dialogue box -General -tab reached via the dropdown menus *Tools|Options...*) is common to other user's User Names there. I worked for a largish company where they installed MS Office and the Username was always the company name. Easy to solve, make sure your UserName is likely to be unique. Anyone can change the MSOffice username easily in this dialogue box. In the code you could add: If Application.UserName="YourUniqueName" then 'at the top of the sub 'your present code here ' ' End if 'at the bottom of the sub In the corporate environment I used *Environ("Username")* which is the same as the log on to the network/machine, which is something very likely to be unique to each user. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=134027 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Required Fields, an hilarious error!
Dave, thanks again! This is awesome. Thanks so much for taking the time to
help me with this. I really appreciate it! "Dave Peterson" wrote: When I was developing a workbook, I'd change my username to: Dave Petersonxxx (via tools|Options|General Tab (in xl2003 menus) Then I'd check for that username when I needed to save without the checks. But when I was testing my code, I'd change my username back to normal. Then I could see if the code broke. if lcase(application.username) = lcase("dave petersonxxx") then 'do nothing else 'do the work end if It worked nicely when I wanted to be a user sometimes and a developer other times. p45cal wrote: "Another option would be to look at the some indicator (application.username???) and process accordingly." Yes, I've done this in the past, though *Application.username* might not be good if on a company network if your username (the same username that's to be found in the -Options- dialogue box -General -tab reached via the dropdown menus *Tools|Options...*) is common to other user's User Names there. I worked for a largish company where they installed MS Office and the Username was always the company name. Easy to solve, make sure your UserName is likely to be unique. Anyone can change the MSOffice username easily in this dialogue box. In the code you could add: If Application.UserName="YourUniqueName" then 'at the top of the sub 'your present code here ' ' End if 'at the bottom of the sub In the corporate environment I used *Environ("Username")* which is the same as the log on to the network/machine, which is something very likely to be unique to each user. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=134027 -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Required Fields, an hilarious error!
This is great, Dave, and is an easy fix. Thanks!!
"Dave Peterson" wrote: This code fires when you're saving--but only if macros are enabled and if events are enabled. So you could open that workbook with macros disabled, make your changes and save and close the workbook. Or you could turn off events first, save, then turn on events. Open the VBE Hit ctrl-g to see the immediate window type this and hit enter: application.enableevents = false Then back to excel and save your workbook. Then back to the VBE's immediate window and turn it back on again: application.enableevents = true (Remember that any user can do the same thing, too!) ===== Another option would be to look at the some indicator (application.username???) and process accordingly. laavista wrote: This is the first time I have ever been stumped, was desperate, and laughed until I had tears in my eyes! I'm using Excel 2003. In the spreadsheet, there are 10 cells, and the user must select 1 or more options by entering an X in the desired cell(s). If they SAVE the spreadsheet, at least one of the 10 cells must have data. I'm thinking, no problem, that's simple. I'm fairly new to Excel VBA, and coded the following. It works, but it won't let ME save my code without having an X in at least one of the 10 cells! Is there a way to save the code only? Your help would be GREATLY appreciated! For brevity sake, I only listed conditions for 2 of the cells. ======= Private Sub Workbook_BeforeSave(ByVal CheckCells As Boolean, Cancel As Boolean) Dim TheCells As Range Dim SomethingWasChecked As String If CheckCells = False Then SomethingWasChecked = "N" If Me.Worksheets("sheet1").Range("A19").Value < "" Then SomethingWasChecked = "Y" GoTo StopChecking End If If Me.Worksheets("sheet1").Range("A25").Value < "" Then SomethingWasChecked = "Y" GoTo StopChecking End If StopChecking: If SomethingWasChecked < "Y" Then Cancel = True MsgBox "Please checkmark at least one option or close file without saving!", vbCritical, "Missing Data!" End If End If End Sub -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Required Fields, an hilarious error!
You have answers to your main question, but I would like to address your posted code.
First, why did you change the SaveAsUI parameter from the BeforeSave event header to CheckCells? You should not be modifying the header in any way as it is provided by VB for you... leave it as SaveAsUI and, if you need to, use that name within your code. Just so you know, that parameter will be automatically set to True if the SaveAs dialog box will appear and False if it won't appear. The SaveAs dialog box will appear only when the file is being saved for the first time or when the user chooses Save As from the File menu item... I don't see how your code would need to care about that. Second, assuming your range of 10 cells is A19:A28 (just a guess as you didn't tell us), you can perform the function you are trying to do with this much shorter code... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ' Count how many cells in the range have something in them If WorksheetFunction.CountA(Worksheets("Sheet1").Rang e("A19:A28")) = 0 Then Cancel = True MsgBox "Please checkmark at least one option or close file without saving!", vbCritical, "Missing Data!" End If End Sub -- Rick (MVP - Excel) "laavista" wrote in message ... This is the first time I have ever been stumped, was desperate, and laughed until I had tears in my eyes! I'm using Excel 2003. In the spreadsheet, there are 10 cells, and the user must select 1 or more options by entering an X in the desired cell(s). If they SAVE the spreadsheet, at least one of the 10 cells must have data. I'm thinking, no problem, that's simple. I'm fairly new to Excel VBA, and coded the following. It works, but it won't let ME save my code without having an X in at least one of the 10 cells! Is there a way to save the code only? Your help would be GREATLY appreciated! For brevity sake, I only listed conditions for 2 of the cells. ======= Private Sub Workbook_BeforeSave(ByVal CheckCells As Boolean, Cancel As Boolean) Dim TheCells As Range Dim SomethingWasChecked As String If CheckCells = False Then SomethingWasChecked = "N" If Me.Worksheets("sheet1").Range("A19").Value < "" Then SomethingWasChecked = "Y" GoTo StopChecking End If If Me.Worksheets("sheet1").Range("A25").Value < "" Then SomethingWasChecked = "Y" GoTo StopChecking End If StopChecking: If SomethingWasChecked < "Y" Then Cancel = True MsgBox "Please checkmark at least one option or close file without saving!", vbCritical, "Missing Data!" End If End If End Sub |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Required Fields, an hilarious error!
Rick, thank you for your input. I will change my code and use the SaveAsUI
parameter. I like the change you made with the consecutive range, but the cells I'm checking are not contiguous. I really appreciate you taking the time to provide advice. "Rick Rothstein" wrote: You have answers to your main question, but I would like to address your posted code. First, why did you change the SaveAsUI parameter from the BeforeSave event header to CheckCells? You should not be modifying the header in any way as it is provided by VB for you... leave it as SaveAsUI and, if you need to, use that name within your code. Just so you know, that parameter will be automatically set to True if the SaveAs dialog box will appear and False if it won't appear. The SaveAs dialog box will appear only when the file is being saved for the first time or when the user chooses Save As from the File menu item... I don't see how your code would need to care about that. Second, assuming your range of 10 cells is A19:A28 (just a guess as you didn't tell us), you can perform the function you are trying to do with this much shorter code... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ' Count how many cells in the range have something in them If WorksheetFunction.CountA(Worksheets("Sheet1").Rang e("A19:A28")) = 0 Then Cancel = True MsgBox "Please checkmark at least one option or close file without saving!", vbCritical, "Missing Data!" End If End Sub -- Rick (MVP - Excel) "laavista" wrote in message ... This is the first time I have ever been stumped, was desperate, and laughed until I had tears in my eyes! I'm using Excel 2003. In the spreadsheet, there are 10 cells, and the user must select 1 or more options by entering an X in the desired cell(s). If they SAVE the spreadsheet, at least one of the 10 cells must have data. I'm thinking, no problem, that's simple. I'm fairly new to Excel VBA, and coded the following. It works, but it won't let ME save my code without having an X in at least one of the 10 cells! Is there a way to save the code only? Your help would be GREATLY appreciated! For brevity sake, I only listed conditions for 2 of the cells. ======= Private Sub Workbook_BeforeSave(ByVal CheckCells As Boolean, Cancel As Boolean) Dim TheCells As Range Dim SomethingWasChecked As String If CheckCells = False Then SomethingWasChecked = "N" If Me.Worksheets("sheet1").Range("A19").Value < "" Then SomethingWasChecked = "Y" GoTo StopChecking End If If Me.Worksheets("sheet1").Range("A25").Value < "" Then SomethingWasChecked = "Y" GoTo StopChecking End If StopChecking: If SomethingWasChecked < "Y" Then Cancel = True MsgBox "Please checkmark at least one option or close file without saving!", vbCritical, "Missing Data!" End If End If End Sub |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Required Fields, an hilarious error!
The CountA worksheet function does not require contiguous cells (I used contiguous cells because you didn't provide the range you were interested in covering, so I had to make a guess), here is the code modified to handle 10 non-contiguous cells (by the way, they do not have to be in the same column as I have shown either)...
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ' Count how many cells in the range have something in them If WorksheetFunction.CountA(Worksheets("Sheet1").Rang e("A19,A25,A31,A37,A43,A49,A55,A61,A67,A73")) = 0 Then Cancel = True MsgBox "Please checkmark at least one option or close file without saving!", vbCritical, "Missing Data!" End If End Sub By the way, this portion of this thread is a perfect example of why it is almost always a bad idea to simplify your questions when you post them to newsgroups... we cannot offer other solutions to setups you don't tell us about. -- Rick (MVP - Excel) "laavista" wrote in message ... Rick, thank you for your input. I will change my code and use the SaveAsUI parameter. I like the change you made with the consecutive range, but the cells I'm checking are not contiguous. I really appreciate you taking the time to provide advice. "Rick Rothstein" wrote: You have answers to your main question, but I would like to address your posted code. First, why did you change the SaveAsUI parameter from the BeforeSave event header to CheckCells? You should not be modifying the header in any way as it is provided by VB for you... leave it as SaveAsUI and, if you need to, use that name within your code. Just so you know, that parameter will be automatically set to True if the SaveAs dialog box will appear and False if it won't appear. The SaveAs dialog box will appear only when the file is being saved for the first time or when the user chooses Save As from the File menu item... I don't see how your code would need to care about that. Second, assuming your range of 10 cells is A19:A28 (just a guess as you didn't tell us), you can perform the function you are trying to do with this much shorter code... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ' Count how many cells in the range have something in them If WorksheetFunction.CountA(Worksheets("Sheet1").Rang e("A19:A28")) = 0 Then Cancel = True MsgBox "Please checkmark at least one option or close file without saving!", vbCritical, "Missing Data!" End If End Sub -- Rick (MVP - Excel) "laavista" wrote in message ... This is the first time I have ever been stumped, was desperate, and laughed until I had tears in my eyes! I'm using Excel 2003. In the spreadsheet, there are 10 cells, and the user must select 1 or more options by entering an X in the desired cell(s). If they SAVE the spreadsheet, at least one of the 10 cells must have data. I'm thinking, no problem, that's simple. I'm fairly new to Excel VBA, and coded the following. It works, but it won't let ME save my code without having an X in at least one of the 10 cells! Is there a way to save the code only? Your help would be GREATLY appreciated! For brevity sake, I only listed conditions for 2 of the cells. ======= Private Sub Workbook_BeforeSave(ByVal CheckCells As Boolean, Cancel As Boolean) Dim TheCells As Range Dim SomethingWasChecked As String If CheckCells = False Then SomethingWasChecked = "N" If Me.Worksheets("sheet1").Range("A19").Value < "" Then SomethingWasChecked = "Y" GoTo StopChecking End If If Me.Worksheets("sheet1").Range("A25").Value < "" Then SomethingWasChecked = "Y" GoTo StopChecking End If StopChecking: If SomethingWasChecked < "Y" Then Cancel = True MsgBox "Please checkmark at least one option or close file without saving!", vbCritical, "Missing Data!" End If End If End Sub |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Required Fields, an hilarious error!
Rick, thanks again for this information. This simplies the code
significantly. Lesson learned on my part. I'll be more specific with my next question. Appreciate your help! "Rick Rothstein" wrote: The CountA worksheet function does not require contiguous cells (I used contiguous cells because you didn't provide the range you were interested in covering, so I had to make a guess), here is the code modified to handle 10 non-contiguous cells (by the way, they do not have to be in the same column as I have shown either)... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ' Count how many cells in the range have something in them If WorksheetFunction.CountA(Worksheets("Sheet1").Rang e("A19,A25,A31,A37,A43,A49,A55,A61,A67,A73")) = 0 Then Cancel = True MsgBox "Please checkmark at least one option or close file without saving!", vbCritical, "Missing Data!" End If End Sub By the way, this portion of this thread is a perfect example of why it is almost always a bad idea to simplify your questions when you post them to newsgroups... we cannot offer other solutions to setups you don't tell us about. -- Rick (MVP - Excel) "laavista" wrote in message ... Rick, thank you for your input. I will change my code and use the SaveAsUI parameter. I like the change you made with the consecutive range, but the cells I'm checking are not contiguous. I really appreciate you taking the time to provide advice. "Rick Rothstein" wrote: You have answers to your main question, but I would like to address your posted code. First, why did you change the SaveAsUI parameter from the BeforeSave event header to CheckCells? You should not be modifying the header in any way as it is provided by VB for you... leave it as SaveAsUI and, if you need to, use that name within your code. Just so you know, that parameter will be automatically set to True if the SaveAs dialog box will appear and False if it won't appear. The SaveAs dialog box will appear only when the file is being saved for the first time or when the user chooses Save As from the File menu item... I don't see how your code would need to care about that. Second, assuming your range of 10 cells is A19:A28 (just a guess as you didn't tell us), you can perform the function you are trying to do with this much shorter code... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ' Count how many cells in the range have something in them If WorksheetFunction.CountA(Worksheets("Sheet1").Rang e("A19:A28")) = 0 Then Cancel = True MsgBox "Please checkmark at least one option or close file without saving!", vbCritical, "Missing Data!" End If End Sub -- Rick (MVP - Excel) "laavista" wrote in message ... This is the first time I have ever been stumped, was desperate, and laughed until I had tears in my eyes! I'm using Excel 2003. In the spreadsheet, there are 10 cells, and the user must select 1 or more options by entering an X in the desired cell(s). If they SAVE the spreadsheet, at least one of the 10 cells must have data. I'm thinking, no problem, that's simple. I'm fairly new to Excel VBA, and coded the following. It works, but it won't let ME save my code without having an X in at least one of the 10 cells! Is there a way to save the code only? Your help would be GREATLY appreciated! For brevity sake, I only listed conditions for 2 of the cells. ======= Private Sub Workbook_BeforeSave(ByVal CheckCells As Boolean, Cancel As Boolean) Dim TheCells As Range Dim SomethingWasChecked As String If CheckCells = False Then SomethingWasChecked = "N" If Me.Worksheets("sheet1").Range("A19").Value < "" Then SomethingWasChecked = "Y" GoTo StopChecking End If If Me.Worksheets("sheet1").Range("A25").Value < "" Then SomethingWasChecked = "Y" GoTo StopChecking End If StopChecking: If SomethingWasChecked < "Y" Then Cancel = True MsgBox "Please checkmark at least one option or close file without saving!", vbCritical, "Missing Data!" End If End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to have fields to be required | Excel Worksheet Functions | |||
WorkbookBeforeSave Event - check required fields before saving | Excel Programming | |||
My first post and 11 Hilarious videos | Excel Discussion (Misc queries) | |||
Required fields | Excel Programming | |||
Required fields | Excel Programming |