Format ActiveX Listbox for Currency

[oldman]

This is a worksheet/ActiveX multi-column (6)listbox.

The listbox source is: "Me.lb1.List = Worksheets("POP").Cells(1, 1).CurrentRegion.Value"

The two far right columns need to be formated for American currency ($) and/or as a
number such as "####.##"

I have used "listfillrange" range in the past and was able to obtain the desired results; however, I have been advised not to use this method.

Module for populating the listbox:


Private Sub csrc_Click() 'Finds items based upon user criteria
Application.ScreenUpdating = False
Dim ws As Worksheet '
Set ws = Worksheets("SKU LineUp")
Dim vbnulstring
ws.Unprotect
Dim Rang As Long
Dim OleObj As OLEObject
Dim x As Integer

'check that at least one box has data
For Each OleObj In ActiveSheet.OLEObjects
If OleObj.progID = "Forms.TextBox.1" Then
If Right(OleObj.Name, 1) > 0 And Right(OleObj.Name, 1) < 4 Then
If OleObj.Object.Value <> "" Then
x = x + 1
End If
End If
End If
Next OleObj

If x = 0 Then 'all boxes are empty
MsgBox "Minimum of 1 Criteria Required", vbOKOnly, "Search Error!"
Exit Sub
End If

ws.AutoFilterMode = False
ws.Range("b1:g1").AutoFilter Field:=1, Criteria1:="*" & TextBox1.Text & "*"
ws.Range("b1:g1").AutoFilter Field:=2, Criteria1:="*" & TextBox2.Text & "*"
ws.Range("b1:g1").AutoFilter Field:=3, Criteria1:="*" & textbox3.Text & "*"
With ws.AutoFilter.Range.Offset(1, 0)
.Copy Sheets("Pop").Range("b1")

End With

Me.lb1.List = Worksheets("POP").Cells(1, 1).CurrentRegion.Value

End Sub


Module for populating worksheet from listbox select:


Private Sub lb1_Click() 'Listbox selection to first available row and calculates data. Courtesy of SNB
With Sheets("sheet2").Cells(Rows.Count, 4).End(xlUp).Offset(1)
.Resize(, 7) = Array(lb1.List(lb1.ListIndex, 1), "", lb1.List(lb1.ListIndex, 3), lb1.List(lb1.ListIndex, 5), _
"=" & .Offset(, 1).Address & "*" & .Offset(, 3).Address, lb1.List(lb1.ListIndex, 6), "=" & .Offset(, 1).Address & "*" & .Offset(, 5).Address)

End With

lb1.Clear

End Sub

[snb]

Private Sub lb1_Click() 'Listbox selection to first available row and calculates data. Courtesy of SNB
with Sheets("sheet2").Cells(Rows.Count, 4).End(xlUp).Offset(1)
.Resize(, 7) = Array(lb1.List(lb1.ListIndex, 1), "", lb1.List(lb1.ListIndex, 3), format(lb1.List(lb1.ListIndex, 5),"currency"),"=" & .Offset(, 1).Address & "*" & .Offset(, 3).Address, format(lb1.List(lb1.ListIndex, 6),"currency"), "=" & .Offset(, 1).Address & "*" & .Offset(, 5).Address)
End With
End Sub

[oldman]

This will ensure the data from the listbox to the worksheet is in the currency format. Unfortunately, when tested, it removed the currency and two decimal format from the worksheet cells.

What I am in need of is the last two columns in the listbox to display in currency or at the very least a two decimal format such as "#####.##"

I apologize if I was not more specific.

Thank you.



Private Sub lb1_Click() 'Listbox selection to first available row and calculates data. Courtesy of SNB
with Sheets("sheet2").Cells(Rows.Count, 4).End(xlUp).Offset(1)
.Resize(, 7) = Array(lb1.List(lb1.ListIndex, 1), "", lb1.List(lb1.ListIndex, 3), format(lb1.List(lb1.ListIndex, 5),"currency"),"=" & .Offset(, 1).Address & "*" & .Offset(, 3).Address, format(lb1.List(lb1.ListIndex, 6),"currency"), "=" & .Offset(, 1).Address & "*" & .Offset(, 5).Address)
End With
End Sub

[Kenneth Hobs]

Worksheets("Pop").Range("F:G").NumberFormat = "$#,##0.00"

[oldman]

