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).
---Pat

 

[Nigel]

Place the following code in the "ThisWorkbook" code sheet........

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If Len(Trim(Sheet1.Range("E59"))) = 0 Then
MsgBox "Enter name in cell E59 before save", vbCritical + vbOKOnly
Cancel = True
End If
End Sub

 

[Patrick Riley]

Thank you, Nigel. I placed the code in the "ThisWorkbook" code sheet, but it
did not work (I still could save the file with cell E59 being blank. I then
substituted the name of the worksheet (Main) for "Sheet1", but that did not
work, either.

 

[Nigel]

If the name of the 'Tab' on the worksheet is Main, then use

If Len(Trim(Worksheets.("Main").Range("E59"))) = 0 Then

It does work, the only one area is if the user press the application X, an
Excel will ask to save bypassing this code, you could put some code in the
Before_Close event that re-directs the test.

--

Regards,
Nigel
(e-mail address removed)