Textbox macro - update problem

[DMW]

Hi there

I'm working on Excel 2000 on a W2K machine. I am currently putting
together a data entry sheet, on which there is 1 textbox to allow a
lengthy entry to be both visible and printable. However, in the
background I have a hidden sheet which collates all the data in
columns. I currently have the following macro (code kindly provided by
another group member) assigned to my textbox to copy the text into a
cell for collation purposes:

Sub getext()
Dim s As String
ActiveSheet.Shapes("Text Box 9").Select
s = Selection.Characters.Text
Range("b100").Value = s
End Sub

This suceeds in copying the text but only after I click out and then
back into the textbox. Can anyone suggest how I can change my code to
instruct an afterupdate or lostfocus event, so that the cell is
updated? I'm stumped but thats because I've not got a clue when it
comes to VB. I've tried adding afterupdate or lostfocus after sub
getext but to no avail. Any help gratefully received as this is
driving me to distraction!!

Many thanks.

Donna

 

[Jim Rech]

Can anyone suggest how I can change my code to instruct an afterupdate or

lostfocus event

The only event a text box on a sheet has is the click event. I'd suggest a
different approach if you want to use a text box on a sheet - remove the
macro from the text box and instead assign it to a button placed next to the
text box. Label the button something like "Commit" or "Click me when text
is complete".

--
Jim
| Hi there
|
| I'm working on Excel 2000 on a W2K machine. I am currently putting
| together a data entry sheet, on which there is 1 textbox to allow a
| lengthy entry to be both visible and printable. However, in the
| background I have a hidden sheet which collates all the data in
| columns. I currently have the following macro (code kindly provided by
| another group member) assigned to my textbox to copy the text into a
| cell for collation purposes:
|
| Sub getext()
| Dim s As String
| ActiveSheet.Shapes("Text Box 9").Select
| s = Selection.Characters.Text
| Range("b100").Value = s
| End Sub
|
| This suceeds in copying the text but only after I click out and then
| back into the textbox. Can anyone suggest how I can change my code to
| instruct an afterupdate or lostfocus event, so that the cell is
| updated? I'm stumped but thats because I've not got a clue when it
| comes to VB. I've tried adding afterupdate or lostfocus after sub
| getext but to no avail. Any help gratefully received as this is
| driving me to distraction!!
|
| Many thanks.
|
| Donna
|

 

[DMW]

Hi Jim

Thanks for this - its a great workaround. However, it presents me with
another problem. When you complete the text box, you cannot click
straight onto the command button - you have to click a cell to leave
the text box first and then click the command button. Is there a way
to eliminate this additional step as I want to make the use of this
form as simple as possible? Thanks you so much for your help so far.

Kind regards.

Donna

 

[Jim Rech]

you cannot click straight onto the command button

That is true for a Control Toolbox command button. But if you use a Forms
toolbar button it's not an issue. Hopefully you can switch.

--
Jim
| Hi Jim
|
| Thanks for this - its a great workaround. However, it presents me with
| another problem. When you complete the text box, you cannot click
| straight onto the command button - you have to click a cell to leave
| the text box first and then click the command button. Is there a way
| to eliminate this additional step as I want to make the use of this
| form as simple as possible? Thanks you so much for your help so far.
|
| Kind regards.
|
| Donna
|
|
| Jim Rech wrote:
| > >Can anyone suggest how I can change my code to instruct an afterupdate
or
| > >lostfocus event
| >
| > The only event a text box on a sheet has is the click event. I'd
suggest a
| > different approach if you want to use a text box on a sheet - remove the
| > macro from the text box and instead assign it to a button placed next to
the
| > text box. Label the button something like "Commit" or "Click me when
text
| > is complete".
| >
| > --
| > Jim
|

 

[DMW]

Hi Jim

One word - fantastic! That has worked and solved the problem for me.
Thank you so much - your help is truly appreciated.

Kind regards.

Donna

 

[DMW]

Hi Jim

It would appear that I have spoken too soon. The code is only copying
a certain amount of the text - not all of it. Do you have any ideas?

Thanks.

Donna