Display data at listbox on userform properly
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Display data at listbox on userform properly
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?
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?
-
- Lounger
- Posts: 43
- Joined: 07 Jun 2023, 15:34
Re: Display data at listbox on userform properly
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:
similar:
Another idea:
In the properties pane of the listbox, set IntegralHeight to False
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
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
In the properties pane of the listbox, set IntegralHeight to False
Last edited by HansV on 08 Jun 2023, 18:57, edited 1 time in total.
Reason: to add [code] and [/code] tags
Reason: to add [code] and [/code] tags
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Display data at listbox on userform properly
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.
By the way the listbox has only one column and I have set it to be very wide.
-
- 4StarLounger
- Posts: 574
- Joined: 27 Jun 2021, 10:46
Re: Display data at listbox on userform properly
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
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
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Display data at listbox on userform properly
Thank you very much for your interest. Take your time. I will wait for your idea.
-
- 4StarLounger
- Posts: 574
- Joined: 27 Jun 2021, 10:46
Re: Display data at listbox on userform properly
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 ...
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Display data at listbox on userform properly
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.
-
- 4StarLounger
- Posts: 574
- Joined: 27 Jun 2021, 10:46
Re: Display data at listbox on userform properly
Yep, I looked at a version of that solution that as well, but it causes the horizontal scroll bar to appear
-
- Lounger
- Posts: 43
- Joined: 07 Jun 2023, 15:34
Re: Display data at listbox on userform properly
Ana idea could be trying to simulate a listbox with an htm table
here an example: some test here looking for interaction:
here an example: some test here looking for interaction:
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Display data at listbox on userform properly
I think Yasser wants something like this:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 43
- Joined: 07 Jun 2023, 15:34
Re: Display data at listbox on userform properly
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
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
-
- 4StarLounger
- Posts: 574
- Joined: 27 Jun 2021, 10:46
Re: Display data at listbox on userform properly
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)
(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)
-
- Lounger
- Posts: 43
- Joined: 07 Jun 2023, 15:34
Re: Display data at listbox on userform properly
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.
This is the example file:
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
It has a RightToLeft Property (see OWCVBA11.CHM).
Only as a curiosity, because it's an old control.
This is the example file:
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