Roy asks how to prevent a workbook from being saved until the workbook is complete. Episode 1118 will show you the four lines of VBA needed to make this happen.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Roy from the UK.
Roy has a cell, he says “Look, I want to check this cell, if this cell isn't complete, I don't want to let them save the workbook.” OK, yeah, we can do that, come back here to the Developer tab, go to Visual Basic.
Now in the Project Explorer you want to go to this workbook, double-click and then left drop-down, choose Workbook, right drop-down, want to choose an event called BeforeSave.
Alright, and you’ll notice here that the BeforeSave gives us a variable called Cancel, alright.
So what we're going to do, we’re going to say If Not Worksheets( and then the worksheet is “Menu”).Range(“E3”) > let's say “ “, they have to put something there, more than a space.
Then Cancel = True, there you go, that'll do it, that will prevent them from saving.
Now, maybe we should tell them what's going on, Msgbox “Can not save until E3 is complete!” Alright, so we get rid of this little bit of code here, and come back to Excel.
Will do File, Save As, “Can not save until E3 is complete!” I just put something in there, and now File, Save As, and it works, there you go, check it out.
Alright Roy, thanks for sending that question in, and thanks to you for stopping by, we’ll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Roy from the UK.
Roy has a cell, he says “Look, I want to check this cell, if this cell isn't complete, I don't want to let them save the workbook.” OK, yeah, we can do that, come back here to the Developer tab, go to Visual Basic.
Now in the Project Explorer you want to go to this workbook, double-click and then left drop-down, choose Workbook, right drop-down, want to choose an event called BeforeSave.
Alright, and you’ll notice here that the BeforeSave gives us a variable called Cancel, alright.
So what we're going to do, we’re going to say If Not Worksheets( and then the worksheet is “Menu”).Range(“E3”) > let's say “ “, they have to put something there, more than a space.
Then Cancel = True, there you go, that'll do it, that will prevent them from saving.
Now, maybe we should tell them what's going on, Msgbox “Can not save until E3 is complete!” Alright, so we get rid of this little bit of code here, and come back to Excel.
Will do File, Save As, “Can not save until E3 is complete!” I just put something in there, and now File, Save As, and it works, there you go, check it out.
Alright Roy, thanks for sending that question in, and thanks to you for stopping by, we’ll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
