Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel ActiveX TextBox Question
I have a TextBox Control in Access and I'm trying to do something similar in
Excel. In Access I can use the event txbMyTextBox_AfterUpdate() and then process the full string after the user hits "Enter". But in Excel I just get the event txbMyTextBox_Change(). So it would seem that I have to process each character, store the results in a string array and check for the "Enter" key. 1) Is this correct - or am I missing something? 2) What is the syntax of a string array so that I can read in the data? 3) What is the string compare function that I can use to compare 2 strings? Thanks for any help you can provide |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel ActiveX TextBox Question
You don't need a change event for a cell to change if you use the LinkedCell
property in the text box. You need to get into design mode and set the linkedCell property 1) View - Toolbars - Control toolbox. 2) On the toolbar press the triangle which is a toggle button which enters/exits design mode. You should see the sizing dots on the textbox control when in design mode 3) With the sizing button visible on the textbox control press the Properties ICON on the toolbar(the one with the hand). The property window for the calendar control should be visible. 4) In the LinkedCell prperty enter a cell location like sheet1!I1 5) Exit design mode by pressingTriangle on the toolbar. 6) Now when ever you enter data in the textbox the linked cell value will change. "dhstein" wrote: I have a TextBox Control in Access and I'm trying to do something similar in Excel. In Access I can use the event txbMyTextBox_AfterUpdate() and then process the full string after the user hits "Enter". But in Excel I just get the event txbMyTextBox_Change(). So it would seem that I have to process each character, store the results in a string array and check for the "Enter" key. 1) Is this correct - or am I missing something? 2) What is the syntax of a string array so that I can read in the data? 3) What is the string compare function that I can use to compare 2 strings? Thanks for any help you can provide |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel ActiveX TextBox Question
Joel,
Thanks for your reply. The problem is I want to verify the user's input (This is a small piece of a larger process). So I want to check when the user is done entering data (when he hits the enter key) In Access there is an "AfterUpdate" event that does exactly that. But in Excel it seems that there is not. So I believe I need to process the "Change" event and watch for the "Enter" key. So what is the syntax for a string array ? Then I can code a loop - put the characters in the array - and do a string compare. "Joel" wrote: You don't need a change event for a cell to change if you use the LinkedCell property in the text box. You need to get into design mode and set the linkedCell property 1) View - Toolbars - Control toolbox. 2) On the toolbar press the triangle which is a toggle button which enters/exits design mode. You should see the sizing dots on the textbox control when in design mode 3) With the sizing button visible on the textbox control press the Properties ICON on the toolbar(the one with the hand). The property window for the calendar control should be visible. 4) In the LinkedCell prperty enter a cell location like sheet1!I1 5) Exit design mode by pressingTriangle on the toolbar. 6) Now when ever you enter data in the textbox the linked cell value will change. "dhstein" wrote: I have a TextBox Control in Access and I'm trying to do something similar in Excel. In Access I can use the event txbMyTextBox_AfterUpdate() and then process the full string after the user hits "Enter". But in Excel I just get the event txbMyTextBox_Change(). So it would seem that I have to process each character, store the results in a string array and check for the "Enter" key. 1) Is this correct - or am I missing something? 2) What is the syntax of a string array so that I can read in the data? 3) What is the string compare function that I can use to compare 2 strings? Thanks for any help you can provide |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel ActiveX TextBox Question
You cna add a control button with the caption = ENTER. Then verify the data
in the control button event and move the data to the worksheet in the control button macro. "dhstein" wrote: Joel, Thanks for your reply. The problem is I want to verify the user's input (This is a small piece of a larger process). So I want to check when the user is done entering data (when he hits the enter key) In Access there is an "AfterUpdate" event that does exactly that. But in Excel it seems that there is not. So I believe I need to process the "Change" event and watch for the "Enter" key. So what is the syntax for a string array ? Then I can code a loop - put the characters in the array - and do a string compare. "Joel" wrote: You don't need a change event for a cell to change if you use the LinkedCell property in the text box. You need to get into design mode and set the linkedCell property 1) View - Toolbars - Control toolbox. 2) On the toolbar press the triangle which is a toggle button which enters/exits design mode. You should see the sizing dots on the textbox control when in design mode 3) With the sizing button visible on the textbox control press the Properties ICON on the toolbar(the one with the hand). The property window for the calendar control should be visible. 4) In the LinkedCell prperty enter a cell location like sheet1!I1 5) Exit design mode by pressingTriangle on the toolbar. 6) Now when ever you enter data in the textbox the linked cell value will change. "dhstein" wrote: I have a TextBox Control in Access and I'm trying to do something similar in Excel. In Access I can use the event txbMyTextBox_AfterUpdate() and then process the full string after the user hits "Enter". But in Excel I just get the event txbMyTextBox_Change(). So it would seem that I have to process each character, store the results in a string array and check for the "Enter" key. 1) Is this correct - or am I missing something? 2) What is the syntax of a string array so that I can read in the data? 3) What is the string compare function that I can use to compare 2 strings? Thanks for any help you can provide |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel ActiveX TextBox Question
You can use the Exit event to test whether the user should be allowed
to exit the text box and move to the next control. For example, Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) With Me.TextBox1 If .Text < "abc" Then .SelStart = 0 .SelLength = Len(.Text) Cancel = True End If End With End Sub Here, if the text is not "abc", the text box remains in focus. Sometimes it may be useful to have an override of this validation so that the user (or, often, just the developer) can exit the text box even if the validation fails. I usually provide this by testing the state of the SHIFT key. If it is down, the validation is skipped. Go to http://www.cpearson.com/excel/KeyTest.aspx and download the zip file. Unzip it to some folder and then Import the modKeyState module into your project. Then test the state of the SHIFT key: Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) If IsShiftKeyDown() = True Then Exit Sub End If With Me.TextBox1 If .Text < "abc" Then .SelStart = 0 .SelLength = Len(.Text) Cancel = True End If End With End Sub Here, if the SHIFT key is not pressed, focus remains in the text box unless the content is "abc". If the SHIFT key is down, focus moves to the next control. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 29 Jan 2009 04:46:01 -0800, dhstein wrote: I have a TextBox Control in Access and I'm trying to do something similar in Excel. In Access I can use the event txbMyTextBox_AfterUpdate() and then process the full string after the user hits "Enter". But in Excel I just get the event txbMyTextBox_Change(). So it would seem that I have to process each character, store the results in a string array and check for the "Enter" key. 1) Is this correct - or am I missing something? 2) What is the syntax of a string array so that I can read in the data? 3) What is the string compare function that I can use to compare 2 strings? Thanks for any help you can provide |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TextBox Question | Excel Discussion (Misc queries) | |||
Textbox question | Excel Discussion (Misc queries) | |||
Textbox question | Excel Discussion (Misc queries) | |||
Textbox question? | Excel Discussion (Misc queries) | |||
UserForm TextBox/ComboBox question | Excel Discussion (Misc queries) |