Dropdown List That Accepts Other User Input

I cannot find if there is a way to have a dropdown list that contains 
pre-defined data validation entries and also allow a user to input their own.

Is there a way to do this in Excel?

0
Utf
4/3/2010 11:00:01 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
1345 Views

Similar Articles

[PageSpeed] 13

Yes.

Uncheck "Show error alert"

The user-input will not be added to the list permanently.

If you want that you will require some VBA code.

See Debra Dalgleish's site for a sample workbook.

http://www.contextures.on.ca/excelfiles.html#DV0012


Gord Dibben  MS Excel MVP

On Sat, 3 Apr 2010 16:00:01 -0700, GEdwards
<GEdwards@discussions.microsoft.com> wrote:

>I cannot find if there is a way to have a dropdown list that contains 
>pre-defined data validation entries and also allow a user to input their own.
>
>Is there a way to do this in Excel?

0
Gord
4/4/2010 12:03:13 AM
Yes, change the Error Alert to Information.



-- 
Regards
Dave Hawley
www.ozgrid.com
"GEdwards" <GEdwards@discussions.microsoft.com> wrote in message 
news:75975A08-36AA-412C-9F42-34AAB0348CE4@microsoft.com...
>I cannot find if there is a way to have a dropdown list that contains
> pre-defined data validation entries and also allow a user to input their 
> own.
>
> Is there a way to do this in Excel?
> 

0
ozgrid
4/4/2010 2:51:19 AM
Thanks Gord.  This does the work I need and I have also bookmarked the site 
you suggested for future reference.

"Gord Dibben" wrote:

> Yes.
> 
> Uncheck "Show error alert"
> 
> The user-input will not be added to the list permanently.
> 
> If you want that you will require some VBA code.
> 
> See Debra Dalgleish's site for a sample workbook.
> 
> http://www.contextures.on.ca/excelfiles.html#DV0012
> 
> 
> Gord Dibben  MS Excel MVP
> 
> On Sat, 3 Apr 2010 16:00:01 -0700, GEdwards
> <GEdwards@discussions.microsoft.com> wrote:
> 
> >I cannot find if there is a way to have a dropdown list that contains 
> >pre-defined data validation entries and also allow a user to input their own.
> >
> >Is there a way to do this in Excel?
> 
> .
> 
0
Utf
4/4/2010 2:54:01 AM
With all due respect - when the OP asks [quote]: 
"...and also allow a user to input their own" - I, and probably 99% of the 
supporters in this D.G., would guess he DOES NOT want and/or need ANY KIND of 
alert about an "Illegal Value..."
Micky


"ozgrid.com" wrote:

> Yes, change the Error Alert to Information.
> 
> 
> 
> -- 
> Regards
> Dave Hawley
> www.ozgrid.com
> "GEdwards" <GEdwards@discussions.microsoft.com> wrote in message 
> news:75975A08-36AA-412C-9F42-34AAB0348CE4@microsoft.com...
> >I cannot find if there is a way to have a dropdown list that contains
> > pre-defined data validation entries and also allow a user to input their 
> > own.
> >
> > Is there a way to do this in Excel?
> > 
> 
0
Utf
4/4/2010 6:32:01 PM
http://boisgontierjacques.free.fr/fichiers/DonneesValidation/DV_AjoutListe.xls

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 2 And Target.Count = 1 Then
  If Target <> "" Then
    If IsError(Application.Match(Target.Value, [Liste], 0)) Then
      If MsgBox("Add?", vbYesNo) = vbYes Then
        [Liste].End(xlDown).Offset(1, 0) = Target.Value
        Sheets("Liste").[Liste].Sort key1:=Sheets("Liste").Range("A2")
      Else
        Application.Undo
      End If
     End If
   End If
  End If
End Sub

Named range:
=Offset(Liste!$A$2,,,CountA(Liste!$A:$A)-1)

JB


On 4 avr, 01:00, GEdwards <GEdwa...@discussions.microsoft.com> wrote:
> I cannot find if there is a way to have a dropdown list that contains
> pre-defined data validation entries and also allow a user to input their own.
>
> Is there a way to do this in Excel?

