Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
monaahmed2012
 | Femeie
25 ani
Calarasi
cauta Barbat
25 - 46 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
VBA code source:
1. Create your data validation list - I created mine dynamic, see here:
IMPORTANT: The codes below must be put in the worksheet's code (not in a Module).
2. VBA Code to allow Multiple Selections in a Drop-down List (without repetition)
A lot of people have been asking about the code to select multiple items from a drop-down list without repetition.
Here is the code that will make sure an item can only be selected once so that there are no repetitions:
Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from trumpexcel
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$C$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 |
3. VBA Code to allow Multiple Selections in a Drop-down List (with repetition)
Below is the Excel VBA code that will enable us to select more than one item from the drop-down list (allowing repetitions in selection):
Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from trumpexcel
' To make mutliple selections in a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
On Error GoTo Exitsub
If Target.Address = "$C$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
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub |
Modificat de TRaP (acum 4 ani)
|
|
| pus acum 4 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Q: In the VBA code, the functionality is for cell C2 only. How do I get it for other cells?
Ans: To get this multiple selection drop-down in other cells, you need to modify the VBA code in the backend. Suppose you want to get this for C2, C3, and C4, you need to replace the following line in the code:
If Target.Address = "$C$2" Then
with this line:
If Target.Address = "$C$2" Or Target.Address = "$C$3" Or Target.Address = "$C$4" Then
*****
Q: I need to create multiple drop-downs in entire column 'C'. How do I get this for all the cells in the columns with multi-select functionality?
Ans: To enable multiple selections in drop-downs in an entire column, replace the following line in the code:
If Target.Address = "$C$2" Then
with this line:
If Target.Column = 3 Then
On similar lines, if you want this functionality in column C and D, use the below line:
If Target.Column = 3 or Target.Column = 4 Then
*****
Q: I need to create multiple drop-downs in a row. How can I do this?
Ans: If you need to create drop-down lists with multiple selections in a row (let's say the second row), you need to replace the below line of code:
If Target.Address = "$C$2" Then
with this line:
If Target.Row = 2 Then
Similarly, if you want this to work for multiple rows (let's say second and third row), use the below line of code instead:
If Target.Row = 2 or Target.Row = 3 Then
*****
Q: As of now, the multiple selections are separated by a comma. How can I change this to separate these with space (or any other separator).
Ans: To separate these with a separator other than a comma, you need to replace the following line of VBA code:
Target.Value = Oldvalue & ", " & Newvalue
with this line of VBA code:
Target.Value = Oldvalue & " " & Newvalue
Similarly, if you want to change comma with other character, such as |, you can use the following line of code:
Target.Value = Oldvalue & "| " & Newvalue
*****
Q: Can I get each selection in a separate line in the same cell?
Ans: Yes you can. To get this, you need to replace the below line of VBA code:
Target.Value = Oldvalue & ", " & Newvalue
with this line of code:
Target.Value = Oldvalue & vbNewLine & Newvalue
vbNewLine inserts a new line in the same cell. So whenever you make a selection from the drop-down, it will be inserted in a new line.
****
Q: Can I make the multiple selection functionality work in a protected sheet?
Ans: Yes you can.
To get this done, you need to do two things:
Add the following line in the code (right after the DIM statement):
Me.Protect UserInterfaceOnly:=True
Second, you need to make sure the cells - that have the drop-down with multiple selection functionality - are not locked when you protect the entire sheet.
|
|
| pus acum 4 ani |
|