Make text cells and combo boxes required?

[Guest]

I would like to make some text cells and combo boxes required fields. Is
there a way to format these cells/combo boxes so that when there is no value
selected the user receives an error messaging upon saving letting them know
that they must complete the required fields before they can save the
spreadsheet?

 

[Guest]

I think you will need VBA for this. You can add a Workbook_BeforeSave event
in the ThisWorkbook module. For example:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Check for a value in cell B5 on Sheet1.
If Len(Sheets("Sheet1").Range("B5").Value) = 0 Then
MsgBox "Cell B5 on Sheet1 must have data before saving"
'Setting Cancel to TRUE stops the workbook from saving.
Cancel = True
Exit Sub
End If
'Check for a selection in combo box Drop Down 1.
If Sheets("Sheet1").Shapes("Drop Down 1").ControlFormat.ListIndex = 0 Then
MsgBox "You must select a value from the combo box before saving"
Cancel = True
Exit Sub
End If
End Sub

Hope this helps,

Hutch

 

[Gord Dibben]

And hope that users do not disable macros when opening the workbook.

You need a whole 'nother set of code to deal with that.


Gord Dibben MS Excel MVP