How can I "remind" users to fill in cells on a worksheet

Question

Hello: I have a set of cells on a worksheet called "Docs". The cell range is (B13:C23). When users get taken to this page, they are meant to fill out each of these cells with a value from 0 through 6. My Question: Is there some code that I can attach to this sheet where, if a user does not fill in a cell with anything (ie. leaves it blank) and tries to leave the sheet or close the workbook, they are somehow reminded to fill it in? Or is there a way to not let them leave the sheet until it's completed? Thanks.. Allan

Answer 1

Try writing a vba macro. Alt + F11 opens the VB Editor. Check out this SO post for VBA tutorials.

There are worksheet and workbook events that you can use. For example, Workbook_BeforeClose or Workbook_SheetChange. If you create methods for those events you can put code inside that checks that the required cells are filled.

Answer 2

You could give these cells conditional formatting, making them red if empty.

Answer 3

You can attach a macro to the change event of the form. Excel comes with built in validation but it does not work that well. For instance if someone pastes a value into the cell it does not validate what is pasted.

Start by creating a range by selecting the range of cells to be validated, right click and select "Name a Range". Note that I am testing this with Excel 2007. Say you call your range "InputRange".

Then open the VBA editor and create a procedure for the change event.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim vrange As Range, cell As Range
  Set vrange = Range("InputRange")
  If Intersect(vrange, Target) Is Nothing Then Exit Sub
  For Each cell In Intersect(vrange, Target)
     If cell.Value < 1 Or cell.Value > 6 Then
        MsgBox "Invalid Entry", vbCritical
        Application.EnableEvents = False
        cell.ClearContents
        cell.Activate
        Application.EnableEvents = True

     End If
  Next cell
End Sub

Note you can attach to any event that suits you.