Class Event for ComboBox Enter (Excel 2013)

[davis1118]

I'm falling short with finding any info to help me with a userform issue. I have 14 comboboxes on a userform that are named "PDFLink1" through 7 and "MDLink1" through 7. I'm using the Enter event to open the FileDialog and place the selected file path into the comboboxes. I was trying to create a class event for the Enter event rather than create 14 individual events. But there isn't a Enter event with the WithEvents function, and for some reason I am unable to get the Click event in the userform module to work, which is why I am using the Enter event.
I don't have any of the Class code to put on here because I was stuck at the very beginning since the Enter event is not available when using WithEvents. But below is just the code for Enter event on the userform module. Just wondering if there is any way around this.

Thanks for the help!

'INSERT PDF LINK #1
Private Sub PDFLink1_Enter()
Dim fDialog As FileDialog, result As Integer
With ENGForm
    If .PDFLink1.Value = "" Then
        Set fDialog = Application.FileDialog(3)
            With fDialog
                .AllowMultiSelect = False
                .Title = "Select File"
                .InitialFileName = "F:\"
                .Filters.Clear
                If fDialog.Show = -1 Then
                    ENGForm.PDFLink1.Value = .SelectedItems(1)
                End If
            End With
    End If
End With
End Sub

[pike]

hi Its quite involved,

userform class RaiseEvent OnEnter control

userform class event code from Jaafar Tribak RaiseEvent on enter or exit of control.
This is especially useful when using multiple cascading combobox list which are dependant on each others selection
combobox's don't have this feature

Userform code

Option Explicit
Public Event OnEnter(ctrl As msforms.Control)
Public Event OnExit(ctrl As msforms.Control)
Private oXitClass As CtlExitCls
Private bFormUnloaded As Boolean
Private oPrevActiveCtl As msforms.Control
Private oCol As New Collection

Sub Combobox_List(myCtrl As Control)

    Dim myRng As Range, objCtrl As Control
    Set myRng = ThisWorkbook.Names("petSkill").RefersToRange
    Dim e, v
    With myRng
        v = .Value
    End With
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For Each e In v
            If Not .exists(e) Then .Add e, Nothing
        Next e
        For Each objCtrl In Me.Controls
            If TypeName(objCtrl) Like "ComboBox" And Not objCtrl.Name Like myCtrl.Name And .exists(objCtrl.Value) Then
                .Remove (objCtrl.Value)
            End If
        Next

        If .Count Then
            myCtrl.List = Application.Transpose(.keys)
        End If
    End With
End Sub
Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub UserForm_Layout()
    Call WatchEvents
End Sub

Private Sub UserForm_Terminate()
    Call CleanUp
End Sub

Private Sub WatchEvents()
    If Not oXitClass Is Nothing Then Exit Sub
    Set oXitClass = New CtlExitCls
    Set oXitClass.FormCtrl = Me
    bFormUnloaded = False
    Set oPrevActiveCtl = Me.ActiveControl
    RaiseEvent OnEnter(Me.ActiveControl)
    Do While bFormUnloaded = False
        If Not oPrevActiveCtl Is Nothing Then
            If Not oPrevActiveCtl Is Me.ActiveControl Then
                RaiseEvent OnExit(oPrevActiveCtl)
                RaiseEvent OnEnter(Me.ActiveControl)
                Me.ActiveControl.SetFocus
            End If
        End If
        Set oPrevActiveCtl = Me.ActiveControl
        DoEvents
    Loop
End Sub

Private Sub CleanUp()
    bFormUnloaded = True
    RaiseEvent OnExit(oPrevActiveCtl)
    Set oXitClass = Nothing
    Set oCol = Nothing
    Set oPrevActiveCtl = Nothing
End Sub

Class Module named CtlExitCls

Option Explicit
Public WithEvents FormCtrl As UserForm1
Private Sub FormCtrl_OnEnter(ctrl As msforms.Control)
' MsgBox "You Exited the Control :  " & "(" & Ctrl.Name & ")"

    Select Case True
    Case TypeName(ctrl) Like "ComboBox"
        Call FormCtrl.Combobox_List(ctrl)
    Case TypeName(ctrl) Like "TextBox"
     '   MsgBox "You Entered the Control :  " & "(" & Ctrl.Name & ")"
    Case TypeName(ctrl) Like "CommandButton"
      '  MsgBox "You Entered the Control :  " & "(" & Ctrl.Name & ")"
    End Select

End Sub
Private Sub FormCtrl_OnExit(ctrl As msforms.Control)
' MsgBox "You Exited the Control :  " & "(" & Ctrl.Name & ")"

 Select Case True
    Case TypeName(ctrl) Like "ComboBox"
       '   MsgBox "You Exited the ComboBox Control :  " & "(" & Ctrl.Name & ")"
    Case TypeName(ctrl) Like "TextBox"
     '   MsgBox "You Exited the TextBox Control :  " & "(" & Ctrl.Name & ")"
    Case TypeName(ctrl) Like "CommandButton"
      '  MsgBox "You Exited the CommandButton Control :  " & "(" & Ctrl.Name & ")"
    End Select

End Sub

[snb]

Why not ?

Private Sub PDFLink1_Enter()
    M_select 1
End Sub

Private Sub PDFLink2_Enter()
    M_select 2
End Sub

Private Sub PDFLink3_Enter()
    M_select 3
End Sub

Private Sub PDFLink4_Enter()
    M_select 4
End Sub



Sub M_select(y)
  With Application.FileDialog(3)
      .InitialFileName = "F:\*.pdf"
      If .Show Then Me("PDFLink" & y) = .SelectedItems(1)
  End With
End Sub

[davis1118]

Pike,
You're not kidding. That is quite involved. Thank you for taking the time to find and send the code to me, but I will use snb's method. I'm not sure I can follow along with all of the code, but I will definitely keep it handy for any future use.

snb,
Thank you very much for that simple solution. It works perfect and my code looks much cleaner now.

Thank you both for the time and help! I'm never disappointed with the help on this forum.