0
JB
4/5/2010 4:45:01 AM
Reply:

Similar Artilces:

Create list to choose from
Hi, I'm just trying to create a short pulldown list in a spreadsheet. Instead of having to retype repetitve selections for this spreadsheet that gets used a lot, I'd like to create a pulldown list, but I'm not sure which kind of list to choose and I'm not sure how to enter the list. Can anyone help. Thanks a bunch. Diana Hi Dianah i'ld go with data / validation check out Debra Dalgleish's site for details http://www.contextures.com/xlDataVal01.html cheers JulieD "Dianah" <nospam@nospam.com> wrote in message news:OLWdR3GWEHA.2716@tk2msftngp13.phx....

find a description in a list and add it in an other one
Hello, I would like to create a small vb code, to find a description in a list and add it in an other one... Let me explain my case : I have an excel spreadsheet with two sheets... The first one (sheet "result") contents a list like this one : D10993300 YG37 DistrDemand (Plnd.) D10385135 YG38 Total Demand D10387412 YT23 Dependent Demand D10385167 TE45 Total Demand This list can have a very variable size, One line or several thousands of line The second one, sheet "Dcode list" contents the description of ALL the code like "D10993300" D10383164 p...

Dropdown menu
How do I add a dropdown menu in my workbook? You can use Data Validation to create dropdown lists. There are instructions in Excel's help, and here: http://www.contextures.com/xlDataVal01.html keisha wrote: > How do I add a dropdown menu in my workbook? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

How do I create a scroll list of macro buttons?
I have created several buttons for different macros on one worksheet. How can I make some type of scroll list that contains each macro button? Place the buttons on a Toolbar which is activated when that worksheet is activated. Private Sub Worksheet_Activate() Application.CommandBars("new bar name").Visible = True End Sub Private Sub Worksheet_Deactivate() Application.CommandBars("new bar name").Visible = False End Sub Gord Dibben Excel MVP On Mon, 31 Oct 2005 13:15:04 -0800, "RobertM" <RobertM@discussions.microsoft.com> wrote: >I have created s...

Names Drop down list
I have version 2002 SP-2 Outlook. Also, under tools/options/email options/advanced email options, I have "suggest names while completing" and "automatic name checking" checked. I was recently moved from outlook express to Outlook. Now when I write a letter, when I start writing a name, nothing happens. When I start writing an email address, then I get a suggestion drop down list. , How do I get names to do that,. I had no problem in Outlook Express. ...

How do I create a dropdown within a dropdown?
I know how to create a dropdown list. On one or more of the items in that dropdown list, I would like to create another dropdown. How do I do this? The second list dependent on what was chosen in the first???? If you used Data|validation for your dropdown list, then you could visit Debra Dalgleish site: http://www.contextures.com/xlDataVal02.html Joyce Keller wrote: > > I know how to create a dropdown list. > On one or more of the items in that dropdown list, I would like to create > another dropdown. > How do I do this? -- Dave Peterson ...

Project Server 2007 bind Existing users and Resources together
Hi, We have a Project server 2007 solution where we have 10-15 users (forms authentication) when creating them we did not define/marked them as resources. we then created the resources, now we would like to know if it is possible to bind the existing users and resources together as the resources we allready have created is connected to projects... i think using pwa to make existing users resources will create them as new resources not bind them to existing ones.? -- Torben Nielsen Torben -- Before I attempt to give you a solution to your problem, please answer a ques...

Backup Strategies for heavy outlook users?
I'm looking at an Internet backup service for someone who hs GB+ PST files and lives inside Outlook2003. Gettting files off-site is a big plus for him. He has residential broadband and his uplink speed is about 400kbps At that rate, uploading a PST file will take hours. Suggestions? -- a d y k e s @ p a n i x . c o m Don't blame me. I voted for Gore. A Proud signature since 2001 If the user is using a single pst, or mutiply pst's that they access frequently then there is no option but to backup the entire pst(s) since they will be flagged as modified every night so requ...

