Changing color of individual item in a listbox

[bisel]

Greetings,

I have a user form that populates the names of all visible sheets in the workbook and then when the user double clicks a sheet name, then that sheet is selected and activated.

What I would like to do is to highlight the current active sheet in the listbox using either a different back color or fore color. I have been searching around and cannot find a answer ... in fact, my searching, so far, is leading to the conclusion that I may not be able to do this.

Here is the current code that is working ... notice that in the if statement testing if current sheet name = active sheet name, the if and else is same code. I commented in each case that I want different highlighting.

Private Sub UserForm_Initialize()

Dim Top As Double, Left As Double
Dim currentsheetname As String
Dim Sh As Variant

' Position this user form on screen

    Me.StartUpPosition = 0
    Top = Abs(Application.Top) + (Application.Height - ActiveWindow.Height) + (Application.UsableHeight - ActiveWindow.UsableHeight)
    Left = Abs(Application.Left) + (Application.Width) - (Me.Width + 200)
    Me.Top = Top
    Me.Left = Left

' Get the name of the active sheet
    currentsheetname = activesheet.Name

'for each loop the add visible sheets to the listbox
    For Each Sh In ActiveWorkbook.Sheets
        
        Select Case Sh.Visible
            Case Is = True
            If Sh.Name = currentsheetname Then
                Me.ListBox1.AddItem Sh.Name ' want to change color attributes of the list item if current active sheet
            Else
                Me.ListBox1.AddItem Sh.Name ' want to have no highlighting of all sheets that are not the current active sheet
            End If
        Case Else
        End Select
    
   Next Sh

End Sub

Appreciate any help.

Thanks,

Steve

 

[Fluff]

AFAIK, it's not possible to do that.

 

[Marcelo Branco]

I think the best you can do is to select the Item corresponding to Active Sheet

Private Sub UserForm_Initialize()

Dim Top As Double, Left As Double
Dim currentsheetname As String
Dim Sh As Variant

' Position this user form on screen

    Me.StartUpPosition = 0
    Top = Abs(Application.Top) + (Application.Height - ActiveWindow.Height) + (Application.UsableHeight - ActiveWindow.UsableHeight)
    Left = Abs(Application.Left) + (Application.Width) - (Me.Width + 200)
    Me.Top = Top
    Me.Left = Left

' Get the name of the active sheet
    currentsheetname = ActiveSheet.Name

'for each loop the add visible sheets to the listbox
    For Each Sh In ActiveWorkbook.Sheets
        
        Select Case Sh.Visible
            Case Is = True
            If Sh.Name = currentsheetname Then
                Me.ListBox1.AddItem Sh.Name  ' want to change color attributes of the list item if current active sheet
               [COLOR=#ff0000] Me.ListBox1.Value = Sh.Name
[/COLOR]            Else
                Me.ListBox1.AddItem Sh.Name ' want to have no highlighting of all sheets that are not the current active sheet
            End If
        Case Else
        End Select
    
   Next Sh

End Sub

M.