Combobox decimal

[Ruca13]

Hello everyone.

Can somebody please help understand what's going wrong with the line of code that's suppose to format the combobox to show only 3 decimal places? It is as if that line of code does not exist at all, since the result of having it or not it's the same.

The userform code follows below:

Private Sub UserForm_Activate()
    With UserForm1
        .Top = Application.Top + 15 '< change 125 to what u want
        .Left = Application.Left + 600 '< change 25 to what u want
    End With
End Sub
 Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = 0 Then
    Cancel = True
  End If
End Sub
Private Sub UserForm_Initialize()


'Load both combobox w/ cell values
Me.ComboBoxPrice.List = Worksheets("temp filter").Range(Cells(2, 10), Cells(2, 10).End(xlDown)).Value
Me.ComboBoxPrice.Value = Format(Me.ComboBoxPrice.Value, "#.000")
Me.ComboBoxSupplier.List = Worksheets("temp filter").Range(Cells(2, 12), Cells(2, 12).End(xlDown)).Value


End Sub
Private Sub comboboxPrice_change()


'Select sheet
If ActiveSheet.Name = "temp filter" Then
ActiveSheet.Previous.Activate
End If


'Force CB2 value = CB1 list index #
ComboBoxSupplier.ListIndex = ComboBoxPrice.ListIndex


End Sub
Private Sub comboboxSupplier_change()


'Select sheet
If ActiveSheet.Name = "temp filter" Then
ActiveSheet.Previous.Activate
End If


'Force CB1 value = CB2 list index #
ComboBoxPrice.ListIndex = ComboBoxSupplier.ListIndex


End Sub
Private Sub commandbuttonOK_click()


ActiveCell.Value = ComboBoxPrice.Value
Cells(ActiveCell.Row, ActiveCell.Column + 1).Value = ComboBoxSupplier.Value


Unload Me


End Sub
Private Sub commandbuttonCancel_click()


Unload Me
If ActiveSheet.Name = "temp filter" Then
ActiveSheet.Previous.Activate
End If
End Sub

Thank you for your help.

 

[Jerry Sullivan]

Hi Ruca13,

Your Me.ComboBoxPrice.Value = statement below doesn't assign the formatted value to each item in the ComboBox.

'Load both combobox w/ cell values
Me.ComboBoxPrice.List = Worksheets("temp filter").Range(Cells(2, 10), Cells(2, 10).End(xlDown)).Value
Me.ComboBoxPrice.Value = Format(Me.ComboBoxPrice.Value, "#.000")

Instead it says that the Value of the Combobox should be the current value of the Combobox (which is blank at that point), formatted as "#.000" (which is still blank).

If that value matched an item in your Combobox list, then the code would Select that item. Since it doesn't- it just puts that value in the TextBox part of the ComboBox.

Try this code to confirm that...

'Load both combobox w/ cell values
Me.ComboBoxPrice.List = Worksheets("temp filter").Range(Cells(2, 10), Cells(2, 10).End(xlDown)).Value
Me.ComboBoxPrice.Value = "XXX"

To format each item in the list you could loop through them using an array...

Sub PopulateCombo()
 Dim vPrices As Variant
 Dim lNdx As Long
 
 vPrices = Worksheets("temp filter").Range(Cells(2, 10), Cells(2, 10).End(xlDown)).Value
 
 For lNdx = 1 To UBound(vPrices, 1)
   vPrices(lNdx, 1) = Format(vPrices(lNdx, 1), "#0.000")
 Next lNdx
 Me.ComboBoxPrice.List = vPrices
 
End Sub

Or use the Evaluate method like this...

Sub PopulateCombo2()

 Dim sAddress As String
 
 With Sheets("temp filter")
   sAddress = Range(.Cells(2, 10), .Cells(2, 10).End(xlDown)).Address(0, 0, , 1)
   Me.ComboBoxPrice.List = .Evaluate("IF(LEN(" & sAddress & "),TEXT(" & sAddress & ",""#0.000""),"""")")
      
 End With
End Sub

 

[Ruca13]

Thank you for your explanation.

I've opted for the Evaluate option, since I've seen many users talk about it in the forum but I've never had the chance to use it, so now it will be a good time to understand it.

Once again, thank you for your help.