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

p45cal,

Question: If "Completed" is selected from the list, can the other machines that are selected be cleared? For example if the user selects "#4, #5" and then they select "Completed", the cell will only display "Completed" not "#4, #5, Completed"?
 
You'd be happy with #4 and #5 reappearing in the Data validation dropdown lists for that column?
 
try attached.
 

Attachments

  • Multiselect_Sample7.xlsm
    24.6 KB · Views: 18
Hi again,

I added the following If statement to change the color of a shape is a machine is selected. This will give a visual of all available machines at a quick glance.

Code:
 If newVal = "" Then
  For Each stri In Split(oldVal, ", ")
    For Each shp In ActiveSheet.Shapes
        If shp.Name = stri Then shp.Fill.ForeColor.ObjectThemeColor = 2
    Next
  Next
  End If


  For Each shp In ActiveSheet.Shapes
    If shp.Name = newVal Then shp.Fill.ForeColor.RGB = RGB(255, 0, 0)
    Next

So if a machine is selected the shape turns red. If the machine is no longer selected (user clears the cell) the machine turns back to white. This works perfect unless the user does not clear the cell instead the select "Completed". So if the cell goes from #04, #05 and then goes to Completed, the shapes do not turn back to white, they stay red.

Attached is my sample for you to see.
 

Attachments

  • Multiselect _Sample9.xlsm
    31.8 KB · Views: 11
Solution to previous question:
Code:
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
     [COLOR=#ff0000][B] If newVal <> "" And newVal <> "Completed" Then Target.Value = oldVal & ", " & newVal[/B][/COLOR]
    End If


  If Not Intersect(Target, Range("B8:B16, D8:D16")) Is Nothing Then 'FirstWrap and sercondWrap, both Range("V3:V7")
    UpdateDVs Intersect(rngDV, Range("B8:B16, D8:D16")), Range("AE3:AE7")
  ElseIf Not Intersect(Target, Range("E8:E16")) Is Nothing Then  'Braiders Range("X3:X25")
    UpdateDVs Intersect(rngDV, Range("E8:E16")), Range("X3:X25")
  ElseIf Not Intersect(Target, Range("F8:F16")) Is Nothing Then  'Mylar Range("V3")
    UpdateDVs Intersect(rngDV, Range("F8:F16")), Range("V3")
  End If
  
  [COLOR=#ff0000][B]If newVal = "Completed" Or newVal = "" Then[/B][/COLOR]
  For Each stri In Split(oldVal, ", ")
    For Each shp In ActiveSheet.Shapes
        If shp.Name = stri Then shp.Fill.ForeColor.ObjectThemeColor = 2
    Next
  Next
  End If


  For Each shp In ActiveSheet.Shapes
    If shp.Name = newVal Then shp.Fill.ForeColor.RGB = RGB(255, 0, 0)
  Next
End If
 
Hi again,

So I just thought of a possible issue that you may be able to help me find a solution for. So the whole intent for this worksheet is to track what machine is available and how much time the machine is being used. If a person is running an order on two machines (#04, #05) to complete an order and then machine #04 is switched to a different order, the user has no way of removing just one machine without having to delete them both, and if they delete them both (clearing the cell) it will reset my method I'm using to track time.

I was thinking maybe there is a way for the user to have an option in the drop down that maybe says "Update Machine/s" and if they select that option a form will pop up allowing the user to select the new machines or machine that need to be displayed in the cell without clearing out the cell and resetting my timer method.

This may be easier to fix by changing my timer method.

I'm just shooting out ideas. I really do like the first option but I'm not sure how possible it is.

Feedback and solutions are greatly appreciated!

Thanks again!
 
So the whole intent for this worksheet is to track what machine is available and how much time the machine is being used. If a person is running an order on two machines (#04, #05) to complete an order and then machine #04 is switched to a different order, the user has no way of removing just one machine without having to delete them both, and if they delete them both (clearing the cell) it will reset my method I'm using to track time.
In your other thread, using my suggestion for tracking time: "Changing a non-empty cell in column B to a different value (including deleting it) will not stop the timer, only entering 'complete' will."
 
Hi p45cal,

What is I wanted to use "Sample Completed" only on one of the ranges?

For example: The drop down for one of my ranges will have "Sample Completed" but none of the other ranges will have that option.
 
Back
Top