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

~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

"~C" wrote in message
...
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, 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

"Paul B" wrote in message
...
~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

"~C" wrote in message
...
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,

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

~C

"Paul B" wrote:

~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

"~C" wrote in message
...
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

LOL! Sorry, I couldn't help it

Peo


"~C" wrote in message
...
Paul,

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

~C

"Paul B" wrote:

~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

"~C" wrote in message
...
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

ooops!

My mistake, put it in the wrong place.

Biff

"Biff" wrote in message
...
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

"Paul B" wrote in message
...
~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

"~C" wrote in message
...
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

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

"~C" wrote in message
...
Paul,

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

~C

"Paul B" wrote:

~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

"~C" wrote in message
...
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

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.


~C wrote:

Paul,

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

~C

"Paul B" wrote:

~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

"~C" wrote in message
...
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




--

Dave Peterson

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" wrote in message
...
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.


~C wrote:

Paul,

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

~C

"Paul B" wrote:

~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

"~C" wrote in message
...
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




--

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!")



Biff wrote:

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" wrote in message
...
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.


~C wrote:

Paul,

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

~C

"Paul B" wrote:

~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

"~C" wrote in message
...
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




--

Dave Peterson

--

Dave Peterson

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

Biff:

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

"Invalid Outside Procedure"

What do I do now?



"Biff" wrote:

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

"~C" wrote in message
...
Paul,

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

~C

"Paul B" wrote:

~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

"~C" wrote in message
...
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

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

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



~C wrote:

Biff:

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

"Invalid Outside Procedure"

What do I do now?

"Biff" wrote:

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

"~C" wrote in message
...
Paul,

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

~C

"Paul B" wrote:

~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

"~C" wrote in message
...
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







--

Dave Peterson