Prev: Please guide
Next: Start/End Cells in a Range
From: sarndt on 12 Apr 2010 12:57
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

From: Paul Robinson on 12 Apr 2010 17:06
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

On Apr 12, 5:57 pm, sarndt <sar...(a)discussions.microsoft.com> wrote:
> 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

From: sarndt on 13 Apr 2010 00:19
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.

"Paul Robinson" wrote:

> 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
>
> On Apr 12, 5:57 pm, sarndt <sar...(a)discussions.microsoft.com> wrote:
> > 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
>
> .
>
 | 
Pages: 1
Prev: Please guide
Next: Start/End Cells in a Range