Fred:

From my first look, it appears that your code is correct. I assume that
the text box in question shows up on the screen and behaves correctly, except
for the responding to the event. Where is the "Sub txtZoom_AfterUpdate()"
code located? It has to be inside of the code module (actually, the form's
class module) so that it can be recognized for the event. If you have it in
a separate module, then Excel can't find it to execute. If it is in the
class module, I'd place a few DEBUG.PRINT or MSGBOX statements in and around
the click event code to make sure that I'm where I want to be. Also, I'd
build a temporary button on the form to "manually" activate (simulate) the
AfterUpdate event just to make sure that everything is working as planned.

Steve in Ohio

"Fred Holmes" wrote:

I'm trying to get the AfterUpdate event to work in a TextBox on a
UserForm. It works fine if the TextBox is created using the Controls
Toolbox and placing the TextBox on the form in the "conventinal"
manner. However, I'm trying to use a TextBox that is created with a
Set statemnt in the UserForm code.

Dim WithEvents txtZoom As MSForms.TextBox

Set txtZoom = Me.Controls.Add("forms.TextBox.1", "txtZoom", True)
With txtZoom
.Visible = True
.Top = 3
.Left = 50
.Height = 18
.Width = 40
.Text = "100"
.TextAlign = fmTextAlignCenter
.BackStyle = fmBackStyleOpaque
.BackColor = &H80000005 ' White
.BorderStyle = fmBorderStyleSingle
.BorderColor = &H80000006 ' black
.SpecialEffect = fmSpecialEffectSunken
.Font.Size = 10
.Font.Bold = True
.EnterFieldBehavior = fmEnterFieldBehaviorSelectAll
.EnterKeyBehavior = False
.HideSelection = True
.SelectionMargin = True
.TabKeyBehavior = False
.IMEMode = fmIMEModeNoControl

End With

Private Sub txtZoom_AfterUpdate() ' AfterUpdate, not Change
' This sholud execute the zoom function by pressing Enter
' This works fine on the Old Form, but not on the New Form
' in which all of the controls are created by Set statements
' in Code. AfterUpdate does not "fire" on the new Form.
Me.Zoom = txtZoom.Text
Me.Height = hf * (txtZoom.Text / 100) ' hf = initial form height
Me.Width = wf * (txtZoom.Text / 100) ' wf = initial form width
End Sub ' Private Sub txtZoom_AfterUpdate()

The properties listed in the code for txtZoom are those taken from the
Properties dialog of the verision in which the controls are created on
the form in the "customary" fashion. I tried to make sure that any
property that looked at all "relevant" was set to an identical value
in both instances.

MS Office 2003, SP-1 WinXP, SP-2

Thanks for any help.

Fred Holmes

Steve,

Thanks for the comments. Over the past few years I have been using
and trying to create and improve a better "Data, Form" way to read and
update an Excel database/list. It's a UserForm, customized with text
boxes that are sized according to the amount of data expected in that
particular field. They are multiline text boxes that wordwrap and
have vertical scroll bars. They functionally replace the single line,
non-scrolling boxes in "data, form." It has been very useful on a
number of different Excel "lists," customized by hand for each list.

It has always been squirrely. One example of its failure is that
after developing the userform for a while, I can no longer change the
properties of a control on the userform in conventional manner, using
the properties box in the VBA editor. The property appears to be
changed, as I edit the line items in the properties box, but it
doesn't take. Even after saving the Workbook, the values that are
really there in the properties of the control are the prior values.
Hence I decided to "start over" and create only the userform itself
using the VBA editor and "veiw object" and then creating all of the
controls with Set statements in the code for the UserForm, so that
everything (all of the properties for all of the controls) would be
located in one place. Command Buttons, Spin Buttons and Scroll Bars
(name? the ones that output a number) all trigger events properly --
they work just fine. The Text Boxes don't.

If I could get this code to work well, there are a lot of things I
could do with sending Excel workbooks to folks who have MSOffice
"standard" (or whatever), that doesn't have ACCESS. And many folks
who have ACCESS don't use it. I've always felt that MS doesn't take
the effort to fix all this so that more folks will give up and use
ACCESS.

