I have made a drop down menu (data Validation) I would like to select multiple selections from the drop down menu
Isso é possível, embora eu precise do VBA para fazer isso.
O código que estou usando atualmente é:
Private Sub Worksheet_Change(ByVal Target As Range)
' Vars to store the contents the cell which we are changing.
Dim oldVal As String
Dim newVal As String
' In range
Dim rngDV As Range
' We do not even try to handle multiple cells at the same time.
If Target.Count > 1 Then GoTo exitHandler
' We are called because a single cell is getting changed.
' If it is a drop down list then we want to act. If not skip dropdown code all the way to exit)
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
' rngDv is filled with Cells.SpecialCells(xlCellTypeAllValidation)
' That can be empty. In which case skip to end.
If rngDV Is Nothing Then GoTo exitHandler
' Check if our cell is in range in a complicated way.
' If not insersect might be cleaner. Then I can remove the else.
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
' OK, we are changing one of the drop down lists in our range.
' Block new changes until we have finished.
Application.EnableEvents = False
' Excel has already written the new value to the cell.
' This new value will replace our own content.
' Three options here:
' 1) We are adding a value for the first time.
' 2) We already have a value, opened drop down and did not select anything
' 3) We had a value and select a new option -> Action needed to APPEND rather than REPLACE
' Rescue the new value in a string
newVal = Target.Value
' We also want the old value, bt atm it is already gone. Lets do a generic undo to get it back.
Application.Undo
oldVal = Target.Value
' Re-replace with new content.
' If we are in range for our special actions we do will do this it again later.
' If we are not in in our special section and do not replace then we effective
' block all changes. That is not desired.
Target.Value = newVal
' Are we in range of [BC,BJ]?
' (A-Z = 1-26, AA-AZ is 27,52, ...
'
If (Target.Column >= 55 And Target.Column 2) Then
If (oldVal = "" Or newVal = "") Then
'Case 1 or case 2 as mentioned above
Else
' Finally, actual action code!
' Returns an integer specifying the start position of the first occurrence of one string within another.
' https://msdn.microsoft.com/en-us/library/8460tsh1%28v=vs.90%29.aspx
'
' We are going to use this for a mode 1 (text) comparision between the old cell content and the new entry.
' If the new entry is already somewhere in the old string then instr returns its position.
'
' Creatively use this to check if the value is present (we do not care WHERE in the string).
'
If (InStr(1, oldVal, newVal)) > 0 Then
' If we already have the same aswer in our cell.
' Instead of appending it and thus adding it a second time we will remove it.
' This allows use the easily remove a selection from a list, even if we already have a long list of entries.
' https://msdn.microsoft.com/en-us/library/dxs6hz0a%28v=vs.90%29.aspx
' Right returns a string which we ca compare with.
If Right(oldVal, Len(newVal)) = newVal Then
Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
Else
' Replace ( string1, find, replacement, [start, [count, [compare]]] )
'
' Search in the string with the old answers.
' for "new value, "
' and remove it by replacing it with nothing.
'
Target.Value = Replace(oldVal, newVal & ", ", "")
End If
Else
' Append the new change. Add a comma as cvs like separator.
Target.Value = oldVal & ", " & newVal
End If
End If
End If
' end of If Intersect(Target, rngDV)
End If
exitHandler:
Application.EnableEvents = True
End Sub
É minha primeira tentativa no VBA e alguns valores são bastante codificados.
(Por exemplo, If (Target.Column >= 55 And Target.Column <= 62 And Target.Row > 2) para selecionar o intervalo em que estou atuando).
Isso efetua a concatenação de todas as seleções em uma célula, separadas por vírgula. Se você quiser agir com base nesses dados, será necessário torná-los tokens novamente ou usar instruções if para abranger todas as combinações possíveis.
and display the values in cells below.
Você certamente pode copiar uma célula. O código atual combina todas as seleções (e remove uma se você selecioná-lo duas vezes) e coloca isso como uma string em uma célula. Essa string pode ser copiada para qualquer local usando a funcionalidade padrão do Excel.