+ Reply to Thread
Results 1 to 13 of 13

Can I make certain cells required?

  1. #1
    ~C
    Guest

    Can I make certain cells required?

    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

  2. #2
    Paul B
    Guest

    Re: Can I make certain cells required?

    ~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" <C@discussions.microsoft.com> wrote in message
    news:FE4011EF-817A-495D-A626-8D70D57086EA@microsoft.com...
    >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




  3. #3
    Biff
    Guest

    Re: Can I make certain cells required?

    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" <to_much_spam_to_list@nospam.com> wrote in message
    news:Ow3D5iVbGHA.488@TK2MSFTNGP04.phx.gbl...
    > ~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" <C@discussions.microsoft.com> wrote in message
    > news:FE4011EF-817A-495D-A626-8D70D57086EA@microsoft.com...
    >>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

    >
    >




  4. #4
    ~C
    Guest

    Re: Can I make certain cells required?

    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" <C@discussions.microsoft.com> wrote in message
    > news:FE4011EF-817A-495D-A626-8D70D57086EA@microsoft.com...
    > >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

    >
    >
    >


  5. #5
    Peo Sjoblom
    Guest

    Re: Can I make certain cells required?

    LOL! Sorry, I couldn't help it

    Peo


    "~C" <C@discussions.microsoft.com> wrote in message
    news:4BB1845C-F801-4F09-8381-28169262EFD9@microsoft.com...
    > 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" <C@discussions.microsoft.com> wrote in message
    >> news:FE4011EF-817A-495D-A626-8D70D57086EA@microsoft.com...
    >> >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

    >>
    >>
    >>




  6. #6
    Biff
    Guest

    Re: Can I make certain cells required?

    ooops!

    My mistake, put it in the wrong place.

    Biff

    "Biff" <biffinpitt@comcast.net> wrote in message
    news:uuFFCHWbGHA.4672@TK2MSFTNGP04.phx.gbl...
    > 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" <to_much_spam_to_list@nospam.com> wrote in message
    > news:Ow3D5iVbGHA.488@TK2MSFTNGP04.phx.gbl...
    >> ~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" <C@discussions.microsoft.com> wrote in message
    >> news:FE4011EF-817A-495D-A626-8D70D57086EA@microsoft.com...
    >>>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

    >>
    >>

    >
    >




  7. #7
    Biff
    Guest

    Re: Can I make certain cells required?

    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" <C@discussions.microsoft.com> wrote in message
    news:4BB1845C-F801-4F09-8381-28169262EFD9@microsoft.com...
    > 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" <C@discussions.microsoft.com> wrote in message
    >> news:FE4011EF-817A-495D-A626-8D70D57086EA@microsoft.com...
    >> >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

    >>
    >>
    >>




  8. #8
    Dave Peterson
    Guest

    Re: Can I make certain cells required?

    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" <C@discussions.microsoft.com> wrote in message
    > > news:FE4011EF-817A-495D-A626-8D70D57086EA@microsoft.com...
    > > >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

  9. #9
    Biff
    Guest

    Re: Can I make certain cells required?

    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" <petersod@verizonXSPAM.net> wrote in message
    news:44568993.1CDEBEDA@verizonXSPAM.net...
    > 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" <C@discussions.microsoft.com> wrote in message
    >> > news:FE4011EF-817A-495D-A626-8D70D57086EA@microsoft.com...
    >> > >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




  10. #10
    Dave Peterson
    Guest

    Re: Can I make certain cells required?

    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" <petersod@verizonXSPAM.net> wrote in message
    > news:44568993.1CDEBEDA@verizonXSPAM.net...
    > > 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" <C@discussions.microsoft.com> wrote in message
    > >> > news:FE4011EF-817A-495D-A626-8D70D57086EA@microsoft.com...
    > >> > >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

  11. #11
    vezerid
    Guest

    Re: Can I make certain cells required?

    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


  12. #12
    ~C
    Guest

    Re: Can I make certain cells required?

    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" <C@discussions.microsoft.com> wrote in message
    > news:4BB1845C-F801-4F09-8381-28169262EFD9@microsoft.com...
    > > 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" <C@discussions.microsoft.com> wrote in message
    > >> news:FE4011EF-817A-495D-A626-8D70D57086EA@microsoft.com...
    > >> >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
    > >>
    > >>
    > >>

    >
    >
    >


  13. #13
    Dave Peterson
    Guest

    Re: Can I make certain cells required?

    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" <C@discussions.microsoft.com> wrote in message
    > > news:4BB1845C-F801-4F09-8381-28169262EFD9@microsoft.com...
    > > > 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" <C@discussions.microsoft.com> wrote in message
    > > >> news:FE4011EF-817A-495D-A626-8D70D57086EA@microsoft.com...
    > > >> >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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1