Hi Guys,
Appreciate your advice on the following as I am not a VBA expert.
HAVE:
- Excel Spreadsheet with 3 separate Data Validation (DV) Drop lists created in one column B
- VBA code which allows to select multiple text values and deselect individual text values in the same column B
- Selected multiple text values in DV lists are added in one line
- Used ChrW(9670) & Space(1) to separate any newly selected & added text values in one line
NEEDED:
1. Adjust VBA code to allow specific text to be presented in Bold and in different colors and even underlined in DV List in cell B6 and cell B8
E.g.: specific text like "CRITICAL TASK THRESHOLD" = Bold Red underlined;
and "MANDATORY", "ADDITIONAL", "SPECIALIZED"= Bold Blue underlined
2. To adjust VBA code allowing better visual text separation and avoiding extra space for the rest of text values in one line.
E.g.: right now when you select => deselect => select again the value in DV list the VBA code is adding extra space before the new value.
3. To adjust VBA code allowing to use of a better symbol/character or space as a text separator
E.g.: right now when you select a new value from DV list a Black Diamond character + space are added before the new value. With more selected text values the final result looks a bit congested by this Black Diamond character
Thanks in advance for your advice and guidelines.
----
Appreciate your advice on the following as I am not a VBA expert.
HAVE:
- Excel Spreadsheet with 3 separate Data Validation (DV) Drop lists created in one column B
- VBA code which allows to select multiple text values and deselect individual text values in the same column B
- Selected multiple text values in DV lists are added in one line
- Used ChrW(9670) & Space(1) to separate any newly selected & added text values in one line
NEEDED:
1. Adjust VBA code to allow specific text to be presented in Bold and in different colors and even underlined in DV List in cell B6 and cell B8
E.g.: specific text like "CRITICAL TASK THRESHOLD" = Bold Red underlined;
and "MANDATORY", "ADDITIONAL", "SPECIALIZED"= Bold Blue underlined
2. To adjust VBA code allowing better visual text separation and avoiding extra space for the rest of text values in one line.
E.g.: right now when you select => deselect => select again the value in DV list the VBA code is adding extra space before the new value.
3. To adjust VBA code allowing to use of a better symbol/character or space as a text separator
E.g.: right now when you select a new value from DV list a Black Diamond character + space are added before the new value. With more selected text values the final result looks a bit congested by this Black Diamond character
Thanks in advance for your advice and guidelines.
----
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Sp As Variant, n As Long, nstr As String
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("B:B")) Is Nothing 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 = ChrW(9670) & Space(1) & Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & Space(1) & ChrW(9670) & Space(1) & Newvalue
Else:
Sp = Split(Target.Value, ChrW(9670) & Space(1) & Newvalue)
For n = 0 To UBound(Sp)
If Not Sp(n) = Newvalue Then
nstr = nstr & IIf(nstr = Sp(n), Space(1), Sp(n))
End If
Next n
Target.Value = nstr: nstr = Space(0)
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
____________________
