Remove Name From Drop Down After Its's Selected, Office 2013

Justair07

New member
Joined
Dec 10, 2015
Messages
31
Reaction score
0
Points
0
Hello,

I'm using VBA to allow me to select multiple items from a data validation drop down list. What I would like to do is remove items from my list if something is already selected so it cant be selected twice. I know how to do this from a regular drop down but the multi select makes this impossible for me to figure out on my own.

The multiple selection looks like this in the cell: Justin, Brian, Chris

There is a comma and a blank space between the selections. So basically if i selected these three names I would no longer want those three names to be available. Then once the cell is cleared out, the names can be selected again (added back to the list).

I'm also using a method to refresh my data range as items from my list are removed.

It works great, I just need help with the VBA to remove the items in the list as they are selected and then replace them back to the list as they are no longer selected or being used.

Sample attached

Thank you I'm sure this is not easy so any help is very much appreciated.

- Justin
 

Attachments

  • Multiselect _Sample.zip
    12.5 KB · Views: 14
See if the attached does anything for you.
 

Attachments

  • Multiselect _Sample2.xlsm
    16.1 KB · Views: 29
Oh groan…

cross posted:
http://www.utteraccess.com/forum/index.php?showtopic=2033500
http://www.mrexcel.com/forum/excel-questions/907853-remove-name-drop-down-after-itss-selected.html
https://social.msdn.microsoft.com/F...fter-itss-selected-office-2013?forum=exceldev

Justair07, for your information, you should always provide links to your cross posts.
If you have cross posted at other places, please add links to them too.
Why? Have a read of http://www.excelguru.ca/content.php?184

This is particularly galling as I can now see you had this solved yesterday.
I've just spent the last hour understanding your problem, having a go at solving it and posting it here.
All a chuffing waste of time (you might see a word beginning with f where you see 'chuffing'; you might be right).
 
p45cal,

Great sub! Works a lot better than other solutions I've encountered. Is it wrong to share your solution since I crossposted. I'd hate for anyone else to work on a solution when yours is perfect.
See if the attached does anything for you.
 
Is it wrong to share your solution since I crossposted.
As far as I'm concerned, no it's not wrong - all these forums/threads are in the public domain. It might be courtesy to attribute the solution, and certainly it would be good to link to that solution.



I'd hate for anyone else to work on a solution…
That's a slightly different matter, that's what the cross posting rules, that most forums have, are all about - provide links to the relevant threads at all forums to all the other forums/threads, so that anyone considering your problem for you can check it hasn't already been satisfactorily addressed elsewhere; leave it up to any potential solution provider to decide for himself whether to supply what he thinks might be a better solution for you.
I can promise you, that all contributors will quickly ignore your requests for help if you repeatedly cross post without links, simply because (a) they don't want to have their time wasted and (b) they really can't be bothered to go hunting around the internet for your cross posts. But all of that is explained in the link I provided earlier.
 
Last edited:
Thanks again for the help.

I noticed that your code is using the split function for a range E4. This limits me from having the same functionality on multiple drop downs. Is there an easy fix?

Edit: Is it possible to modify your code so that the transpose does not add anything that is being used by a number of drop downs?

For example, if one of my drop downs says Justin, Marian and then I update another drop down to say Brian, the list refreshes with Justin and Marian even though they are being used in one of the drop downs still.

I hope my question makes sense.

Code:
Sub blah()


dvlist = Application.Transpose(Range("A2:A8").Value)
chosen = Split(Application.ActiveCell, ",")
For Each nme In chosen
  dvlist = Filter(dvlist, Trim(nme), False, vbTextCompare)
Next nme
Range("C2:C8").ClearContents
Range("C2").Resize(UBound(dvlist) - LBound(dvlist) + 1) = Application.Transpose(dvlist)
End Sub
 
Last edited:
In the attached find multiple data validation cells.
At the moment all data validation cells will be become based on cells A2:A8 but that can be tweaked.
No checks for type of validation in cells etc but that too can be tweaked. Just proof of concept.
 

Attachments

  • Multiselect _Sample3.xlsm
    18.8 KB · Views: 8
In the attached find multiple data validation cells.
At the moment all data validation cells will be become based on cells A2:A8 but that can be tweaked.
No checks for type of validation in cells etc but that too can be tweaked. Just proof of concept.
My apologizes, I asked my question wrong (thats what happens when I work too long of hours).
I was hoping that all dropdown list would work in conjunction with each other.

For example: If one list says "Justin, Marian" the other lists will not all Justin or Marian to be selected again. Basically all the dropdowns will be based on the same range of names.


or example, if one of my drop downs says Justin, Marian and then I update another drop down to say Brian, the list refreshes with Justin and Marian even though they are being used in one of the drop downs still.

^ Wrong, I actally didn't want Justin, Marian available if its used already in another drop down.

