Alerting Users that Required Fields have not been Completed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to setup a function similar to a web-based function which
informs users that all required information has not been completed and to
please complete all required fields. I would like to be able to allow them
to close the spreadsheet even if required fields are not completed.

Does anyone know how to setup such a functionality in Excel?
 
hi XLUser

You can use the beforeclose event in the Thisworkbook module

Try this tester for the cells A1,A3 and C1

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim response
If Application.WorksheetFunction.CountA(Sheets("Sheet1").Range("A1,A3,C1")) < 3 Then
response = MsgBox("Are you sure you want to close, not all cells are filled", vbYesNo)
If response = vbYes Then
'do nothing
Else
Cancel = True
End If
Else
'do nothing
End If
End Sub
 
So if I spent 3 hours doing data entry and run out of time, you're going to have
me throw away my work by not allowing me to save it and come back later to
finish up?

Personally, I think this is not nice.

I like to use another worksheet that has lots of formulas that check to see if
my data is valid.

I could add another check like:

=If(counta(sheet2!a1:b3)=6,"ok","Error: Please enter all the data in Sheet2
A1:B3")

If I have lots of these error checks, I'll add data|filter|autofilter so that
the only the non-Ok's show.
 
Dave,

Not sure if you were referring to my request or Ron's response, but I am not
seeking to prevent users from closing the worksheet if all fields are not
filled in. I simply want to give a warning.

On the other hand, I don't quite understand how to actually implement what
Ron or you have suggested. I am not an Excel expert, so I need some advice
at a novice level :-)
 
I posted event code that will run when you close the file.
It will check if the 3 cells have a value and show a msgbox if not.

If you say No it will not close the file

See
http://www.cpearson.com/excel/events.htm




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


XLUser said:
Dave,

Not sure if you were referring to my request or Ron's response, but I am not
seeking to prevent users from closing the worksheet if all fields are not
filled in. I simply want to give a warning.

On the other hand, I don't quite understand how to actually implement what
Ron or you have suggested. I am not an Excel expert, so I need some advice
at a novice level :-)
Click to expand...
 
Sorry, I misread your last statement.

I'd still use formulas--either in cells close by or in a separate worksheet.
Dave,

Not sure if you were referring to my request or Ron's response, but I am not
seeking to prevent users from closing the worksheet if all fields are not
filled in. I simply want to give a warning.

On the other hand, I don't quite understand how to actually implement what
Ron or you have suggested. I am not an Excel expert, so I need some advice
at a novice level :-)
Click to expand...
 
Back
Top