Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox macro - update problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox macro - update problem
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 | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox macro - update problem
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox macro - update problem
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 "DMW" wrote in message ps.com... | 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 | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox macro - update problem
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 Jim Rech wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox macro - update problem
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 Jim Rech wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Textbox Bug? Missing/delayed update of textbox filled via VBA | Excel Programming | |||
Textbox Bug? Missing/delayed update of textbox filled via VBA | Excel Programming | |||
Dynamic update of UserForm TextBox | Excel Programming | |||
Textbox on excel sheet do not update with VBA | Excel Programming | |||
Textbox object does not update | Excel Programming |