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.