Search code examples
excelvbauserform

How does one get the UserForm where MSForms.ComboBox Object is located?


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.


Solution

  • 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.