Combobox Column Formatting

[DFragnDragn]

Hello,

I've extended my sheet range formatting to a combobox column list indexes.
All work as expected, except for column 4 where a list separator or ) is expected.
I've futzed with this on several occasions and I'm just not getting it.

Any ideas greatly appreciated.

Option Explicit


Sub FormatListIndex()
    On Error Resume Next
    ActiveSheet.Range("L55").Value = ActiveSheet.Range("L55") + 1
    Dim i As Integer
    For i = 1 To ActiveSheet.Range("L55").Value
        If i > 0 Then Application.ScreenUpdating = False
        ActiveSheet.ComboBox1.ListIndex = i
        ActiveSheet.ComboBox1.Column(0) = Format(ActiveSheet.ComboBox1.Column(0), "mm-dd-yy")
        ActiveSheet.ComboBox1.Column(1) = Format(ActiveSheet.ComboBox1.Column(1), "#,###,##0.00")
        ActiveSheet.ComboBox1.Column(2) = Format(ActiveSheet.ComboBox1.Column(2), "#,###,##0.00")
        ActiveSheet.ComboBox1.Column(3) = Format(ActiveSheet.ComboBox1.Column(3), "#,###,##0.00")
        
        ActiveSheet.ComboBox1.Column(4) = Format(ActiveSheet.ComboBox1.Column(4), "#,#[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0_]#0_[/URL] );"("#,##0")";"? ? ?"_)
        
    Next
    ActiveSheet.ComboBox1.ListIndex = 0
End Sub

 

[DFragnDragn]

'Oops, I had an errant space in there.

Sub FormatListIndex()
    On Error Resume Next
    ActiveSheet.Range("L55").Value = ActiveSheet.Range("L55") + 1
    Dim i As Integer
    For i = 1 To ActiveSheet.Range("L55").Value
        If i > 0 Then Application.ScreenUpdating = False
        ActiveSheet.ComboBox1.ListIndex = i
        ActiveSheet.ComboBox1.Column(0) = Format(ActiveSheet.ComboBox1.Column(0), "mm-dd-yy")
        ActiveSheet.ComboBox1.Column(1) = Format(ActiveSheet.ComboBox1.Column(1), "#,###,##0.00")
        ActiveSheet.ComboBox1.Column(2) = Format(ActiveSheet.ComboBox1.Column(2), "#,###,##0.00")
        ActiveSheet.ComboBox1.Column(3) = Format(ActiveSheet.ComboBox1.Column(3), "#,###,##0.00")
        
        ActiveSheet.ComboBox1.Column(4) = Format(ActiveSheet.ComboBox1.Column(4), "#,##0_ );"("#,##0")";"? ? ?"_)
        
    Next
    ActiveSheet.ComboBox1.ListIndex = 0
End Sub

 

[RoryA]

You need to double up any quotes in the format string (although you don't actually need any here), and note that Format doesn't understand the concept of leaving the width of a specific character. My best guess is you want this:

 ActiveSheet.ComboBox1.Column(4) = Format(ActiveSheet.ComboBox1.Column(4), "#,##0 ;(#,##0);? ? ?")

 

[DFragnDragn]

I hit my thumb & you nailed RoryA.
Thank You.

 

[RoryA]

Glad to help.