To answer your questions, the two instances of my UserForm (in two
different workbooks, each with the same data/list) are identical
(substantially) except for in the first instance, the controls are
created with "view object" and the controls toolbox, and in the second
instance the controls are all created with Set statements in the code
for the UserForm (Right-Click on the UserForm in the Project Explorer,
and select "view code.") In the former, the TextBox "AfterUpdate"
event triggers/works, in the latter it doesn't. All of the
event-triggered statements are in the code for the UserForm. I
haven't really tested other TextBox events yet. For the moment I have
made the UserForm work by adding command buttons. Clicking on the
appropriate command button performs the "AfterUpdate" function.

I've even tried a simple instance in a new workbook (just the one
TextBox on the form), and AfterUpdate doesn't seem to work. I was
hoping maybe there is an obvious mistake on my part. No such luck, I
guess.

Many thanks for your help.

Fred Holmes

On Wed, 24 Nov 2004 07:27:04 -0800, "Stephen Knapp"
wrote:

Fred:

From my first look, it appears that your code is correct. I assume that
the text box in question shows up on the screen and behaves correctly, except
for the responding to the event. Where is the "Sub txtZoom_AfterUpdate()"
code located? It has to be inside of the code module (actually, the form's
class module) so that it can be recognized for the event. If you have it in
a separate module, then Excel can't find it to execute. If it is in the
class module, I'd place a few DEBUG.PRINT or MSGBOX statements in and around
the click event code to make sure that I'm where I want to be. Also, I'd
build a temporary button on the form to "manually" activate (simulate) the
AfterUpdate event just to make sure that everything is working as planned.

Steve in Ohio

"Fred Holmes" wrote:

I'm trying to get the AfterUpdate event to work in a TextBox on a
UserForm. It works fine if the TextBox is created using the Controls
Toolbox and placing the TextBox on the form in the "conventinal"
manner. However, I'm trying to use a TextBox that is created with a
Set statemnt in the UserForm code.

Dim WithEvents txtZoom As MSForms.TextBox

Set txtZoom = Me.Controls.Add("forms.TextBox.1", "txtZoom", True)
With txtZoom
.Visible = True
.Top = 3
.Left = 50
.Height = 18
.Width = 40
.Text = "100"
.TextAlign = fmTextAlignCenter
.BackStyle = fmBackStyleOpaque
.BackColor = &H80000005 ' White
.BorderStyle = fmBorderStyleSingle
.BorderColor = &H80000006 ' black
.SpecialEffect = fmSpecialEffectSunken
.Font.Size = 10
.Font.Bold = True
.EnterFieldBehavior = fmEnterFieldBehaviorSelectAll
.EnterKeyBehavior = False
.HideSelection = True
.SelectionMargin = True
.TabKeyBehavior = False
.IMEMode = fmIMEModeNoControl

End With

Private Sub txtZoom_AfterUpdate() ' AfterUpdate, not Change
' This sholud execute the zoom function by pressing Enter
' This works fine on the Old Form, but not on the New Form
' in which all of the controls are created by Set statements
' in Code. AfterUpdate does not "fire" on the new Form.
Me.Zoom = txtZoom.Text
Me.Height = hf * (txtZoom.Text / 100) ' hf = initial form height
Me.Width = wf * (txtZoom.Text / 100) ' wf = initial form width
End Sub ' Private Sub txtZoom_AfterUpdate()

The properties listed in the code for txtZoom are those taken from the
Properties dialog of the verision in which the controls are created on
the form in the "customary" fashion. I tried to make sure that any
property that looked at all "relevant" was set to an identical value
in both instances.

MS Office 2003, SP-1 WinXP, SP-2

Thanks for any help.

Fred Holmes

I've checked, and the

Sub txtZoom_Change()

event fires just fine.

I have a feeling it is a matter of how the AfterUpdate event is
sensed, i.e., how pressing the Enter key is sensed. I may next try
direct intercept of the Enter key. I don't think I've done it before
in VBA/Excel, but I have code for it in VB6. It will take some
"digging" to find it, however.