DropDown
Need to be able to enter first several letters of a name and have the dropdown pick them up as I type in the name. Had it on a demo worksheet in my "learn" files BUT lost it somewhere. Please refer me to where I can pick up some samples to use.. Thank You.. Ensure the AutoWordSelect property of the combobox is set to true (default) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk "jlmccabes" <jlmccabes@cfl.rr.com> wrote in message news:kLxeh.9601$yj1.496@tornado.tampabay.rr.com... &...

Data Validation Input message position
A workbook I've created has quite a few Data Validations. By accident I found out that when the input message is showing, you can use the mouse to shift it to wherever you want on the sheet. Once that is done, all other data validation messages will appear in that same spot (which is not always a bad thing) BUT..... The messages will no longer be shown in their default position (next to or near the active cell that causes the validation input to appear). Is there a way to restore that default setting? Rob AFAIK, once the input message has been moved, you can't reset it to its ori...

Allow user to choose reply to address
Exchange 2003, Outlook 2003. A user has two smtp addresses, and they want to be able to change the reply to address and from address depending on who they are sending too from these two assigned SMTP addresses. This is only one Exchange account with multiple SMTP addresses though. Is this possible? Third party app, or you will need to create two separate objects that has send as rights to the other. http://www.ivasoft.biz/choosefrom.shtml James Chong (MVP) MCSE | M+, S+, MCTS, Security+ msexchangetips.blogspot.com On Mar 12, 1:22 pm, "Kevin Nickell" <knickell@yahoo_....

background color of individual list items
I was wondering if someone might know if it is possible change the background color of a listbox so that one line was the default white and another line was some other color. If not, I guess I will have to use individual label controls for this. I would rather use a listbox though. Thanks, Roger You can do that if you set the owner draw flag. It is pretty easy. take a look these examples: http://www.codeguru.com/Cpp/controls/listbox/colorlistboxes/article.php/c4745/ http://www.codeguru.com/Cpp/controls/listbox/colorlistboxes/article.php/c1611/ What you would have to do is o...

DropDown Buttons
Hi, I need to create a drop down button that will show a drop down menu when a user clicks it then allow the user to select an option from the dorp down menue. This needs to be a stand alone button that can be placed anywhere and not a menu button that must be part of a menu. Here is a link to an image of the type of button I am wanting to create http://img126.imageshack.us/img126/2304/dropdownbutton9cp.jpg If anyone has any suggestions or knows of any good resources please post them up. Thanks in advanced, Ryan One way is to bring up a popup window when the button is clicked. Try th...

LDAP Filter on Adress List
Dear Support, How to built one adress list by Organisation Unit? I must find only recipients of this OU. Thanks for helping I manage an exchange environment for a school at a university. Many of our faculty have very large mailboxes and like to keep their mail for ever. As silly as it may sound, I try to support this. My peferred configuration for these people is to auto-archive mail from the server into a .pst when it reaches some age then divide their pst files into years for permanent archiving. The benefits for this are obvious. The problem is how to backup the local copy of...

Export data from List box
Hi, I'd like to export data from a list box on a form. The list box has a stored procedure with parameter as RecordSource and I want to do it with a click button event. I need to copy data in csv format, but if it's difficult tell me how I can do it in other formats. Many thanks for your answers. Angelo ...

exchange 2007 central address lists
Hi Guys What's the best way to handle this one? -Require a central address list most people will have read access and a couple of people read/write to keep it updated -Several thousand users and groups from none exchange days so in .pst file -No reason that these are in the GAL because they are just mailing lists and no need for AD involvement Cheers A contacts-type public folder might work for you. -- Ed Crowley MVP "There are seldom good technological solutions to behavioral problems." .. "martin" <usenet@etiqa.co.uk> wrote in messag...

Server accepts mail for multiple domains, replies with wrong one?
Hi there. Our server accepts mail for 3 different domain names. My question is, if someone sends an Email to user@domaina.com when outlook replies it always sends the mail as the Primary User on the AD account. Is there anyway to have it automatically SEND replies as the address that recieved the message? Here is an example. Bob has 3 addresses: bob@domain1.com bob@domain2.com bob@domain3.com Jane sends an email to bob@domain3.com in AD bob's primary account is bob@domain2.com so whenever bob replies outlook automatically sends the email back as bob@domain2.com Is there any...

