View Full Version : [SOLVED:] Class Event for ComboBox Enter (Excel 2013)
davis1118
03-30-2018, 08:10 PM
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
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
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
03-31-2018, 07:55 AM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.