Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 266
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 266
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7,247
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
TextBox Question Jim Excel Discussion (Misc queries) 4 July 11th 08 10:41 PM
Textbox question browie Excel Discussion (Misc queries) 1 June 13th 05 11:06 PM
Textbox question browie Excel Discussion (Misc queries) 0 June 13th 05 04:00 PM
Textbox question? Greg B Excel Discussion (Misc queries) 2 June 2nd 05 03:56 PM
UserForm TextBox/ComboBox question grasping@straws Excel Discussion (Misc queries) 1 February 2nd 05 12:14 PM


All times are GMT +1. The time now is 10:42 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"