¿Cómo cargar y mostrar en listbox columnas con valores en formato de moneda?
Necesito de su valiosa ayuda para hacer que esta macro cargue en la columna 4 y 12 del listbox los valores en formato de moneda, gracias por su ayuda, saludos.
Private Sub UserForm_Initialize()
Dim lr As Long
Set sh = Sheets("compras")
lr = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row
a = sh.Range("A1", sh.Cells(lr, _
sh.Cells(2, Columns.Count).End(1).Column)).Value
With ListBox1
.ColumnCount = UBound(a, 2)
.ColumnWidths = "70PT;40PT;40PT;40PT;100PT;60PT;30PT;40PT;60PT;60PT;60PT;50PT;200PT;100PT;200PT;40PT;70PT;460PT;60PT;70PT;40PT;60PT;100PT;100PT;120PT;170PT;60PT;60PT;60PT;60PT;120PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT"
.List = a
End With
End Sub
1 Respuesta
Prueba esto:
Private Sub UserForm_Initialize()
Dim sh As Worksheet
Dim lr As Long
Dim a As Variant
Dim i As Long
'
Set sh = Sheets("compras")
lr = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row
a = sh.Range("A1", sh.Cells(lr, sh.Cells(2, Columns.Count).End(1).Column)).Value
For i = 1 To UBound(a, 1)
a(i, 4) = Format(a(i, 4), "$#,##0.00")
a(i, 12) = Format(a(i, 12), "$#,##0.00")
Next
With ListBox1
.ColumnCount = UBound(a, 2)
.ColumnWidths = "70PT;40PT;40PT;40PT;100PT;60PT;30PT;40PT;60PT;60PT;60PT;50PT;200PT;100PT;200PT;40PT;70PT;460PT;60PT;70PT;40PT;60PT;100PT;100PT;120PT;170PT;60PT;60PT;60PT;60PT;120PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT;60PT"
.List = a
End With
End Sub
Te muestro una manera de ajustar el ancho de las columnas del listbox:
Private Sub UserForm_Initialize()
Dim sh As Worksheet
Dim lr As Long, lc As Long
Dim a As Variant
Dim i As Long
Dim ancho As String
'
Set sh = Sheets("compras")
lr = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row
lc = sh.Cells(2, Columns.Count).End(1).Column
a = sh.Range("A1", sh.Cells(lr, lc)).Value
For i = 1 To UBound(a, 1)
a(i, 4) = Format(a(i, 4), "$#,##0.00")
a(i, 12) = Format(a(i, 12), "$#,##0.00")
Next
'
sh.Cells.EntireColumn.AutoFit
For i = 1 To lc
ancho = ancho & Int(sh.Cells(1, i).Width + 3) & ";"
Next
'
With ListBox1
.ColumnCount = UBound(a, 2)
.ColumnWidths = ancho
.List = a
End With
End Sub
