Excel - Style format on drop down list selection

Question

I'm working on a excel document being generated with APACHE POI. The document is filled with many drop down lists for data validation. The data chosen in those drop down lists are always of the same type:

LABEL (ID)

For the person who fills the excel document, the ID is less important than the LABEL _ but the ID is still necessary for parsing purposes. I managed through APACHE POI to put a specific format on those kind of cells, in order to help the user to focus on the information more useful to him/her :

LABEL is in black
(ID) is in grey

My problem: when the user change a value in the cell throught the drop down list, the style format is lost on the cell.

My question: is it possible to set up a listener on my excel document that does the folowing job:

on ANY cell
filled through ANY drop down list
on ANY sheet of the workbook
set the specified cell format ?

I already have a function that does the "style format" job, but I don't know how to plug it on this kind of listener...

Function formatStyle()

    Dim cellContent As String
    Dim valeurLength As Integer
    For Each currentCell In Selection.Cells

        cellContent = currentCell.Value

        For valeurLength = 1 To Len(cellContent)
           If Mid(cellContent, valeurLength, 1) = "(" Then
              Exit For
           End If
        Next valeurLength
        With currentCell.Characters(Start:=1, Length:=valeurLength - 1).Font
            .ThemeColor = xlThemeColorLight1
             .TintAndShade = 0
        End With
        With currentCell.Characters(Start:=valeurLength, Length:=Len(cellContent) - valeurLength + 1).Font
             .ThemeColor = xlThemeColorDark1
             .Color = -4144960
        End With
    Next
End Function

look at `Worksheet_Change()` subs – CallumDA Mar 24 '17 at 10:46 — CallumDA, Mar 24 '17 at 10:46

score 0 · Answer 1 · answered Mar 24 '17 at 19:11

Excel Form controls don't support any kind of font and color formatting. ActiveX controls let you change the font and colors, but not of individual characters. Custom drawing parts of the control most likely can be achieved with some complicated VBA and WinAPI calls.

The closest alternative I can think of is some of the bold extended Unicode characters:

score 0 · Answer 2 · edited May 23 '17 at 12:17

Thanks to Determine if cell contains data validation, I've managed to do exactly what I wanted:

Private Sub Workbook_SheetChange(ByVal Sh As Object,ByVal Target As Range)
    Dim cell As Range, v As Long
    For Each cell In Selection.Cells
        v = 0
        On Error Resume Next

        v = cell.SpecialCells(xlCellTypeSameValidation).Count
        On Error GoTo 0    
        If v <> 0 Then
            formatReferenceCell (Target)
        End If
    Next
End Sub



Function formatReferenceCell(cellContent)
    Dim X As Integer   
    For X = 1 To Len(cellContent)
       If Mid(cellContent, X, 1) = "(" Then
          Exit For
       End If
    Next X
    With ActiveCell.Characters(Start:=1, Length:=X - 1).Font
        .ThemeColor = xlThemeColorLight1
         .TintAndShade = 0
    End With
    With ActiveCell.Characters(Start:=X, Length:=Len(cellContent) - X + 1).Font
         .ThemeColor = xlThemeColorDark1
         .Color = -4144960
    End With
End Function

Excel - Style format on drop down list selection

2 Answers2