Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List that will not allow cell to be empty
Hi again all! ;-)
Is there a way to use the Data/Validation/List function to only allow users to only enter Y or N BUT cannot be left empty? Or is there a Custom formula that will do this? thanks! -- carusso |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List that will not allow cell to be empty
Since there is nothing to force the user to even select that cell, there is
nothing in Data Validation to prevent the cell from being left blank. However, there are VBA methods that could be used. For instance, when the user saves the workbook or closes the workbook, VBA can check that certain cells are in whatever condition you want. If they are not, a message box can be displayed to inform the user of what is required, and the saving/closing can be negated if you wish. Post back if this might fit what you want. HTH Otto "carusso" wrote in message ... Hi again all! ;-) Is there a way to use the Data/Validation/List function to only allow users to only enter Y or N BUT cannot be left empty? Or is there a Custom formula that will do this? thanks! -- carusso |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List that will not allow cell to be empty
Enter a Y or N in the cell then DataValidationAllowList
In the source listbox enter Y,N Make sure error alert is enabled. Gord Dibben MS Excel MVP On Mon, 26 Jan 2009 15:52:01 -0800, carusso wrote: Hi again all! ;-) Is there a way to use the Data/Validation/List function to only allow users to only enter Y or N BUT cannot be left empty? Or is there a Custom formula that will do this? thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List that will not allow cell to be empty
and the saving/closing can be negated if you wish.
In that case I'll just use Task Manager to kill it! <BG -- Biff Microsoft Excel MVP "Otto Moehrbach" wrote in message ... Since there is nothing to force the user to even select that cell, there is nothing in Data Validation to prevent the cell from being left blank. However, there are VBA methods that could be used. For instance, when the user saves the workbook or closes the workbook, VBA can check that certain cells are in whatever condition you want. If they are not, a message box can be displayed to inform the user of what is required, and the saving/closing can be negated if you wish. Post back if this might fit what you want. HTH Otto "carusso" wrote in message ... Hi again all! ;-) Is there a way to use the Data/Validation/List function to only allow users to only enter Y or N BUT cannot be left empty? Or is there a Custom formula that will do this? thanks! -- carusso |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List that will not allow cell to be empty
Hi Gord, I did try what you suggested below, with the error alert enabled,
but The Stop Error wasn't displayed when the cell was left blank. -- carusso "Gord Dibben" wrote: Enter a Y or N in the cell then DataValidationAllowList In the source listbox enter Y,N Make sure error alert is enabled. Gord Dibben MS Excel MVP On Mon, 26 Jan 2009 15:52:01 -0800, carusso wrote: Hi again all! ;-) Is there a way to use the Data/Validation/List function to only allow users to only enter Y or N BUT cannot be left empty? Or is there a Custom formula that will do this? thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List that will not allow cell to be empty
Hi Otto, thanks for your response. This would definitely solve the problem.
Me being FAR from being an expert in VBA, I would greatly appreciate your help with writing that code. Thanks! Cheryl -- carusso "Otto Moehrbach" wrote: Since there is nothing to force the user to even select that cell, there is nothing in Data Validation to prevent the cell from being left blank. However, there are VBA methods that could be used. For instance, when the user saves the workbook or closes the workbook, VBA can check that certain cells are in whatever condition you want. If they are not, a message box can be displayed to inform the user of what is required, and the saving/closing can be negated if you wish. Post back if this might fit what you want. HTH Otto "carusso" wrote in message ... Hi again all! ;-) Is there a way to use the Data/Validation/List function to only allow users to only enter Y or N BUT cannot be left empty? Or is there a Custom formula that will do this? thanks! -- carusso |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List that will not allow cell to be empty
Well, Biff, fortunately a lot of my users don't even have a clue what Task
Manager is! ;-) Cheryl -- carusso "T. Valko" wrote: and the saving/closing can be negated if you wish. In that case I'll just use Task Manager to kill it! <BG -- Biff Microsoft Excel MVP "Otto Moehrbach" wrote in message ... Since there is nothing to force the user to even select that cell, there is nothing in Data Validation to prevent the cell from being left blank. However, there are VBA methods that could be used. For instance, when the user saves the workbook or closes the workbook, VBA can check that certain cells are in whatever condition you want. If they are not, a message box can be displayed to inform the user of what is required, and the saving/closing can be negated if you wish. Post back if this might fit what you want. HTH Otto "carusso" wrote in message ... Hi again all! ;-) Is there a way to use the Data/Validation/List function to only allow users to only enter Y or N BUT cannot be left empty? Or is there a Custom formula that will do this? thanks! -- carusso |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List that will not allow cell to be empty
Since you are the man. I have quesiton that has has not really been answered.
I think the quesiton lies more into how to structure my table array. I have a table array that is based on an agent #. from there I use vlookup formulas to popluate all the various info that goes along with the agent #. OK here is the problem, for accounting purposes when the agent # is paid, an office code (that is part of the table array) get s paid as well. Ok so... here is the problem, we change people to differeent office codes at times. If I had the array stuctured were the agent # was duplicated for example 177721 BOB Jones 401895 1-1-2008 177721 BOB Jones 401912 6-1-2009 Ok there is production date column in data sheet. How can I stucture this were based off the agent # and the date it will produce the correct office code. Please advise "T. Valko" wrote: and the saving/closing can be negated if you wish. In that case I'll just use Task Manager to kill it! <BG -- Biff Microsoft Excel MVP "Otto Moehrbach" wrote in message ... Since there is nothing to force the user to even select that cell, there is nothing in Data Validation to prevent the cell from being left blank. However, there are VBA methods that could be used. For instance, when the user saves the workbook or closes the workbook, VBA can check that certain cells are in whatever condition you want. If they are not, a message box can be displayed to inform the user of what is required, and the saving/closing can be negated if you wish. Post back if this might fit what you want. HTH Otto "carusso" wrote in message ... Hi again all! ;-) Is there a way to use the Data/Validation/List function to only allow users to only enter Y or N BUT cannot be left empty? Or is there a Custom formula that will do this? thanks! -- carusso |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List that will not allow cell to be empty
Hi there, not sure how this happened, but it looks like your response (below)
ended up in the wrong (my) thread.... (Subject: Validation List that will not allow cell to be empty) Cheryl -- carusso " wrote: Since you are the man. I have quesiton that has has not really been answered. I think the quesiton lies more into how to structure my table array. I have a table array that is based on an agent #. from there I use vlookup formulas to popluate all the various info that goes along with the agent #. OK here is the problem, for accounting purposes when the agent # is paid, an office code (that is part of the table array) get s paid as well. Ok so... here is the problem, we change people to differeent office codes at times. If I had the array stuctured were the agent # was duplicated for example 177721 BOB Jones 401895 1-1-2008 177721 BOB Jones 401912 6-1-2009 Ok there is production date column in data sheet. How can I stucture this were based off the agent # and the date it will produce the correct office code. Please advise "T. Valko" wrote: and the saving/closing can be negated if you wish. In that case I'll just use Task Manager to kill it! <BG -- Biff Microsoft Excel MVP "Otto Moehrbach" wrote in message ... Since there is nothing to force the user to even select that cell, there is nothing in Data Validation to prevent the cell from being left blank. However, there are VBA methods that could be used. For instance, when the user saves the workbook or closes the workbook, VBA can check that certain cells are in whatever condition you want. If they are not, a message box can be displayed to inform the user of what is required, and the saving/closing can be negated if you wish. Post back if this might fit what you want. HTH Otto "carusso" wrote in message ... Hi again all! ;-) Is there a way to use the Data/Validation/List function to only allow users to only enter Y or N BUT cannot be left empty? Or is there a Custom formula that will do this? thanks! -- carusso |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List that will not allow cell to be empty
Cheryl
This little macro will fire when the user attempts to save the workbook. As written, this macro will check cell F5 in a sheet named "TheShtName". If the cell is blank, a message box will appear telling him that the Save command has been cancelled and that F5 in sheet "TheShtName" must have an entry. This is a workbook event macro and MUST be placed in the workbook module. In all versions of Excel prior to 2007, you can access that module by right-clicking on the Excel icon that is immediately to the left of the word "File" in the menu at the top of the screen and selecting View Code. "X" out of the module to return to your sheet. HTH Otto Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If IsEmpty(Sheets("TheShtName").Range("F5").Value) Then Cancel = True MsgBox "The Save command has been canceled. An entry must" & Chr(13) & _ "be made in sheet TheShtName, cell F5.", 16, "Entry Required" End If End Sub "carusso" wrote in message ... Hi Otto, thanks for your response. This would definitely solve the problem. Me being FAR from being an expert in VBA, I would greatly appreciate your help with writing that code. Thanks! Cheryl -- carusso "Otto Moehrbach" wrote: Since there is nothing to force the user to even select that cell, there is nothing in Data Validation to prevent the cell from being left blank. However, there are VBA methods that could be used. For instance, when the user saves the workbook or closes the workbook, VBA can check that certain cells are in whatever condition you want. If they are not, a message box can be displayed to inform the user of what is required, and the saving/closing can be negated if you wish. Post back if this might fit what you want. HTH Otto "carusso" wrote in message ... Hi again all! ;-) Is there a way to use the Data/Validation/List function to only allow users to only enter Y or N BUT cannot be left empty? Or is there a Custom formula that will do this? thanks! -- carusso |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List that will not allow cell to be empty
Thanks, Otto. I had a little trouble getting the macro to work...I think it
might have had something to do with the text wrapping in the msg box. I modified it to this (with my specifics) and now it works: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If IsEmpty(Sheets("ADaM General Template").Range("H2").Value) Then Cancel = True MsgBox ("XML Data Type must be entered in Cell H2.") End If End Sub Thanks again! -- carusso "Otto Moehrbach" wrote: Cheryl This little macro will fire when the user attempts to save the workbook. As written, this macro will check cell F5 in a sheet named "TheShtName". If the cell is blank, a message box will appear telling him that the Save command has been cancelled and that F5 in sheet "TheShtName" must have an entry. This is a workbook event macro and MUST be placed in the workbook module. In all versions of Excel prior to 2007, you can access that module by right-clicking on the Excel icon that is immediately to the left of the word "File" in the menu at the top of the screen and selecting View Code. "X" out of the module to return to your sheet. HTH Otto Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If IsEmpty(Sheets("TheShtName").Range("F5").Value) Then Cancel = True MsgBox "The Save command has been canceled. An entry must" & Chr(13) & _ "be made in sheet TheShtName, cell F5.", 16, "Entry Required" End If End Sub "carusso" wrote in message ... Hi Otto, thanks for your response. This would definitely solve the problem. Me being FAR from being an expert in VBA, I would greatly appreciate your help with writing that code. Thanks! Cheryl -- carusso "Otto Moehrbach" wrote: Since there is nothing to force the user to even select that cell, there is nothing in Data Validation to prevent the cell from being left blank. However, there are VBA methods that could be used. For instance, when the user saves the workbook or closes the workbook, VBA can check that certain cells are in whatever condition you want. If they are not, a message box can be displayed to inform the user of what is required, and the saving/closing can be negated if you wish. Post back if this might fit what you want. HTH Otto "carusso" wrote in message ... Hi again all! ;-) Is there a way to use the Data/Validation/List function to only allow users to only enter Y or N BUT cannot be left empty? Or is there a Custom formula that will do this? thanks! -- carusso |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation List that will not allow cell to be empty
Cheryl
You did a good job. Thanks for the feedback. Otto "carusso" wrote in message ... Thanks, Otto. I had a little trouble getting the macro to work...I think it might have had something to do with the text wrapping in the msg box. I modified it to this (with my specifics) and now it works: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If IsEmpty(Sheets("ADaM General Template").Range("H2").Value) Then Cancel = True MsgBox ("XML Data Type must be entered in Cell H2.") End If End Sub Thanks again! -- carusso "Otto Moehrbach" wrote: Cheryl This little macro will fire when the user attempts to save the workbook. As written, this macro will check cell F5 in a sheet named "TheShtName". If the cell is blank, a message box will appear telling him that the Save command has been cancelled and that F5 in sheet "TheShtName" must have an entry. This is a workbook event macro and MUST be placed in the workbook module. In all versions of Excel prior to 2007, you can access that module by right-clicking on the Excel icon that is immediately to the left of the word "File" in the menu at the top of the screen and selecting View Code. "X" out of the module to return to your sheet. HTH Otto Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If IsEmpty(Sheets("TheShtName").Range("F5").Value) Then Cancel = True MsgBox "The Save command has been canceled. An entry must" & Chr(13) & _ "be made in sheet TheShtName, cell F5.", 16, "Entry Required" End If End Sub "carusso" wrote in message ... Hi Otto, thanks for your response. This would definitely solve the problem. Me being FAR from being an expert in VBA, I would greatly appreciate your help with writing that code. Thanks! Cheryl -- carusso "Otto Moehrbach" wrote: Since there is nothing to force the user to even select that cell, there is nothing in Data Validation to prevent the cell from being left blank. However, there are VBA methods that could be used. For instance, when the user saves the workbook or closes the workbook, VBA can check that certain cells are in whatever condition you want. If they are not, a message box can be displayed to inform the user of what is required, and the saving/closing can be negated if you wish. Post back if this might fit what you want. HTH Otto "carusso" wrote in message ... Hi again all! ;-) Is there a way to use the Data/Validation/List function to only allow users to only enter Y or N BUT cannot be left empty? Or is there a Custom formula that will do this? thanks! -- carusso |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get rid of empty cells when displaying Data validation list | Excel Discussion (Misc queries) | |||
Empty Cell Validation | Excel Discussion (Misc queries) | |||
compile list in 1st empty cell | Excel Discussion (Misc queries) | |||
Remove empty cells from named list / validation list | Excel Worksheet Functions | |||
Empty Cells in validation List | Excel Worksheet Functions |