The afterUpdate is an event of the control object in a useform. The control
is the container for the activeX control when it is in a userform and the
Textbox inherits this event from the control object. In the worksheet, the
textbox is contained in the OLEObject - which has no afterupdate event.


Tabbing: Previous post by Rob Bovey:

From: Rob Bovey )
Subject: Worksheet Controls


View this article only
Newsgroups: microsoft.public.excel.programming
Date: 2000/07/22


Hi Fabio,

<<1 - Is there a way to establish the tab order of worksheet controls, or
even
have the Tab or Enter key select them continuously ( as they do in cells)?

There's no automatic way of doing it that I know. You have to do it
yourself
using the KeyDown event for each of the controls on your worksheet. If the
KeyCode argument of the KeyDown event for a given control tells you that TAB
or
ENTER has been pressed, you explicitly activate the control that you want to
be
next in line. Here's a simple example:

Private Sub TextBox2_KeyDown( _
ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
''' Check if the TAB or ENTER key was pressed.
If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then
''' If this is Excel 97 you must select a cell
''' before attempting to activate another control.
If Val(Application.Version) < 9 Then
Me.Range("A1").Select
End If
''' Move the focus appropriately.
If CBool(Shift And 1) Then
''' The user was holding down the SHIFT key
''' move back to the previous control.
TextBox1.Activate
Else
''' Move to the next control.
TextBox3.Activate
End If
End If
End Sub

<<2- Can I have a number inside a TextBox formatted?

Yes, but again you need to handle this yourself. Here's an example for
currency formatting:

Private Sub TextBox1_Change()
Dim szValue As String
szValue = Trim$(TextBox1.Text)
If Len(szValue) 0 And IsNumeric(szValue) Then
TextBox1.Text = Format$(CDbl(szValue), "$#,##0.00")
End If
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/



--------------------

[ ] is a shortcut for Application.Evaluate

which acts like a virtual cell. This is slower than using object
references.

[A1].Value is slower than Range("A1").Value (although I wouldn't see it
as a big problem).

Range("Name1").Range would be faster than [Name1]

--
Regards,
Tom Ogilvy


C. Bailey wrote in message
news:5ue3b.67483$K44.10248@edtnps84...
I borrowed the following code (slightly modified names) from one of Tom's
posts a couple years back:

Private Sub TextBox1_AfterUpdate()
Dim rng As Range
With Worksheets("Bank")
Set rng = .Cells(Rows.Count, "B").End(xlUp)(2)
End With
rng.Value = TextBox1.Text
End Sub

I am curious why I can't get it to work? I have inserted the text box on
the spreadsheet - it seems like most put them on forms. Could that be the
problem? It also seems that no matter what I do (enter, tab, arrow keys,
etc.), you can't leave the text box unless you click outside of it. I
suspect this is why it does not register as an update.

For future reference, how can I get it to skip to the next text box by
pressing the tab or enter key?

Note: The above code will work if I change the first line to Private Sub
TextBox1_Change(), but I don't really care to have one letter per line :)

When I see code in this newsgroup, it seems like few people name their
ranges and refer to them as [namerange] in square brackets? I was curious
why? It seems easier to me. Is it because it is not defined? What is
the
benefit to defining variables? Code always seems to work for me w/o
defined
variables, but I do relatively simple tasks.

Chris

Thank you,

What is the easiest way (i.e. least code), to move the contents of a text
box to the last cell on the screen? I created a dynamic named cell, and
assigned the textbox value to it (one line of code). However, it transfered
each leter I typed to a different row. Are there any other "events" that
the TextBox recognizes beside the "Change Event" outside of a userform? If
not, where will I find information on creating simple user forms and control
features?

Chris

"Tom Ogilvy" wrote in message
...
The afterUpdate is an event of the control object in a useform. The
control
is the container for the activeX control when it is in a userform and the
Textbox inherits this event from the control object. In the worksheet,
the
textbox is contained in the OLEObject - which has no afterupdate event.


Tabbing: Previous post by Rob Bovey:

From: Rob Bovey )
Subject: Worksheet Controls


View this article only
Newsgroups: microsoft.public.excel.programming
Date: 2000/07/22


Hi Fabio,

<<1 - Is there a way to establish the tab order of worksheet controls, or
even
have the Tab or Enter key select them continuously ( as they do in
cells)?

There's no automatic way of doing it that I know. You have to do it
yourself
using the KeyDown event for each of the controls on your worksheet. If the
KeyCode argument of the KeyDown event for a given control tells you that
TAB
or
ENTER has been pressed, you explicitly activate the control that you want
to
be
next in line. Here's a simple example:

Private Sub TextBox2_KeyDown( _
ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
''' Check if the TAB or ENTER key was pressed.
If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then
''' If this is Excel 97 you must select a cell
''' before attempting to activate another control.
If Val(Application.Version) < 9 Then
Me.Range("A1").Select
End If
''' Move the focus appropriately.
If CBool(Shift And 1) Then
''' The user was holding down the SHIFT key
''' move back to the previous control.
TextBox1.Activate
Else
''' Move to the next control.
TextBox3.Activate
End If
End If
End Sub

<<2- Can I have a number inside a TextBox formatted?

Yes, but again you need to handle this yourself. Here's an example for
currency formatting:

Private Sub TextBox1_Change()
Dim szValue As String
szValue = Trim$(TextBox1.Text)
If Len(szValue) 0 And IsNumeric(szValue) Then
TextBox1.Text = Format$(CDbl(szValue), "$#,##0.00")
End If
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/



--------------------

[ ] is a shortcut for Application.Evaluate

which acts like a virtual cell. This is slower than using object
references.

[A1].Value is slower than Range("A1").Value (although I wouldn't see it
as a big problem).

Range("Name1").Range would be faster than [Name1]

--
Regards,
Tom Ogilvy


C. Bailey wrote in message
news:5ue3b.67483$K44.10248@edtnps84...
I borrowed the following code (slightly modified names) from one of
Tom's
posts a couple years back:

Private Sub TextBox1_AfterUpdate()
Dim rng As Range
With Worksheets("Bank")
Set rng = .Cells(Rows.Count, "B").End(xlUp)(2)
End With
rng.Value = TextBox1.Text
End Sub

I am curious why I can't get it to work? I have inserted the text box
on
the spreadsheet - it seems like most put them on forms. Could that be
the
problem? It also seems that no matter what I do (enter, tab, arrow
keys,
etc.), you can't leave the text box unless you click outside of it. I
suspect this is why it does not register as an update.

For future reference, how can I get it to skip to the next text box by
pressing the tab or enter key?

Note: The above code will work if I change the first line to Private
Sub
TextBox1_Change(), but I don't really care to have one letter per line
:)

