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
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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
> ~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" <[email protected]> wrote in message
> news:[email protected]...
>>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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> ~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" <[email protected]> wrote in message
>> news:[email protected]...
>>>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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
> > news:[email protected]...
> > >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> > >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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > >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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> >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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks