Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 21st 08, 04:08 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 34
Default Require specific cell entry before saving file

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.


  #2   Report Post  
Old March 21st 08, 04:46 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,069
Default Require specific cell entry before saving file

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").Val ue) = 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

"Patrick Riley" wrote:

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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Require input in a cell before saving file Patrick Riley Excel Discussion (Misc queries) 21 March 26th 08 02:47 PM
Require specific cell entry before saving file Patrick Riley Excel Discussion (Misc queries) 2 March 20th 08 03:50 PM
2 Questions, Saving a file from a specific cell value, and Searchi Peter Excel Discussion (Misc queries) 2 April 10th 06 01:54 PM
Have cell require input before saving. couriced Excel Discussion (Misc queries) 1 October 20th 05 04:36 PM
Saving a file using a specific name in a cell SU Excel Worksheet Functions 4 February 25th 05 12:30 AM


All times are GMT +1. The time now is 01:17 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017