+ Reply to Thread
Results 1 to 3 of 3

Drop down list that removes entries once clicked

  1. #1
    Registered User
    Join Date
    05-16-2018
    Location
    USA
    MS-Off Ver
    2010
    Posts
    4

    Drop down list that removes entries once clicked

    I currently have a macro that will enable me to select multiple words from a drop down list and places them in one cell separated by commas (no duplicates allowed). So for example your name in cell A1 and the next cell B1 is a drop down list and you select any state you have visited (B1 shows "Alabama, Alaska, Arizona, Arkansas..."). Now, if I accidentally select a state that I did not want, the only way I remove the unwanted word is to delete the whole cell and re-select all the states (tedious). If I try to delete the one state in the cell I will get an error saying "the value you entered is not valid. A user has restricted values that can be entered into this cell." Is there a way if I can select the state again from the drop down list and it will remove from the cell? Or if there are other options I can see if I can work around it but I would like some feedback.


    Macro enabling multiple selections in a drop down list to be placed in one cell
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String
    Dim Newvalue As String
    Application.EnableEvents = True
    On Error GoTo Exitsub
    If Target.Column = 2 Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
    Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
    If Oldvalue = "" Then
    Target.Value = Newvalue
    Else
    If InStr(1, Oldvalue, Newvalue) = 0 Then
    Target.Value = Oldvalue & ", " & Newvalue
    Else:
    Target.Value = Oldvalue
    End If
    End If
    End If
    End If
    Application.EnableEvents = True
    Exitsub:
    Application.EnableEvents = True
    End Sub

  2. #2
    Registered User
    Join Date
    06-02-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    51

    Re: Drop down list that removes entries once clicked

    Can you post your workbook?

  3. #3
    Registered User
    Join Date
    05-16-2018
    Location
    USA
    MS-Off Ver
    2010
    Posts
    4

    Re: Drop down list that removes entries once clicked

    I have attached a sample worksheet.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Dynamic drop list which removes options that are already chosen
    By tigfur in forum Excel General
    Replies: 24
    Last Post: 04-05-2016, 10:43 AM
  2. Display drop list when sell is clicked
    By brainzlp in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-23-2016, 08:49 PM
  3. Replies: 3
    Last Post: 09-16-2015, 12:45 AM
  4. (SOLVED) Drop down list removes used cells
    By SubwAy in forum Excel General
    Replies: 5
    Last Post: 10-22-2011, 01:56 PM
  5. Allow Blank entries in Drop Down list
    By eyeball11 in forum Excel General
    Replies: 2
    Last Post: 05-13-2009, 01:05 PM
  6. 'same entries':Drop Down List
    By warburger in forum Excel General
    Replies: 2
    Last Post: 08-05-2006, 02:35 PM
  7. [SOLVED] How many entries can be put into a drop down list in excel?
    By Mary A in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-26-2006, 04:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1