Setting a VBA Control as a variable
 

I have tried to get a VBA control located on a sheet set into a variable. I
first declare a variable as a listbox, then try to set it as a listbox. It
throws an error at that point. I want to save mysel a lot of coding by
coverting repetitive procedures into methods, btu this is throwing a monkey
wrench into things. Thanks for any help.

Here is a snipet of code of what I'm trying to do:


Private Sub cmdPull_Click()
'VAR
Dim lboPullType As ListBox

'BEGIN
'Initialize Listbox
Set lboPullType = Me.OLEObjects("lboWBSID")

'get Departments listed in Listbox
GetChosenDept arDept(), lboPullType

End Sub

Sub GetChosenDept(ByRef Dept() As String, LBox As OLEObject)

'VAR
Dim inx As Integer
Dim iCount As Integer
Dim bDeptLimited As Boolean
Dim sDept As String

'BEGIN

'For each item in the Listbox
For inx = 0 To LBox.ListCount - 1
'If item is selected, add to array
If LBox.Selected(inx) Then
iCount = iCount + 1
ReDim Preserve Dept(iCount)
Dept(iCount) = LBox.List(inx)
End If
Next
If at least one department was chosen, set bool
If iCount 0 Then
bDeptLimited = True
Else
bDeptLimited = False
End If
'Are we pulling a Heirarchy?
If Me.cboDeptHierarchy And bDeptLimited Then
For inx = 1 To UBound(arDept)
sDept = Dept(inx)
Next
Erase Dept()
'Get all departments under this one.
GetSubDepts sDept, Dept()
End If


End Sub

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

Setting a VBA Control as a variable
 

Have you tried declaring the list box as
MSForms.ListBox?


"J Streger" wrote:

I have tried to get a VBA control located on a sheet set into a variable. I
first declare a variable as a listbox, then try to set it as a listbox. It
throws an error at that point. I want to save mysel a lot of coding by
coverting repetitive procedures into methods, btu this is throwing a monkey
wrench into things. Thanks for any help.

Here is a snipet of code of what I'm trying to do:


Private Sub cmdPull_Click()
'VAR
Dim lboPullType As ListBox

'BEGIN
'Initialize Listbox
Set lboPullType = Me.OLEObjects("lboWBSID")

'get Departments listed in Listbox
GetChosenDept arDept(), lboPullType

End Sub

Sub GetChosenDept(ByRef Dept() As String, LBox As OLEObject)

'VAR
Dim inx As Integer
Dim iCount As Integer
Dim bDeptLimited As Boolean
Dim sDept As String

'BEGIN

'For each item in the Listbox
For inx = 0 To LBox.ListCount - 1
'If item is selected, add to array
If LBox.Selected(inx) Then
iCount = iCount + 1
ReDim Preserve Dept(iCount)
Dept(iCount) = LBox.List(inx)
End If
Next
If at least one department was chosen, set bool
If iCount 0 Then
bDeptLimited = True
Else
bDeptLimited = False
End If
'Are we pulling a Heirarchy?
If Me.cboDeptHierarchy And bDeptLimited Then
For inx = 1 To UBound(arDept)
sDept = Dept(inx)
Next
Erase Dept()
'Get all departments under this one.
GetSubDepts sDept, Dept()
End If


End Sub

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

Setting a VBA Control as a variable
 

Thank you very much. That did it!

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003



"Alok" wrote:

Have you tried declaring the list box as
MSForms.ListBox?


"J Streger" wrote:

I have tried to get a VBA control located on a sheet set into a variable. I
first declare a variable as a listbox, then try to set it as a listbox. It
throws an error at that point. I want to save mysel a lot of coding by
coverting repetitive procedures into methods, btu this is throwing a monkey
wrench into things. Thanks for any help.

Here is a snipet of code of what I'm trying to do:


Private Sub cmdPull_Click()
'VAR
Dim lboPullType As ListBox

'BEGIN
'Initialize Listbox
Set lboPullType = Me.OLEObjects("lboWBSID")

'get Departments listed in Listbox
GetChosenDept arDept(), lboPullType

End Sub

Sub GetChosenDept(ByRef Dept() As String, LBox As OLEObject)

'VAR
Dim inx As Integer
Dim iCount As Integer
Dim bDeptLimited As Boolean
Dim sDept As String

'BEGIN

'For each item in the Listbox
For inx = 0 To LBox.ListCount - 1
'If item is selected, add to array
If LBox.Selected(inx) Then
iCount = iCount + 1
ReDim Preserve Dept(iCount)
Dept(iCount) = LBox.List(inx)
End If
Next
If at least one department was chosen, set bool
If iCount 0 Then
bDeptLimited = True
Else
bDeptLimited = False
End If
'Are we pulling a Heirarchy?
If Me.cboDeptHierarchy And bDeptLimited Then
For inx = 1 To UBound(arDept)
sDept = Dept(inx)
Next
Erase Dept()
'Get all departments under this one.
GetSubDepts sDept, Dept()
End If


End Sub

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003