Workaround For Manually Entering A Value Into A Cell That Is List Validated - If possible

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,812
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have been using data validation to provide a dropdown list for the users to select a value from in a cell. I understand the purpose of validation ... to restrict the value to only those provided in the defined list of acceptable values.

But I'm faced with a challenge, that I hope someone can suggest a workaround, or alternative approach to what I need to do.

I have a cell that allows the user to select a workorder number from a dropdown. This cell has validation applied to it based on the list of available workorder numbers. But there may be an opportunity where the user needs to enter a workorder number that is not part of that list (yet). As long as the user enters a valid workorder number, the process associated with this "non recorded workorder" would eventually add it to the list after the workorder, identified as "new" information specific to that workorder is processed.

So the issue is, if cell validation is restricting the user to only those values in the list already, how would it be made possible for the user to enter a value not in the list? I was thinking of adding "OTHER" to the list for the user to select, opening up the process of data entry specific to a new workorder number and then submitting it to the database. But the cell still won't accept the new value will it even if the code puts it in the cell (overwriting OTHER). It would I assume if I deleted the validation, but I'd prefer not to do that. Would my only solution be to recreate the validation list, delete the xisting one and replace it with the new one (which would include the added workorder number) before sending the "new" number value to the cell? The bahviour I'm looking at is something similar to the combobox form control.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,172
When you create the Data Validation dropdown, click on the "Error Alert" tab and remove the check mark to the left of the "Show error....." line. This will allow you to enter a value that is not in the list of available work order numbers. You could have a macro add this value to the list if doesn't already exist.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows
Another option, on the Error Alert select either Warning, or Information.
That will warn the user, but still allow them to enter a value.
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,812
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thank you folks, your responses provide a far more encouraging solution than I thought I was up against.
With each solution I get I learn a little bit more around the features and parameters that often are left without consideration because of lack of understanding.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,123,391
Messages
5,601,391
Members
414,448
Latest member
Jessica 22664

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top