Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Ancaster, Ontario
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Force data enrty before close (2003)

    I've searched the lounge, and every google way I can think of, so I thought I'd just ask. We have an on-line spreadsheet for logging receipts of goods, which requires specific fields to be entered. If they are not, I'd like the file not to close and a reminder to go back and add the missing info. I'm not much on VBA, but I did play around with logic in the worksheet to generate a True/False for content in the required cells, then tried to find some way to recognize true/false and refuse to close, and spit out a reminder window.
    Any ideas where I can go to get help on this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Force data enrty before close (2003)

    Activate the Visual Basic Editor (Alt+F11).
    Double click the ThisWorkbook node of your workbook in the Project Explorer (the treeview on the left hand side).
    Enter or copy/paste the following code into the module window:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Trim(Range("A1")) = "" Then
    MsgBox "Please enter a value in A1.", vbExclamation
    Range("A1").Select
    Cancel = True
    End If
    End Sub

    Replace A1 with a cell you want to be filled in; you can add If ... End If blocks for other cells as needed.
    Switch back to Excel (Alt+F11).
    Try closing the workbook while a required cell is blank.

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Ancaster, Ontario
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Force data enrty before close (2003)

    That's great Hans...much appreciated.

    I take it that if I have multiple cells needing entry I can nest them, and customize the message in the message box to refer to the missed cell.

    Cool. Now I can play.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Force data enrty before close (2003)

    As noted, you can add If .. End If blocks for other cells, adjusting the cell reference and message for each. You don't have to nest them.

Posting Permissions

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