Requiring Cell Content on Save

[The Chad]

I'm creating a 12 question survey in Excel and want to find a way to require
users to provide responses in specific cells. Ideally, I'd like to run a
check on cells I'm requiring a response in and make sure those specific cells
are not blank. If they are blank, I'd like to generate a dialog box warning
that requires them to populate the cells when they try to save the file.

Not sure how to best do this, any suggestions?

Cheers,

 

[Gord Dibben]

Copy this to Thisworkbook module.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI _
As Boolean, Cancel As Boolean)
If Application.CountA(Sheets("Sheet1").Range("A1:C1")) < 3 Then
Cancel = True
MsgBox "Please fill required cells, save has been cancelled"
End If
End Sub

To access that module right-click on the Excel Icon left of "File" on the
menubar.

Select "View Code". Paste into that module.

Edit the sheetname and range of cells to suit.

Alt + q to return to the Excel window after editing.

Note: if user disables macros the above won't run.


Gord Dibben MS Excel MVP