Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Troubleshoot horizontal scrollbar | Excel Worksheet Functions | |||
Horizontal Scrollbar | New Users to Excel | |||
adding an horizontal scrollbar to columns | Excel Discussion (Misc queries) | |||
Horizontal scrollbar in ListBox | Excel Programming | |||
FullScreen Mode Horizontal ScrollBar Missing | Excel Programming |