VBA ListBox right alignment display issue

[Jomathr]

Good morning everyone,

I have a few listbox with all the same issue. When I set the textalign property to TextAlignRight the las column is not fully displayed, at worst half of a 30 character string is missing. Work fine if I set it to left or centered align.

Here are the properties of those listbox



.boundcolumn = 1
.columncount = 10
.columnwidth = 0 pt;73 pt;60 pt;200 pt;204.95 pt;75 pt;100 pt;60 pt;75 pt;139.95 pt;150 pt
.textalign = fmTextAlignRight
.width = 1124.25


I don't think the other properties are causing the issue here. I tried changing the last .columnwidth and it doesn't change anything. Check to see if the sum of my column was larger then the listbox itself and it's not. Also tried to remove the .columnwidth for column 10 and let VBA handle it but I get the same result.

and here is how I insert data in the listbox:



For Each c In Worksheets("DataJGO").Range(LastCellJE).Cells
strsearch = c.Offset(0, 6).Value
Set aCell = Osht.Range("B1:B" & lastrow).Find(what:=strsearch, LookIn:=xlValues, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not aCell Is Nothing Then
accountName = Range("ConfigComptes!" & aCell.Address).Offset(0, -1).Value
End If
If c.Row <> "1" And c.Value <> "" Then
With Me.LstEJ
.AddItem
.List(x, 0) = c.Value
.List(x, 1) = c.Offset(0, 2).Value
.List(x, 2) = Format(c.Offset(0, 22).Value, "short date")
.List(x, 3) = c.Offset(0, 20).Value
.List(x, 4) = c.Offset(0, 21).Value
.List(x, 5) = c.Offset(0, 31).Value
.List(x, 6) = Application.WorksheetFunction.VLookup(c.Offset(0, 30).Value, Range(langue & "!LangTransType"), 3, False)
.List(x, 7) = c.Offset(0, 14).Value
.List(x, 8) = Format(c.Offset(0, 11).Value, "Standard")
.List(x, 9) = c.Offset(0, 6).Value
x = x + 1
End With
End If
Next c

Anyone have an idea on what is causing this? If you need more info that I didn't include please let me know

I should have used the displayview from VB 6 but deadline is tommorow and don't have time to switch all the listbox that are in the application.

[Aussiebear]

Is that a typo where the first column has a width of 0 pts?

[Jomathr]

no it's not a typo, the column is hidden on purpose, the index is in that column

[Rob342]

Have you tried setting the column count to 11, as you are showing 11 columns

[Jomathr]

My bad, the last column witdth (150pt) was an incorrect entry, corrected it and still the same problem,

I'm not sure if it's a workaround that you were proposing but ListBox are limited to 10 columns, if so I didn't know about it

[Rob342]

I Have Listboxes with 49 columns of data, check the properties on the Listbox, or provide a sample of the workbook

These should be Capital letters and where does this code reside as i would expect to see

With Me.LstEJ

.BoundColumn = 1 ' I would let this default as set up in Properties
.ColumnCount = 10
.ColumnWidth = 0 pt;73 pt;60 pt;200 pt;204.95 pt;75 pt;100 pt;60 pt;75 pt;139.95 pt;150 pt
.TextAlign = 3 'fmTextAlignRight value 1= left 2 = centre & 3 =right
.Width = 1124.25
End with

[Jomathr]

The properties listed above are from the property box of the listbox, I just typed them instead of taking a screenshot of the properties.

I am curious about that 49 column listbox, is it filled using rowsource? if it is the case then yes I knew it was possible, Else I'd really like to know how you did it.

here is a part of the file with the exact same listbox but I had to tweak a few things so I wouldn't have to post all the sheets it gets infor from but the same problem is replicated but it is much worst then 1-2 numbers no showing properly

10799

[Rob342]

Jomathr
There is no listbox or code attached to your file only data ?

Couple of examples that might help


Set ws = Worksheets("MainDB")
IRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1
With ws
Set SearchRange = .Range("AT3:AT" & IRow) '.Range("B3:B" & IRow)for Name
FindWhat = "S"
Set FoundCells = FindAll(SearchRange:=SearchRange, _
FindWhat:=FindWhat, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
MatchCase:=False, _
BeginsWith:=vbNullString, _
EndsWith:=vbNullString, _
BeginEndCompare:=vbTextCompare)
If FoundCells Is Nothing Then
MsgBox "No Invoices At Status S ", , "Invoices At Status S"
Exit Sub
Else
TotNo = FoundCells.Count
For Each FoundCell In FoundCells
ActRow = Val(Mid(FoundCell.Address, 5, Len(FoundCell.Address)))

'// Assign variables to respective fields, chk on name = me.CboCustPay
RsName = .Cells(ActRow, "C").Offset(0, -1).Value 'Name of cust in row
If RsName = Me.CboCustPay.Value Then 'Match Cust Name
'// Define the list and where it's obtained from (Columns A, B, N, O, P in this example)
'// MyList (Row{0 to 9}, Column{0 to 5}) = the ranges given 'Row = ActRow Cols 5
ReDim Preserve MyList(1 To TotNo, 5) 'Dimension Array to actual size
MyList(c, 0) = .Range("A" & ActRow).Text 'job no
MyList(c, 1) = .Range("H" & ActRow).Text 'BRANCH
MyList(c, 2) = .Range("J" & ActRow).Value 'Job text
MyList(c, 3) = .Range("AP" & ActRow).Value ' date inv
MyList(c, 4) = .Range("AL" & ActRow).Value 'total lab & mat
c = c + 1
Me.LB1.List = MyList

End If


or using row source


'// lIST BOX FOR ESTIMATE
IRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
With Me.LBEst
.RowSource = "JCodes!A3:B" & IRow
.ColumnCount = 2
.ColumnHeads = True
.ColumnWidths = "60pt;80pt"
.listIndex = 0
End With

[Jomathr]

Forgot to put a button to access the userform with the listbox, it's there in the vba editor. my bad sorry

[Rob342]

Are you going to attach the file with the code and form, its not in the last file ?

[Aflatoon]

If you use AddItem to populate the listbox then it is limited to 10 columns. If you use RowSource or assign an array to the List property, then it is not limited, except by memory. (performance is not usually good with 100,000 columns).

[Jomathr]

I remade the file, this time with the userform. sorry about the mistake on the last file

10808

[Rob342]

Jomathr

I have created an array for you in the attached example, you can always change the range with the data you personally want
If you want more columns then increase the array size and the column count in the listbox
Rob

[Jomathr]

Nice array Rob! I'll keep it for later use!

The problem remain thought, if you look at the last column the scroll bar still goes over the characters, that's what I am trying to solve

[p45cal]

Hmmm, it looks like there's a problem with the vertical scrollbar obsuring the tail end of the values in the rightmost column. In the attached, I've worked around this by adding another (11th) narrow rightmost column. I've also changed how the listbox is populated by assigning an array to its .List (because I suspect with more than 10 columns you can't use .AddItem). You may notice an increase in speed. It may need a tweak to cater for blank cells in column A amongst the data (which doesn't happen in your sample data, so it may not matter).

[p45cal]

..because I can't add more than one file to a message, a separate message. The attached takes a different approach by loading the listbox with its .RowSource, using a range on a temporary sheet. This allows the customisation of headers in the listbox which you might like. This one does cater for blanks in column A, should there be any.
10813

Now to take a peep at Rob342's file…

[Jomathr]

Fantastic p45cal!

Works like a charm, I just have to insert a temp sheet and use row source from there. I'll have to look a bit more in depth for Ubound and Lbound, as I never used those before but I get the idea, to fully understand the code.

One thing I don't understand is the .Address(external:=True) on the last line, what is the purpose of the external:=true?

Thank you very much, you made my day, even thought it's 10pm here haha

[p45cal]

One thing I don't understand is the .Address(external:=True) on the last line, what is the purpose of the external:=true?.Rowsource wants to see a string. If I hadn't bothered with External:=True the string would have been (in your sample file):
$A$2:$K$224
which refers, I think, to the active sheet; so let's hope that the active sheet is the right one. Mostly it will be, but perhaps yoo don't want that sheet showing behind the userform? You might choose for that sheet to be hidden so the user is unaware of its presence. In that case the active sheet will be something else - definitely the wrong sheet, so using External:= True adds sheet information (and workbook info too):
[vbaExpress48127LstboxRightAlignProb5.xlsm]Sheet5!$A$2:$K$224
So now there will be no question as to what range on which sheet to use as it's all explicitly specified.

ps. If you want to have the sheet hidden then the line in red below added after the existing line in blue:
Set ListSht = Sheets.Add
ListSht.Visible = xlSheetHidden

[Jomathr]

So if I get this right you could use the external property to solve the issue of user switching workbook after they lauched the form then coming back to the VBA userform wich give a range error if you use it in the form initialize to set the various range needed.

[p45cal]

So if I get this right you could use the external property to solve the issue of user switching workbook after they lauched the form then coming back to the VBA userform wich give a range error if you use it in the form initialize to set the various range needed.
I haven't tested, but I strongly suspect that once the form has been launched, and the listbox populated (without External:= True) then it will stick with that .rowsource sheet, so I think that (a) it's the active sheet at the time the .rowsource line is executed that matters and (b) your problem won't arise (does it? - it would if another .rowsource line is executed, say by a button's click_event handler code which might want to refresh the listbox).