| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Change Event in Form
Hi,
I've tried a variety of things including Application.EnableEvents = False . . . change textbox . . . Application.EnableEvents = True but I have not been able to prevent the change event from firing. What am I missing? Is it possible to stop the Change event from within the form code? Thanks, David |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Disable Change Event in Form
The Change event will fire when it fires. You cannot control it directly.
You can ignore it by not having a Change event handler, or you can have a module-level or global variable in the Change event handler whose value determines whether or not the code actually does anything. You can set the value of this variable depending on other events and conditions. Private Sub Text1_Change() If mAllowChangeEvent Then 'do stuff End If End Sub Private Sub Text2_Change() If mIgnoreChangeEvent Then Exit Sub 'do stuff End Sub wrote in message anews.com... Hi, I've tried a variety of things including Application.EnableEvents = False . . . change textbox . . . Application.EnableEvents = True but I have not been able to prevent the change event from firing. What am I missing? Is it possible to stop the Change event from within the form code? Thanks, David |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Disable Change Event in Form
Bob,
Thanks, t'is as I feared. Is there any predictable behavior for the _Change event firing. I.e., *immediately* after a change rather than *sometime* after a change? I'm trying to add functionality to a data entry form that is designed to use a numeric keypad. The idea is to simulate keypresses for keys not available on the numeric keypad by using "+", "-", etc. to act as TAB, BACK-TAB, etc. Capturing the contents of the various textboxes as they change and removing the *special* character before performing the associated function works, but always causes another _Change event. I've tried handling a variety of events, but so far _Change gives the best results. Thanks again, David ---------------------------------------------------------------------- The Change event will fire when it fires. You cannot control it directly. You can ignore it by not having a Change event handler, or you can have a module-level or global variable in the Change event handler whose value determines whether or not the code actually does anything. You can set the value of this variable depending on other events and conditions. Private Sub Text1_Change() If mAllowChangeEvent Then 'do stuff End If End Sub Private Sub Text2_Change() If mIgnoreChangeEvent Then Exit Sub 'do stuff End Sub wrote in message anews.com... Hi, I've tried a variety of things including Application.EnableEvents = False . . . change textbox . . . Application.EnableEvents = True but I have not been able to prevent the change event from firing. What am I missing? Is it possible to stop the Change event from within the form code? Thanks, David -- =3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3 F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F= 3F=3F=3F=3F David Hansen |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Disable Change Event in Form
Hi David,
You can use the KeyPress event to do something like that: Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii Case Asc("+") TextBox2.SetFocus KeyAscii = 0 Case Asc("-") TextBox3.SetFocus KeyAscii = 0 End Select End Sub If the + key is pressed while in TextBox1, focus will move to TextBox2. If - is pressed, focus goes to the previous TextBox. Setting KeyAscii=0 within the event routine will supress the output of that keystroke. You can also use this event routine to make sure only numeric digits or +/- are pressed: Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii Case Asc("+") TextBox2.SetFocus KeyAscii = 0 Case Asc("-") TextBox3.SetFocus KeyAscii = 0 Case Else If InStr(1, "0123456789.", Chr$(KeyAscii)) = 0 Then Interaction.Beep KeyAscii = 0 End If End Select End Sub -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] David Hansen wrote: Bob, Thanks, t'is as I feared. Is there any predictable behavior for the _Change event firing. I.e., *immediately* after a change rather than *sometime* after a change? I'm trying to add functionality to a data entry form that is designed to use a numeric keypad. The idea is to simulate keypresses for keys not available on the numeric keypad by using "+", "-", etc. to act as TAB, BACK-TAB, etc. Capturing the contents of the various textboxes as they change and removing the *special* character before performing the associated function works, but always causes another _Change event. I've tried handling a variety of events, but so far _Change gives the best results. Thanks again, David ---------------------------------------------------------------------- The Change event will fire when it fires. You cannot control it directly. You can ignore it by not having a Change event handler, or you can have a module-level or global variable in the Change event handler whose value determines whether or not the code actually does anything. You can set the value of this variable depending on other events and conditions. Private Sub Text1_Change() If mAllowChangeEvent Then 'do stuff End If End Sub Private Sub Text2_Change() If mIgnoreChangeEvent Then Exit Sub 'do stuff End Sub wrote in message anews.com... Hi, I've tried a variety of things including Application.EnableEvents = False . . . change textbox . . . Application.EnableEvents = True but I have not been able to prevent the change event from firing. What am I missing? Is it possible to stop the Change event from within the form code? Thanks, David |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Disable Change Event in Form
Hi Jake,
Excellent idea! I didn't know setting KeyAscii to 0 would surpress the keystroke. HELP has nothing about it. HELP also says *KeyANSI* instead of *KeyAscii*, so I shouldn't be too surprised. I generalized your idea into a function I could call from all of the text boxes and it seems to work nicely. Thanks again, David --- code snippets --- Private Sub Textbox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) KeyAscii = CheckKey(KeyAscii) End Sub Function CheckKey(ByVal KeyAscii As MSForms.ReturnInteger) As Integer Dim i As Integer i = InStr("/+*-.", Chr(KeyAscii)) ' 12345 CheckSpecialKeyx = 0 ' Assume a match and we want to kill ' this key Select Case i Case 0 ' We're not interested CheckSpecialKeyx = KeyAsciiin this key Case 1 ' / = Toggle component textboxes ToggleComponents Case 2 ' + = Process data, dismiss form CheckOut Case 3 ' * = Toggle penalty textboxes TogglePenalties Case 4 ' - = Back-TAB SendKeys "+{TAB}", False Case 5 ' . = Delete textbox contents ActiveControl = "" ActiveControl.BackColor = rgbWhite End Select End Function ------------------------------------------------------------------------ Hi David, You can use the KeyPress event to do something like that: Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii Case Asc("+") TextBox2.SetFocus KeyAscii = 0 Case Asc("-") TextBox3.SetFocus KeyAscii = 0 End Select End Sub If the + key is pressed while in TextBox1, focus will move to TextBox2. If - is pressed, focus goes to the previous TextBox. Setting KeyAscii=0 within the event routine will supress the output of that keystroke. You can also use this event routine to make sure only numeric digits or +/- are pressed: Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii Case Asc("+") TextBox2.SetFocus KeyAscii = 0 Case Asc("-") TextBox3.SetFocus KeyAscii = 0 Case Else If InStr(1, "0123456789.", Chr$(KeyAscii)) = 0 Then Interaction.Beep KeyAscii = 0 End If End Select End Sub -- =3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3 F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F=3F= 3F=3F=3F=3F David Hansen |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| KeyDown event on form | Excel Discussion (Misc queries) | |||
| Disable SelectionChange Event | Excel Discussion (Misc queries) | |||
| I need to disable to X on a user form | Excel Discussion (Misc queries) | |||
| change event/after update event?? | Excel Programming | |||
| Disable Worksheet change event | Excel Programming | |||
Hybrid Mode
