How can I make users NOT exit an excel form without certain cells being filled in?
Thanks.
How can I make users NOT exit an excel form without certain cells being filled in?
Thanks.
Do you mean exit from a UserForm (with the OK or cancel buttons) or exit and close an entire workbook?
If they are exiting by pushing a button, put some code in the click event that checks for appropriate entries before hiding or unloading the userform.
Sorry, exit and close an entire workbook
Here's an example using the workbook beforeclose event
<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br><SPAN style="color:#00007F">If</SPAN> Len(Cells(1, 1) = 0) <SPAN style="color:#00007F">Then</SPAN><br>MsgBox "Can<SPAN style="color:#007F00">'t Close without completing" _<br>& " cell A1"</SPAN><br>Cancel = <SPAN style="color:#00007F">True</SPAN><br>Else: ActiveWorkbook.<SPAN style="color:#00007F">Close</SPAN> <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
If A1 is not completed, the workbook close event will be cancelled
If A1 has a value, the workbook will close & any changes will be saved.
Hope this helps
Will
Thanks. Do I use this as a formula or a macro? Where should I enter this?
Your help is very much appreciated.
What about just a work-sheet within a Workbook?
And I am using VB editor to enetr the code - is this correct?
Again, many thanks.
Sorry Homer... had to do some work
My code should be placed in the This_Workbook code module in the VBE
Access this by right-clicking the XL Icon to the left of the word File on the top menu ok.
Paste my code in this module.
You can alter the code to make it specific to cells in, say sheet1 of the workbook if you need to....
Will
Thanks. Tried that but nothing seems to happen do i need to compile or run something...?
Thanks, again.
Don’t have an account yet? Register yourself now and be a part of our community!