Hi,
I have a userform which has some command buttons and multiple listboxes in VBA.
I have a class module which handles events on the form, and a code in the form's initialization section which sets the reference to the class module (depending on which control is clicked).
What I am struggling with, is as follows:
When the user clicks on a listbox, it is enabled. This is fine so far. The problem starts when I want to capture this listbox within this event procedure, and pass it onto another procedure as the listbox object.
In this way, I want to have the selected listbox's methods and properties available to the other procedure.
When I run in debug mode, I notice that eventhough I have declared another listbox object as a global object variable, to which I assign the selected listbox, the object value goes to "Nothing" after the event is over.
How can I do this? Is it possible?
The code is as follows:
Thanks for the help in advance.
I have a userform which has some command buttons and multiple listboxes in VBA.
I have a class module which handles events on the form, and a code in the form's initialization section which sets the reference to the class module (depending on which control is clicked).
What I am struggling with, is as follows:
When the user clicks on a listbox, it is enabled. This is fine so far. The problem starts when I want to capture this listbox within this event procedure, and pass it onto another procedure as the listbox object.
In this way, I want to have the selected listbox's methods and properties available to the other procedure.
When I run in debug mode, I notice that eventhough I have declared another listbox object as a global object variable, to which I assign the selected listbox, the object value goes to "Nothing" after the event is over.
How can I do this? Is it possible?
The code is as follows:
Code:
'Class Module code:
Option Explicit
' Declare Event Handlers
Public WithEvents BtnGrp As MSForms.CommandButton
Public WithEvents LbxGrp As MSForms.ListBox
' Declare Object to work with
Dim oLstBx As MSForms.ListBox
Dim iEntryCnt As Integer
Sub LbxGrp_MouseUP(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Set oLstBx = LbxGrp
End Sub
Private Sub BtnGrp_Click()
iEntryCnt = 0
Select Case BtnGrp.Name
Case "okCmdBtn"
With oLstBx '**** -> This is where the problem occurs as oLstBx is "Nothing"
.AddItem
.List(iEntryCnt, 0) = iEntryCnt + 1
.List(iEntryCnt, 1) = "OK was Clicked"
End With
Case "cancelCmdBtn"
MsgBox "You Clicked " & BtnGrp.Name
Case Else
Exit sub
End Select
End Sub
'UserForm module code:
Option Explicit
Dim Btns() As New Class1
Dim Lbox() As New Class1
Private Sub UserForm_Initialize()
Dim BtnCnt As Integer: Dim LbxCnt As Integer
Dim ctl As MSForms.Control
BtnCnt = 0: LbxCnt = 0
For Each ctl In Me.Controls
If TypeName(ctl) = "CommandButton" Then
BtnCnt = BtnCnt + 1
ReDim Preserve Btns(1 To BtnCnt)
Set Btns(BtnCnt).BtnGrp = ctl
ElseIf TypeName(ctl) = "ListBox" Then
With ctl
LbxCnt = LbxCnt + 1
.ColumnCount = 2
.ColumnWidths = "1 cm;6 cm "
ReDim Preserve Lbox(1 To LbxCnt)
Set Lbox(LbxCnt).LbxGrp = ctl
End With
End If
Next ctl
End Sub
Comment