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

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

It's funny. In preparing my code to put in the message, I serendipitously
fixed it!
I originally put in the listbox name in the body of hte code and used the
available properties that popped up with the dot (.)

I replaced the listbox name with LB and made my parameter 'LB as control'

Even though, control doesn't drop down the properties, if you pass a listbox
as the parmeter, it knows what to do with it.

Thanks for the tips.


"Dave Peterson" wrote:

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