Sorry for the confusion and thank you for all of the help.
 
gets its knickers in a twist if you try to delete part of a DV cell, so restrict to deleting the entire cell content.
 

Attachments

  • Multiselect _Sample4.xlsm
    19.7 KB · Views: 12
Works Great! Thank you. Furthermore I added to the code so I can have separate dropdowns using different ranges. Works a little bit only the big issue I'm having is that the range in the dropdown is not correct until after i make a selection. For example, if I select a dropdown for braiders, then select a dropdown for wrappers, the deopdown for wrappers has the range for the braiders. Only after I select a value from the list does the correct range appear in the wrap list. I hope this makes sense.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler


On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler


If rngDV Is Nothing Then GoTo exitHandler


If Not Intersect(Target, rngDV) Is Nothing Then
  Application.EnableEvents = False
  newVal = Target.Value
  Application.Undo
  oldVal = Target.Value
  Target.Value = newVal
  If oldVal <> "" Then
    If newVal <> "" Then Target.Value = oldVal & ", " & newVal
  End If
  If Not Intersect(Target, Range("B8:B16")) Is Nothing Then
  FirstWrap rngDV
  ElseIf Not Intersect(Target, Range("C8:C16")) Is Nothing Then
  SecondWrap rngDV
  ElseIf Not Intersect(Target, Range("D8:D16")) Is Nothing Then
  Braiders rngDV
  ElseIf Not Intersect(Target, Range("E8:E16")) Is Nothing Then
  Mylar rngDV
  End If
End If
exitHandler:
Application.EnableEvents = True
End Sub

Sub FirstWrap(dvcells)
For Each cll In dvcells.Cells
  mynames = mynames & "," & cll.Value
Next cll
chosen = Split(mynames, ",")
dvlist = Application.Transpose(Range("V3:V7").Value)
For Each nme In chosen
  vv = Application.Match(Trim(nme), dvlist, 0)
  If Not IsError(vv) Then dvlist = Filter(dvlist, Trim(nme), False, vbTextCompare)
Next nme
dvlist = Join(dvlist, ",")
If dvlist = "" Then dvlist = " "
For Each cll In dvcells.Cells
  cll.Validation.Modify Formula1:=dvlist  'Join(dvlist, ",")
Next cll
End Sub

Sub SecondWrap(dvcells)
For Each cll In dvcells.Cells
  mynames = mynames & "," & cll.Value
Next cll
chosen = Split(mynames, ",")
dvlist = Application.Transpose(Range("V4:V7").Value)
For Each nme In chosen
  vv = Application.Match(Trim(nme), dvlist, 0)
  If Not IsError(vv) Then dvlist = Filter(dvlist, Trim(nme), False, vbTextCompare)
Next nme
dvlist = Join(dvlist, ",")
If dvlist = "" Then dvlist = " "
For Each cll In dvcells.Cells
  cll.Validation.Modify Formula1:=dvlist  'Join(dvlist, ",")
Next cll
End Sub

Sub Mylar(dvcells)
For Each cll In dvcells.Cells
  mynames = mynames & "," & cll.Value
Next cll
chosen = Split(mynames, ",")
dvlist = Application.Transpose(Range("V3").Value)
For Each nme In chosen
  vv = Application.Match(Trim(nme), dvlist, 0)
  If Not IsError(vv) Then dvlist = Filter(dvlist, Trim(nme), False, vbTextCompare)
Next nme
dvlist = Join(dvlist, ",")
If dvlist = "" Then dvlist = " "
For Each cll In dvcells.Cells
  cll.Validation.Modify Formula1:=dvlist  'Join(dvlist, ",")
Next cll
End Sub

Sub Braiders(dvcells)
For Each cll In dvcells.Cells
  mynames = mynames & "," & cll.Value
Next cll
chosen = Split(mynames, ",")
dvlist = Application.Transpose(Range("X3:X25").Value)
For Each nme In chosen
  vv = Application.Match(Trim(nme), dvlist, 0)
  If Not IsError(vv) Then dvlist = Filter(dvlist, Trim(nme), False, vbTextCompare)
Next nme
dvlist = Join(dvlist, ",")
If dvlist = "" Then dvlist = " "
For Each cll In dvcells.Cells
  cll.Validation.Modify Formula1:=dvlist  'Join(dvlist, ",")
Next cll
End Sub
 

Attachments

  • Multiselect_Sample5.xlsm
    24.7 KB · Views: 11
in sub Mylar, is
dvlist = Application.Transpose(Range("V3").Value)
meant to be just 1 cell?
 
Yes, only one machine to choose from if available depending on other list selections.

I think I have it figured out.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)


Dim rngDV As Range
Dim firstWrapRange As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler
Dim shp As Shape


On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler


If rngDV Is Nothing Then GoTo exitHandler


