Make text cells and combo boxes required?
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? |
Make text cells and combo boxes required?
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 "mlroy@savvis" wrote: 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? |
Make text cells and combo boxes required?
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 On Mon, 24 Jul 2006 09:25:02 -0700, Tom Hutchins wrote: 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 "mlroy@savvis" wrote: 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? |
All times are GMT +1. The time now is 08:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com