Textbox object event - Help please

[sarndt]

HI Everyone...

I've been struggling with this for awhile and still don't understand why
this won't work. Any help in making it work would be appreciate...

1. Code adds frame containing textbox with events for the textbox to
worksheet. This works.

2. Class changes text property of textbox to "aaa" when textbox added. Text
gets displayed using TB_Change() event. This works.

3. If I change the text in textbox to something else, the textbox change
event doesn't fire to display changed text.

--------- Worksheet -----------
Private mcolEvents As Collection
Private clsEvents As clsTxt
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim objFrame As OLEObject
Dim objTextBox As Control

If mcolEvents Is Nothing Then
Set mcolEvents = New Collection
End If

Set objFrame = ActiveSheet.OLEObjects.Add(classtype:="Forms.Frame.1")
Set objTextBox = objFrame.Object.Controls.Add("Forms.Textbox.1")
objFrame.Activate

Set clsEvents = New clsTxt
Set clsEvents.TBControl = objFrame.Object.Controls(0)
mcolEvents.Add ctbnew

End Sub

--------- Class Module -----------
Option Explicit
Public WithEvents TB As MSForms.TextBox
Private Sub TB_Change()
MsgBox TB.Text
End Sub
Public Property Set TBControl(objNewTB As MSForms.TextBox)
Set TB = objNewTB
MsgBox TB.Name
TB.Text = "aaa"
End Property

 

[Paul Robinson]

Hi
Did you create an instance of your class module? long time since I've
done anything with class modules - try searching this group for
"create an instance" or "instantiate" maybe? Probably in Excel
2000/2003/2007? VBA by Green et al too.
regards
Paul

 

[sarndt]

I did. Not sure how to make this work. Even tried removing the code out of
the worksheet_selection change event and put it in a module. Still can't
figure out how to make this work.