MS Office Userforms - Lots of Data, Limited Space
Posted by Derek Mang on 1st May 2007
Presenting data on a Userform is pretty common in Office solutions. Sometimes there's "so much data, so little space", since a userform is only so big and your user requirements need to be addressed.
To help alleviate userform "real estate" limitations, I'd like to present two techniques that can be very effective.
Scrolling Frame Contents
To set up this technique, first add the full-sized frame to a userform, where the userform is larger than its eventual display size.
Next, add all the controls you need inside the frame. This may include text boxes, spin buttons, labels, etc..
Once the controls are suitably named, resize the userform to its desired display height and add a vertical scrollbar inside the frame that is almost the same height as the frame.
The code is pretty straighforward. In the Initialize event, the scrollbar's Max property needs to be determined.
Dim obj As Frame
Dim ctl As Control
Dim sngCtlMax As Single 'max depth of controls in frame
Set obj = txtrole1.Parent
For Each ctl In obj.Controls
If ctl.Name "ScrollBar1" Then
ctl.Tag = ctl.Top
If sngCtlMax> ctl.Top + ctl.Height Then
Else
sngCtlMax = ctl.Top + ctl.Height
End If
End If
Next ctl
ScrollBar1.Max = sngCtlMax - ScrollBar1.Height
End Sub
The remainder of the code is found in the Scroll and Change events of the scrollbar. The Top property of each control in the frame is being adjusted to provide the scrolling effect.
Dim obj As Frame
Dim ctl As Control
Dim i As Long
Set obj = txtrole1.Parent
For Each ctl In obj.Controls
If ctl.Name = "ScrollBar1" Then
Else
ctl.Top = CSng(ctl.Tag) - ScrollBar1.Value
End If
Next ctl
End Sub
Private Sub ScrollBar1_Scroll()
Dim obj As Frame
Dim ctl As Control
Dim i As Long
Set obj = txtrole1.Parent
For Each ctl In obj.Controls
If ctl.Name = "ScrollBar1" Then
Else
ctl.Top = CSng(ctl.Tag) - ScrollBar1.Value
End If
Next ctl
End Sub
Listview with ToolTips
I often use the listview control in place of the standard listbox. It ships with Windows, being used extensively for presentation (i.e. Windows Explorer).
This control can be added to your VBA project by including a reference (Tools | References in the IDE) to Microsoft Windows Common Controls 6.0. (C:\Windows\System32\MSCOMCTL.ocx). Place this control on your form and size as needed.
The listview is not a "lightweight" control like the standard Userform controls, meaning, it has a "handle", or hwnd property. This is the key! Having a handle opens up all kinds of possibilities, but the one used here is the inclusion of a multiline tooltip as a display mechanism.
There are a few coding considerations to implement the tooltip functionality.
Userform Code
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
'listview constants
Const LVM_FIRST = &H1000&
Const LVM_HITTEST = LVM_FIRST + 18
'used for cursor position
Private Type POINTAPI
X As Long
y As Long
End Type
'used in the listview "HIT" test process to determine which list item is beneath the mouse (cursor).
Private Type LVHITTESTINFO
pt As POINTAPI
flags As Long
iItem As Long
iSubItem As Long
End Type
Dim m_lCurItemIndex As Long
'reference to Tooltip class
Dim TT As cToolTip
Listview Headings are defined to establish columns in the control. The first two columns are assigned two-thirds of the controls viewable width. The remaining columns are equally apportioned the remaining 1/3.
Dim c As Integer
With lvwcols
.View = lvwReport
With .ColumnHeaders
.Clear
.Add , , "First Name"
.Add , , "Last Name"
.Add , , "Subject"
.Add , , "Term 1"
.Add , , "Term 2"
.Add , , "Term 3"
.Add , , "Bonus"
.Add , , "Attendance"
.Add , , "Final"
End With
.ColumnHeaders(1).Width = (.Width - 15) / 3
.ColumnHeaders(2).Width = (.Width - 15) / 3
For c = 3 To .ColumnHeaders.Count
.ColumnHeaders(c).Width = ((.Width - 15) / 3) / (.ColumnHeaders.Count - 2)
Next c
End With
End Sub
LVLoad will load some information into the control. In this case, the data is manufactured, but could easily originate in a database, on a worksheet, or in another external source.
Dim ctl As Control
Dim i As Long
Dim itmx As Object
lvwcols.ListItems.Clear
For i = 0 To UBound(lName) - 1
With lvwcols
Set itmx = .ListItems.Add(, , fName(i))
End With
With itmx
.SubItems(1) = lName(i)
.SubItems(2) = Subject(i)
.SubItems(3) = i + 64
.SubItems(4) = i + 81
.SubItems(5) = i + 77
.SubItems(6) = i + 8
.SubItems(7) = i + 4
.SubItems(8) = i + 77
End With
Next i
End Sub
The Userform_Initialize sets up the listview, loads some data (in this case very arbitrary), and establishes an instance of the tooltip class.
fName(0) = "John"
lName(0) = "Smith"
Subject(0) = "Engish"
fName(1) = "John"
lName(1) = "Booth"
Subject(1) = "French"
fName(2) = "John"
lName(2) = "Henry"
Subject(2) = "Spanish"
Call LVHeading
Call LVLoad
Set TT = New cToolTip
TT.Style = TTBalloon
TT.Icon = TTIconInfo
The listview control's MouseMove event is where the real action happens. The "Hit" test determines the list item under the mouse. Note that the mouse must be positioned over the first column. Position will ultimately determine the display position of the tooltip.
Once the position has been established, the tooltip is built. The tooltip title, in this case, contains information from the first 2 columns. The tooltip detail contains columnheaders as identifiers and corresponding item data, on separate lines.
Dim lvhti As LVHITTESTINFO
Dim lItemIndex As Long
Dim c As Integer
Dim strText As String
lvhti.pt.X = X
lvhti.pt.y = y
lItemIndex = SendMessage(lvwcols.hwnd, LVM_HITTEST, 0, lvhti) + 1
If m_lCurItemIndex lItemIndex Then
m_lCurItemIndex = lItemIndex
If m_lCurItemIndex = 0 Then ' no item under the mouse pointer
TT.Destroy
Else
TT.Title = lvwcols.ListItems(m_lCurItemIndex).text & " " & lvwcols.ListItems(m_lCurItemIndex).SubItems(1)
For c = 3 To lvwcols.ColumnHeaders.Count
strText = strText _
& lvwcols.ColumnHeaders(c) & ": " _
& lvwcols.ListItems(m_lCurItemIndex).SubItems(c - 1) & vbCrLf
Next c
TT.TipText = strText
TT.Create lvwcols.hwnd
End If
End If
End Sub
The ToolTip Class
I found this class module code on the Web. I don't know the author, but he / she / they get full marks for a neat little item!
Add a class module to the VBA project (this one is called cToolTip)
Private Declare Sub InitCommonControls Lib "comctl32.dll" ()
''Windows API Functions
Private Declare Function CreateWindowEx Lib "user32" Alias "CreateWindowExA" (ByVal dwExStyle As Long, ByVal lpClassName As String, ByVal lpWindowName As String, ByVal dwStyle As Long, ByVal X As Long, ByVal y As Long, ByVal nWidth As Long, ByVal nHeight As Long, ByVal hWndParent As Long, ByVal hMenu As Long, ByVal hInstance As Long, lpParam As Any) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Private Declare Function SendMessageLong Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
Private Declare Function DestroyWindow Lib "user32" (ByVal hwnd As Long) As Long
''Windows API Constants
Private Const WM_USER = &H400
Private Const CW_USEDEFAULT = &H80000000
''Windows API Types
Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type
''Tooltip Window Constants
Private Const TTS_NOPREFIX = &H2
Private Const TTF_TRANSPARENT = &H100
Private Const TTF_CENTERTIP = &H2
Private Const TTM_ADDTOOLA = (WM_USER + 4)
Private Const TTM_ACTIVATE = WM_USER + 1
Private Const TTM_UPDATETIPTEXTA = (WM_USER + 12)
Private Const TTM_SETMAXTIPWIDTH = (WM_USER + 24)
Private Const TTM_SETTIPBKCOLOR = (WM_USER + 19)
Private Const TTM_SETTIPTEXTCOLOR = (WM_USER + 20)
Private Const TTM_SETTITLE = (WM_USER + 32)
Private Const TTS_BALLOON = &H40
Private Const TTS_ALWAYSTIP = &H1
Private Const TTF_SUBCLASS = &H10
Private Const TTF_IDISHWND = &H1
Private Const TTM_SETDELAYTIME = (WM_USER + 3)
Private Const TTDT_AUTOPOP = 2
Private Const TTDT_INITIAL = 3
Private Const TOOLTIPS_CLASSA = "tooltips_class32"
'Tooltip Window Types
Private Type TOOLINFO
lSize As Long
lFlags As Long
hwnd As Long
lId As Long
lpRect As RECT
hInstance As Long
lpStr As String
lParam As Long
End Type
Public Enum ttIconType
TTNoIcon = 0
TTIconInfo = 1
TTIconWarning = 2
TTIconError = 3
End Enum
Public Enum ttStyleEnum
TTStandard
TTBalloon
End Enum
'local variable(s) to hold property value(s)
Private mvarBackColor As Long
Private mvarTitle As String
Private mvarForeColor As Long
Private mvarIcon As ttIconType
Private mvarCentered As Boolean
Private mvarStyle As ttStyleEnum
Private mvarTipText As String
Private mvarVisibleTime As Long
Private mvarDelayTime As Long
'private data
Private m_lTTHwnd As Long ' hwnd of the tooltip
Private m_lParentHwnd As Long ' hwnd of the window the tooltip attached to
Private ti As TOOLINFO
Public Property Let Style(ByVal vData As ttStyleEnum)
'used when assigning a value to the property, on the left side of an assignment.
'Syntax: X.Style = 5
mvarStyle = vData
End Property
Public Property Get Style() As ttStyleEnum
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.Style
Style = mvarStyle
End Property
Public Property Let Centered(ByVal vData As Boolean)
'used when assigning a value to the property, on the left side of an assignment.
'Syntax: X.Centered = 5
mvarCentered = vData
End Property
Public Property Get Centered() As Boolean
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.Centered
Centered = mvarCentered
End Property
Public Function Create(ByVal ParentHwnd As Long) As Boolean
Dim lWinStyle As Long
If m_lTTHwnd 0 Then
DestroyWindow m_lTTHwnd
End If
m_lParentHwnd = ParentHwnd
lWinStyle = TTS_ALWAYSTIP Or TTS_NOPREFIX
'create baloon style if desired
If mvarStyle = TTBalloon Then lWinStyle = lWinStyle Or TTS_BALLOON
m_lTTHwnd = CreateWindowEx(0&, _
TOOLTIPS_CLASSA, _
vbNullString, _
lWinStyle, _
CW_USEDEFAULT, _
CW_USEDEFAULT, _
CW_USEDEFAULT, _
CW_USEDEFAULT, _
0&, _
0&, _
0, _
0&)
''now set our tooltip info structure
With ti
''if we want it centered, then set that flag
If mvarCentered Then
.lFlags = TTF_SUBCLASS Or TTF_CENTERTIP Or TTF_IDISHWND
Else
.lFlags = TTF_SUBCLASS Or TTF_IDISHWND
End If
''set the hwnd prop to our parent control's hwnd
.hwnd = m_lParentHwnd
.lId = m_lParentHwnd '0
.hInstance = 0
'.lpstr = ALREADY SET
'.lpRect = lpRect
.lSize = Len(ti)
End With
''add the tooltip structure
SendMessage m_lTTHwnd, TTM_ADDTOOLA, 0&, ti
''if we want a title or we want an icon
If mvarTitle vbNullString Or mvarIcon TTNoIcon Then
SendMessage m_lTTHwnd, TTM_SETTITLE, CLng(mvarIcon), ByVal mvarTitle
End If
If mvarForeColor Empty Then
SendMessage m_lTTHwnd, TTM_SETTIPTEXTCOLOR, mvarForeColor, 0&
End If
If mvarBackColor Empty Then
SendMessage m_lTTHwnd, TTM_SETTIPBKCOLOR, mvarBackColor, 0&
End If
SendMessageLong m_lTTHwnd, TTM_SETDELAYTIME, TTDT_AUTOPOP, mvarVisibleTime
SendMessageLong m_lTTHwnd, TTM_SETDELAYTIME, TTDT_INITIAL, mvarDelayTime
End Function
Public Property Let Icon(ByVal vData As ttIconType)
mvarIcon = vData
If m_lTTHwnd 0 And mvarTitle Empty And mvarIcon TTNoIcon Then
SendMessage m_lTTHwnd, TTM_SETTITLE, CLng(mvarIcon), ByVal mvarTitle
End If
End Property
Public Property Get Icon() As ttIconType
Icon = mvarIcon
End Property
Public Property Let ForeColor(ByVal vData As Long)
mvarForeColor = vData
If m_lTTHwnd 0 Then
SendMessage m_lTTHwnd, TTM_SETTIPTEXTCOLOR, mvarForeColor, 0&
End If
End Property
Public Property Get ForeColor() As Long
ForeColor = mvarForeColor
End Property
Public Property Let Title(ByVal vData As String)
mvarTitle = vData
If m_lTTHwnd 0 And mvarTitle Empty And mvarIcon TTNoIcon Then
SendMessage m_lTTHwnd, TTM_SETTITLE, CLng(mvarIcon), ByVal mvarTitle
End If
End Property
Public Property Get Title() As String
Title = ti.lpStr
End Property
Public Property Let BackColor(ByVal vData As Long)
mvarBackColor = vData
If m_lTTHwnd 0 Then
SendMessage m_lTTHwnd, TTM_SETTIPBKCOLOR, mvarBackColor, 0&
End If
End Property
Public Property Get BackColor() As Long
BackColor = mvarBackColor
End Property
Public Property Let TipText(ByVal vData As String)
mvarTipText = vData
ti.lpStr = vData
If m_lTTHwnd 0 Then
SendMessage m_lTTHwnd, TTM_UPDATETIPTEXTA, 0&, ti
End If
End Property
Public Property Get TipText() As String
TipText = mvarTipText
End Property
Private Sub Class_Initialize()
InitCommonControls
mvarDelayTime = 500
mvarVisibleTime = 5000
End Sub
Private Sub Class_Terminate()
Destroy
End Sub
Public Sub Destroy()
If m_lTTHwnd 0 Then
DestroyWindow m_lTTHwnd
End If
End Sub
Public Property Get VisibleTime() As Long
VisibleTime = mvarVisibleTime
End Property
Public Property Let VisibleTime(ByVal lData As Long)
mvarVisibleTime = lData
End Property
Public Property Get DelayTime() As Long
DelayTime = mvarDelayTime
End Property
Public Property Let DelayTime(ByVal lData As Long)
mvarDelayTime = lData
End Property
... and voila ...
Posted in Office (All) | No Comments »