Require specific cell entry before saving file

[Patrick Riley]

I want to require the user to enter his/her name in a specific cell (E59)
before the user can save the file.
I tried using Data Validation where I specified Text Length between 1 and
40, and left blank the check-box for "Ignore Blank". Nope. I never
programmed in VBA, so I hope there is a simple solution (I might be OK with
some simple VBA code; would need to know where to put the code).
---Pat
P.S. I posted this March 19th and again 20th 2008 and someone was kind
enough to respond, but Microsoft's link (in the auto-generated e-mail to me)
to the response did not work. I thank that individual for responding, and
ask that you please re-send your answer.

 

[Tom Hutchins]

Try this VBA event code (change the sheet name as appropriate):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'If there is nothing in Sheet1 cell E59...
If Len(ThisWorkbook.Sheets("Sheet1").Range("E59").Value) = 0 Then
'Notify the user and don't save the workbook.
MsgBox "You must enter your name in cell E59 on Sheet1", , "ERROR"
Cancel = True
End If
End Sub

Paste this code into the ThisWorkbook module of the workbook where this
validation should occur. From the Tools menu, select Macro >> Visual Basic
Editor. This will open the Visual Basic Editor (VBE) for Excel. From the View
menu in the VBE, select Project Explorer. It usually opens along the left
side of the screen. You should see some bold text like "VBAProject (Book1)",
where Book1 is the name of your workbook. Expand the indented list under it
and one of the items listed should say ThisWorkbook. Double-click on
ThisWorkbook and a new blank window should open to the right of the Project
Explorer window. Paste the code above in that window. Close the VBE and save
the file (you won't be able to unless there is something in E59).

If you are new to macros, david mcritchie has some instructions on his site
for navigating the vba editor and how to copy/paste macros into your project.

http://www.mvps.org/dmcritchie/excel/excel.htm

Hope this helps,

Hutch