Listbox date formate problem

[sharky12345]

I'm using this to load a Listbox on a userform;

With Sheets("Referrals")
lbtarget.List = .Range("A3", .Range("L65000").End(xlUp)).Value
End With

Columns 3 and 4 of the sheet are date values, but when they appear in the Listbox they have been converted to US format - can this be stopped or worked around?

 

[MickG]

You may need to do it the long way, similar to below:-

Dim Rng As Range
Dim Ac As Range
Dim Dn As Range
Set Rng = Range("A1:H12")
ListBox1.ColumnCount = 8
ListBox1.Clear
ListBox1.ColumnWidths = "50,50,80,80,50,50,50,50"
        For Each Dn In Rng.Rows
             For Each Ac In Dn.Columns
                With ListBox1
                   If Ac.Column = 1 Then
                        .AddItem Ac
                    Else
                    If Ac.Column = 3 Or Ac.Column = 4 Then
                        .List(.ListCount - 1, Ac.Column - 1) = Format(Ac, "dd/mm/yyyy")
                       Else
                        .List(.ListCount - 1, Ac.Column - 1) = Ac
                    End If
                    End If
                End With
            Next Ac
        Next Dn

 

[sharky12345]

MickG, thanks - I need the range though to be dynamic, in that it only grabs the used range. I've tried this and I get 'Object Required' with the 'Set Rng' line failing;

Dim Rng As Range
Dim Ac As Range
Dim Dn As Range
Set Rng = Sheets("Referrals").Range("A3", Range("L65000").End(xlUp)).Value 'Range("A1:H12")
ListBox1.ColumnCount = 12
ListBox1.Clear
ListBox1.ColumnWidths = "70;70;70;100;100;100;100;100;100;100;100;100"
        For Each Dn In Rng.Rows
             For Each Ac In Dn.Columns
                With ListBox1
                   If Ac.Column = 1 Then
                        .AddItem Ac
                    Else
                    If Ac.Column = 3 Or Ac.Column = 4 Then
                        .List(.ListCount - 1, Ac.Column - 1) = Format(Ac, "dd/mm/yyyy")
                       Else
                        .List(.ListCount - 1, Ac.Column - 1) = Ac
                    End If
                    End If
                End With
            Next Ac
        Next Dn

Any ideas?

 

[dmt32]

Hi,
not tested but perhaps pass your cell values as text to a string array.

See if this helps:

Private Sub UserForm_Initialize()
    Dim rng As Range


    With Sheets("Referrals")
        Set rng = .Range(.Range("A3"), .Range("L" & .Rows.Count).End(xlUp))
        lbtarget.ColumnCount = rng.Columns.Count
        lbtarget.ColumnWidths = "70;70;70;100;100;100;100;100;100;100;100;100"
        lbtarget.List = StringArray(Target:=rng)
    End With


End Sub




Function StringArray(ByVal Target As Range) As String()
    Dim RangeArray() As String
    Dim c As Range
    
    ReDim RangeArray(1 To Target.Rows.Count, 1 To Target.Columns.Count) As String

    For Each c In Target
        RangeArray(c.Row - Target.Row + 1, c.Column) = c.Text
    Next c

    StringArray = RangeArray

End Function

Dave

 

[MickG]

Try this in your Userform.
In the other method there appears to be a limit of the Number of column allowed.
NB:- I don't think you can call the Userform from another sheet , because it uses the range Address in th "RowSource"

Private Sub UserForm_Initialize()
   With Me.ListBox1
    .ColumnCount = 12
    .ColumnWidths = "70,70,70,100,100,100,100,100,100,100,100,100"
    .RowSource = Range(Range("A3"), Range("L" & Rows.Count).End(xlUp)).Address
   End With
End Sub

 

[sharky12345]

Gents - thanks so much to both for taking the time to respond, the solution from MickG works.

Cheers!