Hi all,
I've stripped away all code irrelevant to the issue I'm having, but I'm working on a custom class that represents an autocomplete object. The object will consist of a TextBox, a ListBox, and a Label, all dynamically created. For now, I have just a ComboBox. It will place itself in any cell on BeforeDoubleClick, and autocomplete your typing based on a named range. The object gets instantiated as a global variable in Sub Main(), and uses with only two methods, Show() and Run(), in Worksheet_BeforeDoubleClick.
Now to the issue: How do I adjust the code so that the events don't have to be in the Worksheet? I want them in the main class, if possible. (I'll settle for in a separate class if I have to.) Thanks for your time. Here's the code:
First, the worksheet module:
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Debug.Print "__Begin Worksheet_BeforeDoubleClick"
MGAutoComplete.Reset
MGAutoComplete.Show Target:=Target
Application.EnableEvents = True
Debug.Print "__End Worksheet_BeforeDoubleClick"
End Sub
Second, the standard module:
Option Explicit
Public MGAutoComplete As CMGAutoComplete
Sub Main() 'button in Quick Access Toolbar launches this
Debug.Print "__Begin Main"
Application.EnableEvents = True 'just in case a previous run left it False.
Set MGAutoComplete = New CMGAutoComplete
Debug.Print "__End Main"
End Sub
Third, the class module named CMGAutoComplete:
Option Explicit
Public cmbAutoComplete As OLEObject
'Private lbxAutoComplete As OLEObject 'eventually
Private Sub AddAutoCompleteComboBox()
Debug.Print "__Begin AddAutoCompleteComboBox"
On Error Resume Next
If ActiveSheet.Shapes("AutoCompleteComboBox") Is Nothing Then
On Error GoTo 0
Set cmbAutoComplete = _
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
Link:=False, DisplayAsIcon:=False, Left:=100, Top:=100, Width:=20, Height:=20)
Else
On Error GoTo 0
'Set cmbAutoComplete = ActiveSheet.Shapes("AutoCompleteComboBox")
Set cmbAutoComplete = ActiveSheet.OLEObjects("AutoCompleteComboBox")
End If
Debug.Print "__End AddAutoCompleteComboBox"
End Sub
Private Sub InitializeAutoCompleteComboBox()
Debug.Print "__Begin InitializeAutoCompleteComboBox"
With cmbAutoComplete
.Visible = False
.Name = "AutoCompleteComboBox"
.ListFillRange = ""
.LinkedCell = ""
.Object.ListRows = 12
.Object.MatchEntry = fmMatchEntryNone
Debug.Print .Object.Text
End With
Debug.Print "__End InitializeAutoCompleteComboBox"
End Sub
Private Sub ShowAutoCompleteComboBox(Target As Range)
Debug.Print "__Begin ShowAutoCompleteComboBox"
With cmbAutoComplete
.Left = Target.Left + 10
.Top = Target.Top + 10
.Width = Target.Width + 5
.Height = Target.Height + 5
.Object.Value = Target.Value
'.Object.Text = Target.Value
.Visible = True
.Activate
End With
Debug.Print "__End ShowAutoCompleteComboBox"
End Sub
Public Sub Show(Target As Range)
Debug.Print "__Begin Show"
ShowAutoCompleteComboBox Target:=Target
Debug.Print "__End Show"
End Sub
Public Sub Reset()
Debug.Print "__Begin Reset"
InitializeAutoCompleteComboBox
Debug.Print "__End Reset"
End Sub
Public Sub Run(strMessage As String)
Debug.Print "__Begin Run"
DoAutoComplete strMessage:=strMessage
Debug.Print "__End Run"
End Sub
Sub DoAutoComplete(strMessage As String)
Debug.Print "__Begin DoAutoComplete"
Debug.Print strMessage
cmbAutoComplete.Object.DropDown
Debug.Print "__End DoAutoComplete"
End Sub
Private Sub Class_Initialize()
Debug.Print "__Begin Class_Initialize"
AddAutoCompleteComboBox
Debug.Print "__End Class_Initialize"
End Sub
Private Sub AutoCompleteComboBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Debug.Print "__Begin AutoCompleteComboBox_KeyDown"
Select Case KeyCode
Case 9
Application.ActiveCell.Offset(0, 1).Activate
Case 13
Application.ActiveCell.Offset(1, 0).Activate
' Case 40
' Application.ActiveCell.Offset(1, 0).Activate
End Select
Debug.Print "__End AutoCompleteComboBox_KeyDown"
End Sub
Private Sub AutoCompleteComboBox_GotFocus()
Debug.Print "__Begin AutoCompleteComboBox_GotFocus"
Debug.Print "__End AutoCompleteComboBox_GotFocus"
End Sub
Private Sub cmbAutoComplete_Change()
Debug.Print "__Begin cmbAutoComplete_Change"
Debug.Print "__End cmbAutoComplete_Change"
End Sub
Fourth, my attempt at separating the object's events into a separate class module. (Again, I'd rather just include these in the CMGAutoComplete class.)
Option Explicit
Public WithEvents AutoCompleteComboBox As ComboBox
'not working...
Private Sub AutoCompleteComboBox_Change()
Debug.Print "____________Begin AutoCompleteComboBox_Change (Hooray!)"
MGAutoComplete.Run AutoCompleteComboBox.Value
Debug.Print " It's working now! Thanks for your time. :)"
Debug.Print "____________End AutoCompleteComboBox_Change (Hooray!)"
End Sub
'not working...
Private Sub AutoCompleteComboBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Debug.Print "__Begin AutoCompleteComboBox_KeyDown"
Select Case KeyCode
Case 9 'Tab
Application.ActiveCell.Offset(0, 1).Activate
Case 13 'Enter
Application.ActiveCell.Offset(1, 0).Activate
Case 40 'Down
MsgBox "Down!"
'How do I give focus to dropdown and scroll with
'arrow keys (and pgup, pgdn, home, end)?
'(Actually, I will likely change the ComboBox to a TextBox-ListBox combination after this issue is resolved.)
End Select
Debug.Print "__End AutoCompleteComboBox_KeyDown"
End Sub
Bookmarks