Can I make certain cells required?

[Guest]

I am creating a form and certain fields are required. Can I force the user
to input some value before they can save?

Thanks!

~C

 

[Paul B]

~C, here is one way, put in this workbook code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
'user must enter data into all the defined cells before save
Dim test_rng As Range
Dim ret_str As String
Dim cell As Range
Set test_rng = ActiveSheet.Range("A1:A2,H4") '**change range here****
For Each cell In test_rng
If cell.Value = "" Then
If ret_str = "" Then
ret_str = cell.Address
Else
ret_str = ret_str & " and " & cell.Address
End If
End If
Next
If ret_str <> "" Then
MsgBox "There is information missing in cell(s): " & ret_str & Chr(10) _
& Chr(10) & "You must fill in the cell(s) before you can save" _
, , "Missing Information"
Cancel = True
Else
End If
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Biff]

Paul, this isn't working for me.

I changed the range to A1:A2

Put the code in THIS WORKBOOK.

I'm able to save without entering anything in A1:A2.

Biff

 

[Guest]

Paul,

This puts me in a Catch 22, as I am then not able to save it myself! What
to do?

~C

 

[Peo Sjoblom]

LOL! Sorry, I couldn't help it

Peo

Paul,

This puts me in a Catch 22, as I am then not able to save it myself! What
to do?

~C

 

[Biff]

ooops!

My mistake, put it in the wrong place.

Biff

 

[Biff]

Goto the VBE, Open the Immediate window, type this line then hit ENTER

Application.EnableEvents=FALSE

Exit the VBE

Save your file

Go back into the VBE, open the Immediate window, type this line then hit
ENTER:

Application.EnableEvents=TRUE

Biff

 

[Dave Peterson]

You have a couple of choices...

#1. Change the code to look at some kind of indicator to determine if the Save
should be allowed--maybe a cell, maybe your username????

#2. Turn event handling off
Save the file
Turn event handling back on

Inside the VBE, hit ctrl-g to see the immediate window

Type this and hit enter:
application.enableevents = false

Save your workbook

Then back to the VBE's immediate window and
application.enableevents = true


======
Note that any user can disable events and save your workbook, too.

And opening the workbook with macros disabled would be just as effective.

 

[Biff]

The user can always just close Excel without saving, too. So, there is no
"bulletproof" way to force an entry in a cell.

Threaten them with bodily harm if all else fails!

Biff

 

[Dave Peterson]

I like to use adjacent cells that give immediate feedback.

In nice big bold red letters:

=if(a3<>"","","<-- Please type something into this cell!")

 

[vezerid]

Paul,

this is a BeforeSave, not BeforeCode code you posted. (almost) Exactly
the same code can be pasted inside

Private Sub Workbook_BeforeClose(Cancel As Boolean)
....
End Sub

I say (almost) b/c I believe you could replace ActiveSheet with a
specific sheet. This way, before closing the workbook the macro will
always check the required cells regardless of which workbook is active.
I am pasting my recommended code below, in which the OP can replace

Sheets("Sheet1")

with whatever is the name of the form sheet inside the quotes.

HTH
Kostis Vezerides

 

[Guest]

Biff:

I tried this, but I get a compile error that says:

"Invalid Outside Procedure"

What do I do now?

 

[Dave Peterson]

It's just that one line:
application.enableevents = false

(I'm betting you typed more in the immediate window.)