If Not Intersect(Target, rngDV) Is Nothing Then
  Application.EnableEvents = False
  newVal = Target.Value
  Application.Undo
  oldVal = Target.Value
  Target.Value = newVal
  If oldVal <> "" Then
    If newVal <> "" Then Target.Value = oldVal & ", " & newVal
  End If
  If Not Intersect(Target, Range("B8:B16")) Is Nothing Then
  FirstWrap Target, Range("B8:B16")
  ElseIf Not Intersect(Target, Range("C8:C16")) Is Nothing Then
  SecondWrap Target
  ElseIf Not Intersect(Target, Range("D8:D16")) Is Nothing Then
  Braiders Target
  ElseIf Not Intersect(Target, Range("E8:E16")) Is Nothing Then
  Mylar Target
  End If
  
End If
exitHandler:
Application.EnableEvents = True
End Sub
Sub FirstWrap(dvcells, ByVal tvcells As Range)
For Each cll In tvcells.Cells
  mynames = mynames & "," & cll.Value
Next cll
chosen = Split(mynames, ",")
dvlist = Application.Transpose(Range("V3:V7").Value)
For Each nme In chosen
  vv = Application.Match(Trim(nme), dvlist, 0)
  If Not IsError(vv) Then dvlist = Filter(dvlist, Trim(nme), False, vbTextCompare)
Next nme
dvlist = Join(dvlist, ",")
If dvlist = "" Then dvlist = " "
For Each cll In tvcells.Cells
  cll.Validation.Modify Formula1:=dvlist  'Join(dvlist, ",")
Next cll
End Sub
 
It should work, but a few points:
1. you never use Target(=dvcells) in FirstWrap, so you don't need to pass it.
2. If there were any cells in B8:B16 that didn't have DV you'd be screwed, but since they're all DV cells that's fine.
3. Do you not still get a problem with the last remaining entry in a DV list refusing to diappear?
4. mylar not updating at all.

I'm working on a similar solution but aim at removing the problems above.
 
Last edited:
The attached addresses all 4 points in my previous post.
Just one thing I don't have time to do is cope with refreshing the DVList when an entire column of DVcells is cleared at once (eg. by selecting several cells and pressing Delete on the keyboard), it gets very convoluted. Workaround is to delete all the cells but to follow it by deleting a single cell (even if it already has nothing in it!)
 

Attachments

  • Multiselect_Sample5.xlsm
    24.7 KB · Views: 8
The attached addresses all 4 points in my previous post.
Just one thing I don't have time to do is cope with refreshing the DVList when an entire column of DVcells is cleared at once (eg. by selecting several cells and pressing Delete on the keyboard), it gets very convoluted. Workaround is to delete all the cells but to follow it by deleting a single cell (even if it already has nothing in it!)
Awesome! Thank you very much and sorry for the late reply. Your code is much easier to manage! One issue I noticed however, when I select a number from one list (1st Wrap) the machine is still available in a different drop down list (2nd Wrap). Any thoughts on how to correct this? The idea is to avoid a number from being selected more than once?
 
Awesome! Thank you very much and sorry for the late reply. Your code is much easier to manage! One issue I noticed however, when I select a number from one list (1st Wrap) the machine is still available in a different drop down list (2nd Wrap). Any thoughts on how to correct this? The idea is to avoid a number from being selected more than once?

Also is there a way to add "Completed" to all the list and not have it disappear from the list if it is selected. For example, all drop downs should be able to have "Completed" selected simultaneously.

Thank again!
 
Don't forget to add links to your cross posts at ALL forums.
The attached file addresses 1st/2ndWrap and treats them as one, as well as including a Completed option at all times.
I see you have continued to let nmurray at UtterAccess continue to help you on this topic without informing him of goings on elsewhere - you can surely understand why people won't want to help again if they find you insist on not providing links - and I'm one of them. (I realise that UtterAccess have a rule forbidding cross-posting at all, but if you're up front with them, they will understand.)
 

Attachments

  • Multiselect_Sample6.xlsm
    24.5 KB · Views: 6
This might be sufficient:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
 
    On Error Resume Next
    If Target.Validation.Type = 3 Then Target.SpecialCells(-4175).Validation.Modify , , , Replace(Mid(Replace(";" & Target.Validation.Formula1, ";" & Target, ""), 2), ";", ",")
End Sub
 
Don't forget to add links to your cross posts at ALL forums.
The attached file addresses 1st/2ndWrap and treats them as one, as well as including a Completed option at all times.
I see you have continued to let nmurray at UtterAccess continue to help you on this topic without informing him of goings on elsewhere - you can surely understand why people won't want to help again if they find you insist on not providing links - and I'm one of them. (I realise that UtterAccess have a rule forbidding cross-posting at all, but if you're up front with them, they will understand.)
Brilliant! Thank you so much! - also I made sure to add reference at ustteraccess, thank you!
 
Back
Top