The worksheet range is already formated to currency. I placed your recommendation in both the workbook module and worksheet module but no changes.
What in the good Lord's creation am I not doing to remedy this issue?

Thanks Ken.



Worksheets("Pop").Range("F:G").NumberFormat = "$#,##0.00"

[Kenneth Hobs]

The Pop worksheet that you posted was not formatted.

Put my code after:

Me.lb1.List = Worksheets("POP").Cells(1, 1).CurrentRegion.Value

[oldman]

The Pop worksheet that you posted was not formatted.

Put my code after:

Me.lb1.List = Worksheets("POP").Cells(1, 1).CurrentRegion.Value

Did as you requested but no change.

[Kenneth Hobs]

Oops, I meant just before that line....

[oldman]

Oops, I meant just before that line....

LMAO!

[oldman]

Nothing. Sorry Mr. Hobs.

[Kenneth Hobs]

Without the details to test your file, if you set the numeric format or set it by my method, then you need to use the Range's Text property rather than Value.


Me.lb1.List = Worksheets("POP").Cells(1, 1).CurrentRegion.Text

[oldman]

No go with that also.Thank you for your time and assistance. I did attach the project to my initial post if you wish to review my work.This is not a life or death issue just an annoyance to me. I can hear the users now, "There aren't any dollars signs!"

[Kenneth Hobs]

You included too much or not enough in your file. Simple is better when trouble shooting a problem. Just tell me the steps you used to do it from your example file. e.g. Run the Sub Show in Module 5, then click this, then click that, then the listbox1 is filled.

[oldman]

I'm onto something. If I manually format the cell as '$154.99 it displays correctly in the listbox. Unfortunately there are two columns with over 2500 entries.

[snb]

if you want to reformat data (e.g. in column 6) in a listbox:


sub M_snb()
for j=0 to ubound(Listbox1.list)
listbox1.list(j,5)=formatcurrency(listbox1.List(j,5))
next
end sub


And now the how & why:

- data in a worksheet will be stored as values: the cell's property .Value
- data in a worksheet can be formatted
- cells in a worksheet show the formatted data
- the formatted cells are stored in the cell's property .Text
- if you populate a listbox with data from a worksheet and you use the method .List, the cell's property .Value will be read
- you can't populate a listbox/combobox by listbox.List= with formatted values (the .text property).
- so the listbox/combobox always contains the unformatted values of a worksheet.

- if you want the same appearance in the Listbox (but why would you ?) you can easily adapt the contents of the listbox/combobox with the code I started this post with.
More important than trial & error is trying to understand the logic (whether you approve it or not) behind VBA and Excel's design.

NB. If you have a question please post a sample of your workbook that illustrates that problem and nothing more . Now you introduced a maze to helpers.
I would also like you to use the same alias in several fora, Streub.

[oldman]

Thank you for the detailed explanation. It is very helpful and I now have a basic understanding of the logic. This is the first and only explanation regarding listboxes I have read and that includes tutorials and books. Always the how but never the why.

It was not my intention to confuse or misdirect the members of this forum concerning the listbox issue. My intent was to provide comprehensive information
to the forum members for their and my benefit. It has been my observation that many post do not provide adequate and organized information and I attempt to avoid that when I post an issue.

The appearance of the listbox contents is not important to me; however, from a proactive standpoint I want to avoid user complaints.

Lastly, who is Streub? :)

[Kenneth Hobs]

To get around the Text property to array problem, I wrote a function. Add a Userform and a Listbox control and test in a blank workbook or blank sheet index number 1.

In Userform1:

Private Sub UserForm_Initialize()
Dim i As Integer, j As Integer
With Worksheets(1)
For i = 1 To 10
.Cells(i, "A").Value = i
.Cells(i, "B").Value = i + 0.01
Next i

.Range("A1:B10").NumberFormat = "general"
.Range("B1:B10").NumberFormat = "$#,##0.00"
ListBox1.ColumnCount = 2

ListBox1.List = TextArray(.Cells(1, 1).CurrentRegion)
End With
End Sub



In a Module:

Function TextArray(aRange As Range) As Variant
Dim a() As Variant, d1 As Long, d2 As Long
a() = aRange
For d1 = 1 To UBound(a, 1)
For d2 = 1 To UBound(a, 2)
a(d1, d2) = aRange(d1, d2).Text
Next d2
Next d1
TextArray = a()
End Function

[oldman]

Thank you Mr. Hobs!