Hi got a Class Called "FilterLine", It consist of 3 ComboBoxes which are filled depending on all other already selected filters. Here a simplification of the code:
Option Explicit
Public WithEvents Filter As MSForms.Combobox
Public WithEvents Operator As MSForms.Combobox
Public WithEvents Options As MSForms.Combobox
Public index As Integer
' This sub adds a new FilterLine and formats it
Public Sub Add()
' Do Stuff with form in which it is embedded
frmFilter.Height = frmFilter.Height + 50
End Sub
' Other subs
Where frmFilter is the Userform. This is rather unelegant since I refer to the userform as a name and therefore the class could not be used without changes in another userform. The Userform should always be the userform where the default filter is located. But how does one get the userform from a ComboBox?
For example with a range object you can do this:
dim rng as Range
set rng = 'Whatever you want
rng.Worksheet.activate
This will activate the worksheet where the range is located. But how does one get the UserForm instance from a MSForms.ComboBox?
Edit:
To clarify I call the Userform using this code:
Sub testFilter()
Dim Filterm As FilterModel
Set Filterm = New FilterModel
With New frmFilter
Set .Model = Filterm
.SetDefaultFilter' This is the interesting part
.Show
End With
End Sub
Then the UserForm Instance.SetDefaultFilter sets the default filter with:
Public Sub SetDefaultFilterLine()
Dim DefaultFilterLine As New FilterLine
Set DefaultFilterLine.Filter = frmFilter.DefaultFilter
Set DefaultFilterLine.Operator = frmFilter.DefaultOperator
Set DefaultFilterLine.Options = frmFilter.DefaultOptions
DefaultFilterLine.index = 1
Me.Model.FilterCol.Add DefaultFilterLine
' This doesn't work because I cannot refer to the instance I newed up in the testFilter Sub.
DefaultFilterLine.Add
End Sub
DefaultFilterLine.Add doesn't work because the code uses frmFilter (which is it's default instance and has no model assigned to it). Therefore the question should probably be:
How can one refer to a Userform Instance in another module (class module in this case) ?
Edit 2:
This is the relevant code of frmFilter:
Public DisableEvents As Boolean
Private Type TView
Model As FilterModel
IsCancelled As Boolean
IsBack As Boolean
End Type
Private this As TView
Public Property Get Model() As FilterModel
Set Model = this.Model
End Property
Public Property Set Model(ByVal value As FilterModel)
Set this.Model = value
'Validate
End Property
Model is a property of the UserForm of the Type FilterModel. FilterModel (currently) only consists of a Integer N and a collection where all the FilterLine are stored.
You'd use Filter.Parent
.
As an example:
Create a class named Class1
.
Add this code:
Public WithEvents Filter As MSForms.ComboBox
Private Sub Filter_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
MsgBox Filter.Parent.Name
End Sub
UserForm1
contains a single combo-box named ComboBox1
.
Add this code to the form:
Private colEvents As Collection
Private Sub UserForm_Initialize()
Dim MyEvents As Class1
Set colEvents = New Collection
Set MyEvents = New Class1
Set MyEvents.Filter = Me.ComboBox1
colEvents.Add MyEvents
End Sub
Open the form and double-click the combobox and it will tell you the name of the form.