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" wrote in message
oups.com...
| 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
|