I am my own worst enemy! Putting code together from snippets that I dont always understand.
I have some cool code that counts the number of items to be displayed in a list box and then sets the list box (height) property accordingly - I do this so the list box stays of a fixed dimension in my form.
Only thing is it doesnt then display the vertical scroll bar when the Box height (BoxH) exceeds the actual size of the form.
Heres the code:
I have some cool code that counts the number of items to be displayed in a list box and then sets the list box (height) property accordingly - I do this so the list box stays of a fixed dimension in my form.
Only thing is it doesnt then display the vertical scroll bar when the Box height (BoxH) exceeds the actual size of the form.
Heres the code:
Code:
'Count how many items to appear in the listbox
ItemCount = 0
For c = FirstRow To LastRow
If ws.Range("B" & c).Text = frmContract.cboCProject.Value Then
If ws.Range("J" & c).Value <> "" Then
ItemCount = ItemCount + 1
End If
End If
Next c
If ItemCount = 0 Then
Exit Sub
End If
ReDim MyList(0 To ItemCount, 3) As String
Dim M%, n%
'<< DEFINE THE LIST >>
With ActiveSheet
M = -1
For k = 0 To LastRow
n = k + 6
If Range("B" & n).Value = cboCProject.Value Then
M = M + 1
MyList(M, 0) = .Range("A" & n)
MyList(M, 1) = .Range("E" & n)
MyList(M, 2) = .Range("F" & n)
MyList(M, 3) = .Range("L" & n)
End If
Next k
End With
'<< SET LIST BOX PROPERTIES >>
BoxH = (M + 2) * 12
With lbContract
.ColumnCount = 4
.ColumnWidths = "20;90;200;50"
'.Width = 260
.Height = BoxH
.MultiSelect = fmMultiSelectSingle
.ControlTipText = "Click to select a contract"
End With
'<< POPULATE THE LIST BOX >>
lbContract.List = MyList
Me.lbContract.Visible = True
Me.Frame5.Visible = True
End Sub