Horizontal Scrollbar For Lisbox
 

I have a listbox on a userform that contains entries longer than the length
of the listbox. Thus, I'm looking to add a horizontal scrollbar to the
listbox but am having trouble finding a code sample for userforms. Does
anyone have an example or can help me get one working with userforms?

Thanks,
Justin

Horizontal Scrollbar For Lisbox
 

Just set the columnwidth to more than the width of the listbox in the
properties pane in the VBE.

Robin Hammond
www.enhanceddatasystems.com

"Justin Starnes" wrote in message
...
I have a listbox on a userform that contains entries longer than the length
of the listbox. Thus, I'm looking to add a horizontal scrollbar to the
listbox but am having trouble finding a code sample for userforms. Does
anyone have an example or can help me get one working with userforms?

Thanks,
Justin

Horizontal Scrollbar For Lisbox
 

Unfortunately, the problem with that solution is that I cannot accurately
determine the width of the longest string. To solve that problem, I tried
using the GetTextExtent32 function to obtain the length of the string.
Hoewver, when I set columnwidths to that value, I don't get the correct
result. ColumnWidths defaults to points for the specification. Is
GetTextExtent returning twips, pixels, etc.? I tried to convert it to
points, but am not having much luck. Any ideas?



"Robin Hammond" wrote in message
...
Just set the columnwidth to more than the width of the listbox in the
properties pane in the VBE.

Robin Hammond
www.enhanceddatasystems.com

"Justin Starnes" wrote in message
...
I have a listbox on a userform that contains entries longer than the
length of the listbox. Thus, I'm looking to add a horizontal scrollbar to
the listbox but am having trouble finding a code sample for userforms.
Does anyone have an example or can help me get one working with userforms?

Thanks,
Justin

Horizontal Scrollbar For Lisbox
 

Here is a copy of my code thus far:

Private Type POINTAPI
x As Long
y As Long
End Type

Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal
lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetTextExtentPoint32 Lib "gdi32" Alias
"GetTextExtentPoint32A" (ByVal hDC As Long, ByVal lpsz As String, ByVal
cbString As Long, lpSize As POINTAPI) As Long
Private Declare Function GetWindowDC Lib "user32" (ByVal hwnd As Long) As
Long

Public Sub AddItemToListBox(frm As UserForm, lst As Object, itm As String)
lst.AddItem itm

Dim hwnd As Long
hwnd = FindWindow("ThunderDFrame", frm.Caption)

Dim hDC As Long
hDC = GetWindowDC(hwnd)

Dim pt As POINTAPI
GetTextExtentPoint32 hDC, itm, Len(itm), pt

Dim width As Long
If InStr(lst.ColumnWidths, " pt") 0 Then
width = Mid(lst.ColumnWidths, 1, InStr(lst.ColumnWidths, " pt") - 1)
Else
width = -1
End If

Dim newWidth As Long
newWidth = pt.x
If newWidth width Then lst.ColumnWidths = newWidth
End Sub


"Justin Starnes" wrote in message
...
Unfortunately, the problem with that solution is that I cannot accurately
determine the width of the longest string. To solve that problem, I tried
using the GetTextExtent32 function to obtain the length of the string.
Hoewver, when I set columnwidths to that value, I don't get the correct
result. ColumnWidths defaults to points for the specification. Is
GetTextExtent returning twips, pixels, etc.? I tried to convert it to
points, but am not having much luck. Any ideas?



"Robin Hammond" wrote in message
...
Just set the columnwidth to more than the width of the listbox in the
properties pane in the VBE.

Robin Hammond
www.enhanceddatasystems.com

"Justin Starnes" wrote in message
...
I have a listbox on a userform that contains entries longer than the
length of the listbox. Thus, I'm looking to add a horizontal scrollbar to
the listbox but am having trouble finding a code sample for userforms.
Does anyone have an example or can help me get one working with
userforms?

Thanks,
Justin

Horizontal Scrollbar For Lisbox
 

Hi Justin
Robin's suggestion has nothing to do with the length of the string
(you've found yourself a fine red herring there), but properties of
the Listbox. Try it!
Paul

"Justin Starnes" wrote in message ...
Unfortunately, the problem with that solution is that I cannot accurately
determine the width of the longest string. To solve that problem, I tried
using the GetTextExtent32 function to obtain the length of the string.
Hoewver, when I set columnwidths to that value, I don't get the correct
result. ColumnWidths defaults to points for the specification. Is
GetTextExtent returning twips, pixels, etc.? I tried to convert it to
points, but am not having much luck. Any ideas?



"Robin Hammond" wrote in message
...
Just set the columnwidth to more than the width of the listbox in the
properties pane in the VBE.

Robin Hammond
www.enhanceddatasystems.com

"Justin Starnes" wrote in message
...
I have a listbox on a userform that contains entries longer than the
length of the listbox. Thus, I'm looking to add a horizontal scrollbar to
the listbox but am having trouble finding a code sample for userforms.
Does anyone have an example or can help me get one working with userforms?

Thanks,
Justin

Horizontal Scrollbar For Lisbox
 

Hi,

It has already been pointed out that if you set the columnwidths
property to be wider that the control the horizontal scroller will
appear. To determine how wide it should be you can use the following
function.
It creates a label control on the fly and uses that to determine the
widest piece of text.

Once the list box is populated you can use this command.
ListBox1.ColumnWidths = GetWidth(ListBox1)


'-------------------------------------------
Function GetWidth(MyList As msforms.ListBox, _
Optional ColIndex As Integer)
Dim objLabel As New Control
Dim intItem As Integer
Dim lngMaxWidth As Long

' create label to determine width of listbox contents
Set objLabel = Me.Controls.Add("Forms.Label.1", "xxxTemp", False)
With objLabel
.WordWrap = False
With .Font ' match font attributes
.Name = MyList.Font.Name
.Size = MyList.Font.Size
.Bold = MyList.Font.Bold
.Italic = MyList.Font.Italic
End With
For intItem = 0 To MyList.ListCount - 1
.AutoSize = False
.Width = 1000
.Caption = MyList.List(intItem, ColIndex)
.AutoSize = True
If .Width lngMaxWidth Then lngMaxWidth = .Width
Next
Me.Controls.Remove .Name
End With
Set objLabel = Nothing
GetWidth = lngMaxWidth + 10
End Function
'-------------------------------------------

Cheers
Andy

Justin Starnes wrote:

Unfortunately, the problem with that solution is that I cannot accurately
determine the width of the longest string. To solve that problem, I tried
using the GetTextExtent32 function to obtain the length of the string.
Hoewver, when I set columnwidths to that value, I don't get the correct
result. ColumnWidths defaults to points for the specification. Is
GetTextExtent returning twips, pixels, etc.? I tried to convert it to
points, but am not having much luck. Any ideas?



"Robin Hammond" wrote in message
...

Just set the columnwidth to more than the width of the listbox in the
properties pane in the VBE.

Robin Hammond
www.enhanceddatasystems.com

"Justin Starnes" wrote in message
...

I have a listbox on a userform that contains entries longer than the
length of the listbox. Thus, I'm looking to add a horizontal scrollbar to
the listbox but am having trouble finding a code sample for userforms.
Does anyone have an example or can help me get one working with userforms?

Thanks,
Justin






--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info