Preventing Save or Close Unless Cells are Filled

[LuluG]

I was looking for a solution to force users to fill in certain fields on a macros enabled excel, and today i've found this solution that worked as a charm, the only issue is that I as the autor need to close the file with the blank fields so it gets to the user ready to fill in, and this macros wont let me do that because the macros apply to everybody.

This is the macros (courtesy of Tom):

<code>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If WorksheetFunction.CountA( _
Worksheets("Sheet1").Range("A1,A2,A3,A5,A5, A6")) < 6 Then
MsgBox "Workbook will not be saved unless" & vbCrLf & _
"All required fields have been filled in!", , "Missing info"
Cancel = True
End If
End Sub</code>

Is there a way I can tell in the macros that if I am (my user) working on it I don't have to fill the required fields?

Please tell me if you need more details about my issue...

Thanks!

P.S. I'm not considering using data validation because I'm already using them for dropdown lists in some options of my form.

 

[Chris Macro]

You could add a test based on your Login name. However if someone has the name login name then it wouldn't work for your situation. You could also add a password input to let yourself step over the code:

Login Name Example:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeSave(<SPAN style="color:#00007F">ByVal</SPAN> SaveAsUI <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>, Cancel As <SPAN style="color:#00007F">Boolean</SPAN>)<br><br><SPAN style="color:#00007F">If</SPAN> Environ("UserName") <> "Chris" <SPAN style="color:#00007F">Then</SPAN><br>  <SPAN style="color:#00007F">If</SPAN> WorksheetFunction.CountA( _<br>  Worksheets("Sheet1").Range("A1,A2,A3,A5,A5, A6")) < 6 <SPAN style="color:#00007F">Then</SPAN><br>  MsgBox "Workbook will not be saved unless" & vbCrLf & _<br>  "All required fields have been filled in!", , "Missing info"<br>  Cancel = <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">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Password Example:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeSave(<SPAN style="color:#00007F">ByVal</SPAN> SaveAsUI <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>, Cancel As <SPAN style="color:#00007F">Boolean</SPAN>)<br><br><SPAN style="color:#00007F">If</SPAN> InputBox("Enter Admin Password") = "Password" <SPAN style="color:#00007F">Then</SPAN><br>  <SPAN style="color:#00007F">If</SPAN> WorksheetFunction.CountA( _<br>  Worksheets("Sheet1").Range("A1,A2,A3,A5,A5, A6")) < 6 <SPAN style="color:#00007F">Then</SPAN><br>  MsgBox "Workbook will not be saved unless" & vbCrLf & _<br>  "All required fields have been filled in!", , "Missing info"<br>  Cancel = <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">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[LuluG]

That worked!!! thank you very much Chris!!

I used the login name, since my user name is an ID number it is quite imposible that it will repeat somewhere else

 

[Chris Macro]

Nice! Glad it worked out for you