Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo boxes and formulas | Excel Worksheet Functions | |||
Shifting Combo boxes | Excel Discussion (Misc queries) | |||
Hide my combo boxes! | Excel Discussion (Misc queries) | |||
Nesting Combo Boxes /Returning an Array | Excel Discussion (Misc queries) | |||
dynamic combo boxes | Excel Worksheet Functions |