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(quot;A1:A2,H4quot;) '**change range here****
For Each cell In test_rng
If cell.Value = quot;quot; Then
If ret_str = quot;quot; Then
ret_str = cell.Address
Else
ret_str = ret_str amp; quot; and quot; amp; cell.Address
End If
End If
Next
If ret_str lt;gt; quot;quot; Then
MsgBox quot;There is information missing in cell(s): quot; amp; ret_str amp; Chr(10) _
amp; Chr(10) amp; quot;You must fill in the cell(s) before you can savequot; _
, , quot;Missing Informationquot;
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 amp; 2003
quot;~Cquot; gt; wrote in message
...
gt;I am creating a form and certain fields are required. Can I force the user
gt; to input some value before they can save?
gt;
gt; Thanks!
gt;
gt; ~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
quot;Paul Bquot; gt; wrote in message
...
gt; ~C, here is one way, put in this workbook code
gt;
gt; Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
gt; Boolean)
gt; 'user must enter data into all the defined cells before save
gt; Dim test_rng As Range
gt; Dim ret_str As String
gt; Dim cell As Range
gt; Set test_rng = ActiveSheet.Range(quot;A1:A2,H4quot;) '**change range here****
gt; For Each cell In test_rng
gt; If cell.Value = quot;quot; Then
gt; If ret_str = quot;quot; Then
gt; ret_str = cell.Address
gt; Else
gt; ret_str = ret_str amp; quot; and quot; amp; cell.Address
gt; End If
gt; End If
gt; Next
gt; If ret_str lt;gt; quot;quot; Then
gt; MsgBox quot;There is information missing in cell(s): quot; amp; ret_str amp; Chr(10)
gt; _
gt; amp; Chr(10) amp; quot;You must fill in the cell(s) before you can savequot; _
gt; , , quot;Missing Informationquot;
gt; Cancel = True
gt; Else
gt; End If
gt; End Sub
gt;
gt; --
gt; Paul B
gt; Always backup your data before trying something new
gt; Please post any response to the newsgroups so others can benefit from it
gt; Feedback on answers is always appreciated!
gt; Using Excel 2002 amp; 2003
gt;
gt; quot;~Cquot; gt; wrote in message
gt; ...
gt;gt;I am creating a form and certain fields are required. Can I force the
gt;gt;user
gt;gt; to input some value before they can save?
gt;gt;
gt;gt; Thanks!
gt;gt;
gt;gt; ~C
gt;
gt;
Paul,
This puts me in a Catch 22, as I am then not able to save it myself! What
to do?
~C
quot;Paul Bquot; wrote:
gt; ~C, here is one way, put in this workbook code
gt;
gt; Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
gt; Boolean)
gt; 'user must enter data into all the defined cells before save
gt; Dim test_rng As Range
gt; Dim ret_str As String
gt; Dim cell As Range
gt; Set test_rng = ActiveSheet.Range(quot;A1:A2,H4quot;) '**change range here****
gt; For Each cell In test_rng
gt; If cell.Value = quot;quot; Then
gt; If ret_str = quot;quot; Then
gt; ret_str = cell.Address
gt; Else
gt; ret_str = ret_str amp; quot; and quot; amp; cell.Address
gt; End If
gt; End If
gt; Next
gt; If ret_str lt;gt; quot;quot; Then
gt; MsgBox quot;There is information missing in cell(s): quot; amp; ret_str amp; Chr(10) _
gt; amp; Chr(10) amp; quot;You must fill in the cell(s) before you can savequot; _
gt; , , quot;Missing Informationquot;
gt; Cancel = True
gt; Else
gt; End If
gt; End Sub
gt;
gt; --
gt; Paul B
gt; Always backup your data before trying something new
gt; Please post any response to the newsgroups so others can benefit from it
gt; Feedback on answers is always appreciated!
gt; Using Excel 2002 amp; 2003
gt;
gt; quot;~Cquot; gt; wrote in message
gt; ...
gt; gt;I am creating a form and certain fields are required. Can I force the user
gt; gt; to input some value before they can save?
gt; gt;
gt; gt; Thanks!
gt; gt;
gt; gt; ~C
gt;
gt;
gt;
LOL! Sorry, I couldn't help it
Peoquot;~Cquot; gt; wrote in message
...
gt; Paul,
gt;
gt; This puts me in a Catch 22, as I am then not able to save it myself! What
gt; to do?
gt;
gt; ~C
gt;
gt; quot;Paul Bquot; wrote:
gt;
gt;gt; ~C, here is one way, put in this workbook code
gt;gt;
gt;gt; Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
gt;gt; Boolean)
gt;gt; 'user must enter data into all the defined cells before save
gt;gt; Dim test_rng As Range
gt;gt; Dim ret_str As String
gt;gt; Dim cell As Range
gt;gt; Set test_rng = ActiveSheet.Range(quot;A1:A2,H4quot;) '**change range here****
gt;gt; For Each cell In test_rng
gt;gt; If cell.Value = quot;quot; Then
gt;gt; If ret_str = quot;quot; Then
gt;gt; ret_str = cell.Address
gt;gt; Else
gt;gt; ret_str = ret_str amp; quot; and quot; amp; cell.Address
gt;gt; End If
gt;gt; End If
gt;gt; Next
gt;gt; If ret_str lt;gt; quot;quot; Then
gt;gt; MsgBox quot;There is information missing in cell(s): quot; amp; ret_str amp;
gt;gt; Chr(10) _
gt;gt; amp; Chr(10) amp; quot;You must fill in the cell(s) before you can savequot; _
gt;gt; , , quot;Missing Informationquot;
gt;gt; Cancel = True
gt;gt; Else
gt;gt; End If
gt;gt; End Sub
gt;gt;
gt;gt; --
gt;gt; Paul B
gt;gt; Always backup your data before trying something new
gt;gt; Please post any response to the newsgroups so others can benefit from it
gt;gt; Feedback on answers is always appreciated!
gt;gt; Using Excel 2002 amp; 2003
gt;gt;
gt;gt; quot;~Cquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I am creating a form and certain fields are required. Can I force the
gt;gt; gt;user
gt;gt; gt; to input some value before they can save?
gt;gt; gt;
gt;gt; gt; Thanks!
gt;gt; gt;
gt;gt; gt; ~C
gt;gt;
gt;gt;
gt;gt;
ooops!
My mistake, put it in the wrong place.
Biff
quot;Biffquot; gt; wrote in message
...
gt; Paul, this isn't working for me.
gt;
gt; I changed the range to A1:A2
gt;
gt; Put the code in THIS WORKBOOK.
gt;
gt; I'm able to save without entering anything in A1:A2.
gt;
gt; Biff
gt;
gt; quot;Paul Bquot; gt; wrote in message
gt; ...
gt;gt; ~C, here is one way, put in this workbook code
gt;gt;
gt;gt; Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
gt;gt; Boolean)
gt;gt; 'user must enter data into all the defined cells before save
gt;gt; Dim test_rng As Range
gt;gt; Dim ret_str As String
gt;gt; Dim cell As Range
gt;gt; Set test_rng = ActiveSheet.Range(quot;A1:A2,H4quot;) '**change range here****
gt;gt; For Each cell In test_rng
gt;gt; If cell.Value = quot;quot; Then
gt;gt; If ret_str = quot;quot; Then
gt;gt; ret_str = cell.Address
gt;gt; Else
gt;gt; ret_str = ret_str amp; quot; and quot; amp; cell.Address
gt;gt; End If
gt;gt; End If
gt;gt; Next
gt;gt; If ret_str lt;gt; quot;quot; Then
gt;gt; MsgBox quot;There is information missing in cell(s): quot; amp; ret_str amp; Chr(10)
gt;gt; _
gt;gt; amp; Chr(10) amp; quot;You must fill in the cell(s) before you can savequot; _
gt;gt; , , quot;Missing Informationquot;
gt;gt; Cancel = True
gt;gt; Else
gt;gt; End If
gt;gt; End Sub
gt;gt;
gt;gt; --
gt;gt; Paul B
gt;gt; Always backup your data before trying something new
gt;gt; Please post any response to the newsgroups so others can benefit from it
gt;gt; Feedback on answers is always appreciated!
gt;gt; Using Excel 2002 amp; 2003
gt;gt;
gt;gt; quot;~Cquot; gt; wrote in message
gt;gt; ...
gt;gt;gt;I am creating a form and certain fields are required. Can I force the
gt;gt;gt;user
gt;gt;gt; to input some value before they can save?
gt;gt;gt;
gt;gt;gt; Thanks!
gt;gt;gt;
gt;gt;gt; ~C
gt;gt;
gt;gt;
gt;
gt;
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
quot;~Cquot; gt; wrote in message
...
gt; Paul,
gt;
gt; This puts me in a Catch 22, as I am then not able to save it myself! What
gt; to do?
gt;
gt; ~C
gt;
gt; quot;Paul Bquot; wrote:
gt;
gt;gt; ~C, here is one way, put in this workbook code
gt;gt;
gt;gt; Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
gt;gt; Boolean)
gt;gt; 'user must enter data into all the defined cells before save
gt;gt; Dim test_rng As Range
gt;gt; Dim ret_str As String
gt;gt; Dim cell As Range
gt;gt; Set test_rng = ActiveSheet.Range(quot;A1:A2,H4quot;) '**change range here****
gt;gt; For Each cell In test_rng
gt;gt; If cell.Value = quot;quot; Then
gt;gt; If ret_str = quot;quot; Then
gt;gt; ret_str = cell.Address
gt;gt; Else
gt;gt; ret_str = ret_str amp; quot; and quot; amp; cell.Address
gt;gt; End If
gt;gt; End If
gt;gt; Next
gt;gt; If ret_str lt;gt; quot;quot; Then
gt;gt; MsgBox quot;There is information missing in cell(s): quot; amp; ret_str amp;
gt;gt; Chr(10) _
gt;gt; amp; Chr(10) amp; quot;You must fill in the cell(s) before you can savequot; _
gt;gt; , , quot;Missing Informationquot;
gt;gt; Cancel = True
gt;gt; Else
gt;gt; End If
gt;gt; End Sub
gt;gt;
gt;gt; --
gt;gt; Paul B
gt;gt; Always backup your data before trying something new
gt;gt; Please post any response to the newsgroups so others can benefit from it
gt;gt; Feedback on answers is always appreciated!
gt;gt; Using Excel 2002 amp; 2003
gt;gt;
gt;gt; quot;~Cquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I am creating a form and certain fields are required. Can I force the
gt;gt; gt;user
gt;gt; gt; to input some value before they can save?
gt;gt; gt;
gt;gt; gt; Thanks!
gt;gt; gt;
gt;gt; gt; ~C
gt;gt;
gt;gt;
gt;gt;
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:
gt;
gt; Paul,
gt;
gt; This puts me in a Catch 22, as I am then not able to save it myself! What
gt; to do?
gt;
gt; ~C
gt;
gt; quot;Paul Bquot; wrote:
gt;
gt; gt; ~C, here is one way, put in this workbook code
gt; gt;
gt; gt; Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
gt; gt; Boolean)
gt; gt; 'user must enter data into all the defined cells before save
gt; gt; Dim test_rng As Range
gt; gt; Dim ret_str As String
gt; gt; Dim cell As Range
gt; gt; Set test_rng = ActiveSheet.Range(quot;A1:A2,H4quot;) '**change range here****
gt; gt; For Each cell In test_rng
gt; gt; If cell.Value = quot;quot; Then
gt; gt; If ret_str = quot;quot; Then
gt; gt; ret_str = cell.Address
gt; gt; Else
gt; gt; ret_str = ret_str amp; quot; and quot; amp; cell.Address
gt; gt; End If
gt; gt; End If
gt; gt; Next
gt; gt; If ret_str lt;gt; quot;quot; Then
gt; gt; MsgBox quot;There is information missing in cell(s): quot; amp; ret_str amp; Chr(10) _
gt; gt; amp; Chr(10) amp; quot;You must fill in the cell(s) before you can savequot; _
gt; gt; , , quot;Missing Informationquot;
gt; gt; Cancel = True
gt; gt; Else
gt; gt; End If
gt; gt; End Sub
gt; gt;
gt; gt; --
gt; gt; Paul B
gt; gt; Always backup your data before trying something new
gt; gt; Please post any response to the newsgroups so others can benefit from it
gt; gt; Feedback on answers is always appreciated!
gt; gt; Using Excel 2002 amp; 2003
gt; gt;
gt; gt; quot;~Cquot; gt; wrote in message
gt; gt; ...
gt; gt; gt;I am creating a form and certain fields are required. Can I force the user
gt; gt; gt; to input some value before they can save?
gt; gt; gt;
gt; gt; gt; Thanks!
gt; gt; gt;
gt; gt; gt; ~C
gt; gt;
gt; gt;
gt; gt;
--
Dave Peterson
The user can always just close Excel without saving, too. So, there is no
quot;bulletproofquot; way to force an entry in a cell.
Threaten them with bodily harm if all else fails!
Biff
quot;Dave Petersonquot; gt; wrote in message
...
gt; You have a couple of choices...
gt;
gt; #1. Change the code to look at some kind of indicator to determine if the
gt; Save
gt; should be allowed--maybe a cell, maybe your username????
gt;
gt; #2. Turn event handling off
gt; Save the file
gt; Turn event handling back on
gt;
gt; Inside the VBE, hit ctrl-g to see the immediate window
gt;
gt; Type this and hit enter:
gt; application.enableevents = false
gt;
gt; Save your workbook
gt;
gt; Then back to the VBE's immediate window and
gt; application.enableevents = true
gt;
gt;
gt; ======
gt; Note that any user can disable events and save your workbook, too.
gt;
gt; And opening the workbook with macros disabled would be just as effective.
gt;
gt;
gt; ~C wrote:
gt;gt;
gt;gt; Paul,
gt;gt;
gt;gt; This puts me in a Catch 22, as I am then not able to save it myself!
gt;gt; What
gt;gt; to do?
gt;gt;
gt;gt; ~C
gt;gt;
gt;gt; quot;Paul Bquot; wrote:
gt;gt;
gt;gt; gt; ~C, here is one way, put in this workbook code
gt;gt; gt;
gt;gt; gt; Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
gt;gt; gt; Boolean)
gt;gt; gt; 'user must enter data into all the defined cells before save
gt;gt; gt; Dim test_rng As Range
gt;gt; gt; Dim ret_str As String
gt;gt; gt; Dim cell As Range
gt;gt; gt; Set test_rng = ActiveSheet.Range(quot;A1:A2,H4quot;) '**change range here****
gt;gt; gt; For Each cell In test_rng
gt;gt; gt; If cell.Value = quot;quot; Then
gt;gt; gt; If ret_str = quot;quot; Then
gt;gt; gt; ret_str = cell.Address
gt;gt; gt; Else
gt;gt; gt; ret_str = ret_str amp; quot; and quot; amp; cell.Address
gt;gt; gt; End If
gt;gt; gt; End If
gt;gt; gt; Next
gt;gt; gt; If ret_str lt;gt; quot;quot; Then
gt;gt; gt; MsgBox quot;There is information missing in cell(s): quot; amp; ret_str amp;
gt;gt; gt; Chr(10) _
gt;gt; gt; amp; Chr(10) amp; quot;You must fill in the cell(s) before you can savequot; _
gt;gt; gt; , , quot;Missing Informationquot;
gt;gt; gt; Cancel = True
gt;gt; gt; Else
gt;gt; gt; End If
gt;gt; gt; End Sub
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt; Paul B
gt;gt; gt; Always backup your data before trying something new
gt;gt; gt; Please post any response to the newsgroups so others can benefit from
gt;gt; gt; it
gt;gt; gt; Feedback on answers is always appreciated!
gt;gt; gt; Using Excel 2002 amp; 2003
gt;gt; gt;
gt;gt; gt; quot;~Cquot; gt; wrote in message
gt;gt; gt; ...
gt;gt; gt; gt;I am creating a form and certain fields are required. Can I force the
gt;gt; gt; gt;user
gt;gt; gt; gt; to input some value before they can save?
gt;gt; gt; gt;
gt;gt; gt; gt; Thanks!
gt;gt; gt; gt;
gt;gt; gt; gt; ~C
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
I like to use adjacent cells that give immediate feedback.
In nice big bold red letters:
=if(a3lt;gt;quot;quot;,quot;quot;,quot;lt;-- Please type something into this cell!quot;)
Biff wrote:
gt;
gt; The user can always just close Excel without saving, too. So, there is no
gt; quot;bulletproofquot; way to force an entry in a cell.
gt;
gt; Threaten them with bodily harm if all else fails!
gt;
gt; Biff
gt;
gt; quot;Dave Petersonquot; gt; wrote in message
gt; ...
gt; gt; You have a couple of choices...
gt; gt;
gt; gt; #1. Change the code to look at some kind of indicator to determine if the
gt; gt; Save
gt; gt; should be allowed--maybe a cell, maybe your username????
gt; gt;
gt; gt; #2. Turn event handling off
gt; gt; Save the file
gt; gt; Turn event handling back on
gt; gt;
gt; gt; Inside the VBE, hit ctrl-g to see the immediate window
gt; gt;
gt; gt; Type this and hit enter:
gt; gt; application.enableevents = false
gt; gt;
gt; gt; Save your workbook
gt; gt;
gt; gt; Then back to the VBE's immediate window and
gt; gt; application.enableevents = true
gt; gt;
gt; gt;
gt; gt; ======
gt; gt; Note that any user can disable events and save your workbook, too.
gt; gt;
gt; gt; And opening the workbook with macros disabled would be just as effective.
gt; gt;
gt; gt;
gt; gt; ~C wrote:
gt; gt;gt;
gt; gt;gt; Paul,
gt; gt;gt;
gt; gt;gt; This puts me in a Catch 22, as I am then not able to save it myself!
gt; gt;gt; What
gt; gt;gt; to do?
gt; gt;gt;
gt; gt;gt; ~C
gt; gt;gt;
gt; gt;gt; quot;Paul Bquot; wrote:
gt; gt;gt;
gt; gt;gt; gt; ~C, here is one way, put in this workbook code
gt; gt;gt; gt;
gt; gt;gt; gt; Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
gt; gt;gt; gt; Boolean)
gt; gt;gt; gt; 'user must enter data into all the defined cells before save
gt; gt;gt; gt; Dim test_rng As Range
gt; gt;gt; gt; Dim ret_str As String
gt; gt;gt; gt; Dim cell As Range
gt; gt;gt; gt; Set test_rng = ActiveSheet.Range(quot;A1:A2,H4quot;) '**change range here****
gt; gt;gt; gt; For Each cell In test_rng
gt; gt;gt; gt; If cell.Value = quot;quot; Then
gt; gt;gt; gt; If ret_str = quot;quot; Then
gt; gt;gt; gt; ret_str = cell.Address
gt; gt;gt; gt; Else
gt; gt;gt; gt; ret_str = ret_str amp; quot; and quot; amp; cell.Address
gt; gt;gt; gt; End If
gt; gt;gt; gt; End If
gt; gt;gt; gt; Next
gt; gt;gt; gt; If ret_str lt;gt; quot;quot; Then
gt; gt;gt; gt; MsgBox quot;There is information missing in cell(s): quot; amp; ret_str amp;
gt; gt;gt; gt; Chr(10) _
gt; gt;gt; gt; amp; Chr(10) amp; quot;You must fill in the cell(s) before you can savequot; _
gt; gt;gt; gt; , , quot;Missing Informationquot;
gt; gt;gt; gt; Cancel = True
gt; gt;gt; gt; Else
gt; gt;gt; gt; End If
gt; gt;gt; gt; End Sub
gt; gt;gt; gt;
gt; gt;gt; gt; --
gt; gt;gt; gt; Paul B
gt; gt;gt; gt; Always backup your data before trying something new
gt; gt;gt; gt; Please post any response to the newsgroups so others can benefit from
gt; gt;gt; gt; it
gt; gt;gt; gt; Feedback on answers is always appreciated!
gt; gt;gt; gt; Using Excel 2002 amp; 2003
gt; gt;gt; gt;
gt; gt;gt; gt; quot;~Cquot; gt; wrote in message
gt; gt;gt; gt; ...
gt; gt;gt; gt; gt;I am creating a form and certain fields are required. Can I force the
gt; gt;gt; gt; gt;user
gt; gt;gt; gt; gt; to input some value before they can save?
gt; gt;gt; gt; gt;
gt; gt;gt; gt; gt; Thanks!
gt; gt;gt; gt; gt;
gt; gt;gt; gt; gt; ~C
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
--
Dave Peterson
留言列表