When I see code in this newsgroup, it seems like few people name their
ranges and refer to them as [namerange] in square brackets? I was
curious
why? It seems easier to me. Is it because it is not defined? What is
the
benefit to defining variables? Code always seems to work for me w/o
defined
variables, but I do relatively simple tasks.

Chris

Just assign the LinkedCell property of the textbox to the cell.

Then whatever is entered in the textbox will be displayed in the cell as
well.

LinkedCell: Sheet1!B9

--
Regards,
Tom Ogilvy


"C. Bailey" wrote in message
news:Y1s3b.81236$K44.27231@edtnps84...
Thank you,

What is the easiest way (i.e. least code), to move the contents of a text
box to the last cell on the screen? I created a dynamic named cell, and
assigned the textbox value to it (one line of code). However, it
transfered
each leter I typed to a different row. Are there any other "events" that
the TextBox recognizes beside the "Change Event" outside of a userform?
If
not, where will I find information on creating simple user forms and
control
features?

Chris

"Tom Ogilvy" wrote in message
...
The afterUpdate is an event of the control object in a useform. The
control
is the container for the activeX control when it is in a userform and
the
Textbox inherits this event from the control object. In the worksheet,
the
textbox is contained in the OLEObject - which has no afterupdate event.


Tabbing: Previous post by Rob Bovey:

From: Rob Bovey )
Subject: Worksheet Controls


View this article only
Newsgroups: microsoft.public.excel.programming
Date: 2000/07/22


Hi Fabio,

<<1 - Is there a way to establish the tab order of worksheet controls,
or
even
have the Tab or Enter key select them continuously ( as they do in
cells)?

There's no automatic way of doing it that I know. You have to do it
yourself
using the KeyDown event for each of the controls on your worksheet. If
the
KeyCode argument of the KeyDown event for a given control tells you that
TAB
or
ENTER has been pressed, you explicitly activate the control that you
want
to
be
next in line. Here's a simple example:

Private Sub TextBox2_KeyDown( _
ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
''' Check if the TAB or ENTER key was pressed.
If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then
''' If this is Excel 97 you must select a cell
''' before attempting to activate another control.
If Val(Application.Version) < 9 Then
Me.Range("A1").Select
End If
''' Move the focus appropriately.
If CBool(Shift And 1) Then
''' The user was holding down the SHIFT key
''' move back to the previous control.
TextBox1.Activate
Else
''' Move to the next control.
TextBox3.Activate
End If
End If
End Sub

<<2- Can I have a number inside a TextBox formatted?

Yes, but again you need to handle this yourself. Here's an example
for
currency formatting:

Private Sub TextBox1_Change()
Dim szValue As String
szValue = Trim$(TextBox1.Text)
If Len(szValue) 0 And IsNumeric(szValue) Then
TextBox1.Text = Format$(CDbl(szValue), "$#,##0.00")
End If
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/



--------------------

[ ] is a shortcut for Application.Evaluate

which acts like a virtual cell. This is slower than using object
references.

[A1].Value is slower than Range("A1").Value (although I wouldn't see
it
as a big problem).

Range("Name1").Range would be faster than [Name1]

--
Regards,
Tom Ogilvy


C. Bailey wrote in message
news:5ue3b.67483$K44.10248@edtnps84...
I borrowed the following code (slightly modified names) from one of
Tom's
posts a couple years back:

Private Sub TextBox1_AfterUpdate()
Dim rng As Range
With Worksheets("Bank")
Set rng = .Cells(Rows.Count, "B").End(xlUp)(2)
End With
rng.Value = TextBox1.Text
End Sub

I am curious why I can't get it to work? I have inserted the text box
on
the spreadsheet - it seems like most put them on forms. Could that be
the
problem? It also seems that no matter what I do (enter, tab, arrow
keys,
etc.), you can't leave the text box unless you click outside of it. I
suspect this is why it does not register as an update.

For future reference, how can I get it to skip to the next text box by
pressing the tab or enter key?

Note: The above code will work if I change the first line to Private
Sub
TextBox1_Change(), but I don't really care to have one letter per line
:)

When I see code in this newsgroup, it seems like few people name their
ranges and refer to them as [namerange] in square brackets? I was
curious
why? It seems easier to me. Is it because it is not defined? What
is
the
benefit to defining variables? Code always seems to work for me w/o
defined
variables, but I do relatively simple tasks.

Chris