Referencing a listbox with a variable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have two listboxes on separate tabs that contain records that feed a
report when a command button is pressed on the form. The same command button
is used for both listboxes and the code knows which tab is active and is
supposed to feed the correct listboxes' records to the report. I'm using a
case statement to determine which tab is active, and from there I want to use
a variable to reference the listbox. I haven't been able to figure out how
to represent a listbox with a variable.

Here is my code (which blows up when it gets to the With statement):

Select Case Me.NavTabs.Value
Case 0
lbSelect = "Me.ProfileListBox"
Case 1
lbSelect = "Me.GRListBox"
End Select

'Loop through the ItemsSelected in the list box.
'lbSelect is of type control and varItem is a variant
With lbSelect
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the Job Code (in column 1)
strWhere = strWhere & strDelim & .Column(1, varItem) &
strDelim & ","
'Build up the description from the Job Title. See note 2.
strDescrip = strDescrip & """" & .Column(0, varItem) & """, "
selectCount = selectCount + 1
End If
Next
End With

I could put the second block of code into both case statements and change
the name of the control, but I was hoping for a more elegant solution than
that. Anyone have a suggestion? Btw, to give credit where credit is due,
most of this code can be attributed to Allan Browne (the working part :)

Thanks,

Jeff
 
Solution 1:
With Me(lbSelect)


Solution 2:
Dim lbSelect As Listbox
...
Case 0
Set lbSelect = Me.ProvileListBox
...


BTW, in case these tab pages ever get rearranged, you might like to code:
Case Me.[YourPageNameHere].PageIndex
 
Thanks Allen, that works wonderfully!

Jeff

Allen Browne said:
Solution 1:
With Me(lbSelect)


Solution 2:
Dim lbSelect As Listbox
...
Case 0
Set lbSelect = Me.ProvileListBox
...


BTW, in case these tab pages ever get rearranged, you might like to code:
Case Me.[YourPageNameHere].PageIndex

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Jeff C said:
I have two listboxes on separate tabs that contain records that feed a
report when a command button is pressed on the form. The same command
button
is used for both listboxes and the code knows which tab is active and is
supposed to feed the correct listboxes' records to the report. I'm using
a
case statement to determine which tab is active, and from there I want to
use
a variable to reference the listbox. I haven't been able to figure out
how
to represent a listbox with a variable.

Here is my code (which blows up when it gets to the With statement):

Select Case Me.NavTabs.Value
Case 0
lbSelect = "Me.ProfileListBox"
Case 1
lbSelect = "Me.GRListBox"
End Select

'Loop through the ItemsSelected in the list box.
'lbSelect is of type control and varItem is a variant
With lbSelect
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the Job Code (in column 1)
strWhere = strWhere & strDelim & .Column(1, varItem) &
strDelim & ","
'Build up the description from the Job Title. See note 2.
strDescrip = strDescrip & """" & .Column(0, varItem) & """,
"
selectCount = selectCount + 1
End If
Next
End With

I could put the second block of code into both case statements and change
the name of the control, but I was hoping for a more elegant solution than
that. Anyone have a suggestion? Btw, to give credit where credit is due,
most of this code can be attributed to Allan Browne (the working part :)

Thanks,

Jeff
Click to expand...
Click to expand...
 
Back
Top