Auto dropdown
I would like the list of a combobox to dropdown when the form (where the combox is in) is opened. How can I do that? Thanks, John On Mon, 18 Feb 2008 20:33:18 +0100, John wrote: > I would like the list of a combobox to dropdown when the form (where the > combox is in) is opened. > How can I do that? > Thanks, > John Only when the form is first opened? Code the form's Load event: Me.ComboName.SetFocus Me.ComboName.Dropdown -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail Please ignore, I found the solution: Me!<<ComboName>...

list all menus
Hi, I need to get a list of every menu of every application currently open. By using GetWindow i am able to list all the applications and then by using GetMenu, GetSubMenu, GetMenuText etc I can get the menu details. The problem is that it works perfectly for some applications but not others. For example, I can see all the menus in Notepad and Task Manager but no menus at all for Visual Studio, Internet Explorer or any Explorer windows. Does anyone know why this is? Is it possible to list every menu of every app or should i give up? Thanks, mav I've just checked what you did...

Create template that ignors data list rules
Question: Is there a way to copy all rows in a worksheet [template] to a master data list at the same time, and have possibly several users saving their worksheet forms simultaneously with the data list being updated automatically as they save their work or is there a better way to accomplish this task using Excel? Please explain like I'm a two year old. The plan is for several users on a network to enter data in an Excel 2002 form [1 worksheet each user] the form contains about 17 rows with 10 columns on a template. When the users save their form the data will be automatically copied ...

outlook 2003 user cannot see members of distribution list
I have an outlook 2003 user who, when clicking on a distribution list or an individual name in a received email; the dialog box appears displaying the properties of the person or group, but there is nothing showing up in the "member of" tab. this is the only user I have affected. any help would be appreciated. Normally when clicking on a user of group listed in the "To" of an email the properties can be viewed showing the groups the individual is a member of, etc. Have you tried creating a new mail profile for this user? --� Milly Staples [MVP - Outlook] Post ...

select From dropdown and return another dropdown menu
Hi, i am looking for help with dropdowns and returns. I would like to b able to have staff select their business area from a dropdown menu, when a business area is selected i would like it to then offer the corresponding cost codes for that business area as a dropdown and those cost codes be available for selection. Is it possible? many thanks in anticipation. Nigel hi not sure but see if this is what you want http://www.contextures.com/xlDataVal02.html regards FSt1 "RE4379" wrote: > Hi, > > i am looking for help with dropdowns and retur...

Dropdowns
I am using Office 2000 on a windows 2000 machine. Can you tell me how to create a dropdown with choices for a cell. I want to put choices such as water, lightning, roof, hail, etc. in addition to having a dropdown to open i would like the option of just typing in the first letter and having a choice come up. thank you janice Hi You are looking for Data / Validation. Using this you can set ceratin criteria (whole numbe, date, time etc) or you create a list (or use an existing one) -- Andy. "Janice" <anonymous@discussions.microsoft.com> wrote in message news:2699...

Unix mail to Global Distribution List
We've set up a Global Distribution List on Exchange and it works fine from Outlook. We have a Unix application that needs to mail this group. This does not work. Changing the app to mail individuals does work. Is there an option in Distribution List setup to allow this? Thanks Geoff On 6 Feb 2007 01:03:51 -0800, pritchardgeoff@gmail.com wrote: >We've set up a Global Distribution List on Exchange and it works fine >from Outlook. We have a Unix application that needs to mail this >group. This does not work. Changing the app to mail individuals does >work. Is there an op...

Getting input from and Sending output to another thread's window
Originally there was client software that I wanted to write a program to mimic. However, that will be too difficult I discovered because I don't know the details of how the software communicates with the server. So what I want to do now is simply open the original client, but write a program to automate what the client software does. So my question is, is there a way I can monitor what messages the client software's windows are receiving? And can I tell my program to simulate mouse clicks on certain buttons that are in a window belonging to a thread other than my own (e.g. in the c...