View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default Setting a Control Variable for a listbox

Is this a listbox from the Control toolbox toolbar (placed on a worksheet) or a
listbox on a userform?

If yes to either:
Private Function BuildWhereCondition(LB As MsForms.Listbox) As String

Using this:
Private Function BuildWhereCondition(LB As Listbox) As String
means that you want to pass a listbox (on a worksheet) from the Forms toolbar.




jonefer wrote:

I want to be able to use different listbox controls with this function, but I
don't know how to set the name of the listbox so that it can be variable for
the routine: The routine completly works if I were to replace LB (in the
body) with the name of the listbox

Here is my function : (I have placed ??? where I am unsure)

Private Function BuildWhereCondition(LB As Listbox???) As String
'Set up the WhereCondition Argument for the reports

Dim strWhere As String
Dim lSelectedCount As Long
Dim i As Variant 'The index of whatever they select

set LB = ????

lSelectedCount = 0
'MsgBox "listcount -1 " & lb.ListCount - 1

For x = 0 To LB.ListCount - 1
If LB.Selected(x) = True Then
lSelectedCount = lSelectedCount + 1
i = x
End If

Next x

Select Case lSelectedCount

Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & LB.List(i) & "'"
' MsgBox strWhere
Case Else 'Multiple Selection
strWhere = " IN ("

For x = 0 To LB.ListCount - 1
If LB.Selected(x) = True Then
strWhere = strWhere & "'" & LB.List(x) & "', "
End If
Next x

strWhere = Left(strWhere, Len(strWhere) - 2) & ")"

End Select

BuildWhereCondition = strWhere

End Function


--

Dave Peterson