Display data at listbox on userform properly

[YasserKhalil]

Hello everyone

I have a listbox on userform that is populated with Arabic names and I have aligned the listbox to be right-to-left. The listbox is length so there is a scroll bar appeared to the right of the listbox and this scrollbar hides some letters of the names. How to make the scroll bar to the left of the listbox? or how to make the full name appears properly?

[robertocm]

Just an idea,

A counter-intuitive solution that has worked for me in similar cases:
place the line that establishes the column widths at the end (after loading the data)

Below an example:

Code: Select all

With Me.ListBox2
  .ColumnCount = 13
  If Not rst.EOF Then
    .Column = rst.GetRows()
    rst.Close
  Else
    rst.Close
    GoTo CleanUp
  End If
  .ColumnWidths = "3 cm;1 cm;1 cm;1 cm;1 cm;1 cm;1 cm;1 cm;1 cm;1 cm;1 cm;1 cm;0 cm"
End With

similar:

Code: Select all

Dim frm As UserForm1
Set frm = New UserForm1
With frm
	With .ListBox1
		.ColumnCount = 3
		.List() = MyArray
		.ColumnWidths = "2 cm;2 cm;2 cm;"
	End With
	.Show
End With
Unload frm
Set frm = Nothing

Another idea:
In the properties pane of the listbox, set IntegralHeight to False

[YasserKhalil]

Thanks a lot but the codes don't solve the problem. The problem is not with the column widths.
By the way the listbox has only one column and I have set it to be very wide.

[SpeakEasy]

robertocm was trying to solve the horizontal scroll bar issue (of which there are many posts on the web), but you want the vertical scrollbar ...

And that's a problem, since we are using an MSForms listbox (actually, it would be a problem even with the normal Windows listbox control). And this is almost certainly why there are few if any solutions posted on the internet.

I have a vague idea of how ity might be done, but need some time to try it out

[YasserKhalil]

Thank you very much for your interest. Take your time. I will wait for your idea.

[SpeakEasy]

Ah, no doesn't work. There is a limitation in what i was trying to do (change the underlying extended style of the Listbox, something quite fundamental), But the style I was trying to use only works on a platform where "the shell language is Hebrew, Arabic, or another language that supports reading order alignment" - and on such a platform I'd suspect that the scroll bar already appears on the left ...

[YasserKhalil]

As an idea but I didn't do that. I may add another column to the listbox with specific width (say 20 pt) and leave it empty so as to get the values in the second column displayed properly.

[SpeakEasy]

Yep, I looked at a version of that solution that as well, but it causes the horizontal scroll bar to appear

[robertocm]

Ana idea could be trying to simulate a listbox with an htm table

here an example:

WebBrowser_simulate_ListBox.xlsm

some test here looking for interaction:

WebBrowser_Control_tests_202304.xlsm

[HansV]

I think Yasser wants something like this:

S2404.png

[robertocm]

Another idea:

From VBA Help:
RightToLeft Property

RightToLeft is a new property of the Microsoft Forms 2.0 form that the specified control is placed on; it’s not a property of the control. Note, however, that RightToLeft affects both the form and any controls that are placed on it. The following table describes the two possible settings for this property.
...
The vertical scroll bar (if present) is on the left side of the form or control. The initial thumb location of the horizontal scroll bar is the rightmost position, and the rightmost portion of information is displayed.

I'm not able to test because of my language configuration.
References (VBA RIGHTOLEFT property enable):
- Enable Right-to-Left in Microsoft Project
- RightToLeft property (Visual Basic for Applications) - Microsoft Learn

[SpeakEasy]

The RightToLeft property suffers from the same limitation I mentioned earlier in terms of only working on a platform where "the shell language is Hebrew, Arabic, or another language that supports reading order alignment" . Why? Becasue it is a VBA wrapper around the same style setting that I was trying do with the API (I was using the API to try and get around this, but without success, due to fundamental behaviours of Windows and the listbox control.)

(actually, the VBA wrapper is perhaps somewhat more informative. If we use the API to set the listbox control's WS_EX_RIGHTSCROLLBAR property directly,. then it gets set, but is then ignored.. At least with the VBA wrapper it switches the setting back to False if you try and set it to True)

And, given that the MS Forms team decided to write their own, publicly undocumented versions of the underlying controls (because they wanted/needed some slightly different functionality), a number of other API techniques do not work either (eg the ShowScrollBar API call doesn't do anything)

[robertocm]

Just an experiment with an Office Web Components ActiveX control (spreadsheet object)
It has a RightToLeft Property (see OWCVBA11.CHM).
Only as a curiosity, because it's an old control.

OWC_control.png

This is the example file:

Office_Web_Components_simulate_ListBox.xlsm

References
- Google:
OWC11.exe
office web components download
- OWCDSS11.CHM
- OWCVBA11.CHM (Microsoft Office 2003 Web Components Visual Basic Reference)
- https://www.autoitscript.com/forum/inde ... opic=17802