Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Preventing File Closing if certain cells are empthy

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there a way to prevent the user from exiting or saving a file if certain cells are empty? I am creating a template to move data into a database but I want to make sure the user has entered all the required fields before saving and exiting the program.

    Thanks.

    John

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sure...
    You did not give any details...
    This will not allow an exit if there is
    nothing in sheet1 Cell A1...
    It will select the cell in question as well.
    You could test a range, mutliple ranges, data types, formats, ect...



    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Sheet1.Cells(1, 1) = Empty Or Sheet1.Cells(1, 1) = "" Then
    Cancel = True
    MsgBox "Required field missing"
    Sheet1.Cells(1, 1).Select
    End If
    End Sub



    [ This Message was edited by: TsTom on 2002-04-30 08:40 ]

  3. #3
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this. JSW

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim mySel
    'Works in "ThisWorkBook" module.
    'Only if cell has value will the Save or SaveAs utility open.
    'If cell has no value, no "SaveAs" is acknowledged.
    'If cell has no value "Save" by choice only.
    If Sheets("Sheet1").Range("A1") = "" Then
    mySel = MsgBox("Do you really want to save the workbook," & Chr(13) _
    & "the required cell A1 is empty?", vbYesNo)
    If mySel = vbNo Then Cancel = True
    End If
    End Sub

    The above code is safe.

    The code below, will prevent a "Close" if the condition is not met, so use caution!

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Prevent workbook close if cell is empty.
    If Sheets("Sheet1").Range("A1") = "" Then Cancel = True
    End Sub

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Do you think is any way to:

    Prevent a worksheet/file to print if certain cells are empty?

  5. #5
    New Member
    Join Date
    Apr 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Preventing File Closing if certain cells are empthy

    I put this code but its not working can you please send me any sample file with this code.
    E-mail Id:sarapjit@gmail.com

  6. #6
    New Member
    Join Date
    Apr 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Preventing File Closing if certain cells are empthy

    I put this code but its not working can you please send me any sample file with this code.
    E-mail Id:sarapjit@gmail.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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