#1
|
|||
|
|||
If/Else clear current activex textbox
I have a table in a word document macro-enabled template. Each cell has an ActiveX TextBox in it. In one specific column, I only want to accept numerical characters. If a user enters anything else, a message box pops up informing the user that this is an error.
I would also like to have the non-conforming text be deleted, or the last action to be undone. Unfortunately whatever is typed into an ActiveX control is not registered in the undo table so "Undo" doesn't work. This is my code. Brief Explanation: Cell 1 is the column title and is not changed (named QTY). Each ActiveX TextBox is in cells 2-20 in the column and is named txtQty#. This is where you enter in numbers only. Cell 21 is the Total value of the numbers entered in cells 2-20 and is named txtTotalQty. Code:
Private Sub QtyChanged(newValue As String) If Trim(newValue) <> "" And Not IsNumeric(newValue) Then 'Display a pop-up message MsgBox "This cell only accepts numerical values. Please make the correction before proceeding", vbOKOnly, "ERROR!" Else With Me txtTotalQty.Text = Format(Val(.txtQty1.Value) + Val(.txtQty2.Value) + Val(.txtQty3.Value) + Val(.txtQty4.Value) + Val(.txtQty5.Value) + Val(.txtQty6.Value) + Val(.txtQty7.Value) + Val(.txtQty8.Value) + Val(.txtQty9.Value) + Val(.txtQty10.Value) + Val(.txtQty11.Value) + Val(.txtQty12.Value) + Val(.txtQty13.Value) + Val(.txtQty14.Value) + Val(.txtQty15.Value) + Val(.txtQty16.Value) + Val(.txtQty17.Value) + Val(.txtQty18.Value) + Val(.txtQty19.Value), "#,##0") End With End If End Sub Ideally the solution should involve the current actively selected ActiveX TextBox control being completely cleared after the user clicks "OK" on the error message to dismiss it. |
#2
|
||||
|
||||
You don't actually need a message box, you need a series of macros in the ThisDocument module related to the text boxes e.g. as follows. (I have only shown the first two).
If the user enters a non-numeric character (except a full stop/period) the PC will beep and cancel that character. You could use a message box instead of the beep, but it isn't required. If you don't want to allow the full stop/period, omit the reference to '46,' in the case statement. If you want to include the thousands separator or you use a comma as a decimal point add '44, ' before the 46 i.e. Code:
Case 44, 46, 48 To 57 Code:
Option Explicit Private bTest As Boolean Private Function IsAllowed(ByVal i As String) As Boolean Select Case Val(i) Case 46, 48 To 57 IsAllowed = True Case Else IsAllowed = False End Select lbl_Exit: Exit Function End Function Private Sub txtQty1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) bTest = IsAllowed(CStr(KeyAscii)) If bTest = False Then Beep KeyAscii = 0 End If lbl_Exit: Exit Sub End Sub Private Sub txtQty2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) bTest = IsAllowed(CStr(KeyAscii)) If bTest = False Then Beep KeyAscii = 0 End If lbl_Exit: Exit Sub End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
Quote:
Thanks for the suggestions. This works well. I will likely keep the Msgbox as this will be used in an environment where a 'beep' from a computer cannot be heard. Just a quick question for you about code cleanup/length if you know. Is there a way to accomplish what you suggested in a more condensed format? I know I only have 19 fields so it's not a big deal but what if it was 100 fields? Then there would be a lot of Private Sub's to enter. |
#4
|
||||
|
||||
It should be possible to set up a class for your ActiveX controls so that you don't need to create a set of Subs for each one. If you do a search for Class Modules you should be able to find the information you need to set this up correctly.
Here are some relevant links that will either give you the answer you need or show you the relevant keywords to do a better focused Google search Textbox Class Object - TIP, TRICKS & CODE - OzGrid Free Excel/VBA Help Forum Return active OLEObject in Worksheet - Excel General - OzGrid Free Excel/VBA Help Forum Using change event of multiple ActiveX controls with no userform using class method - Excel General - OzGrid Free Excel/VBA Help Forum
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
Tags |
activex control, textbox, undo |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Event macro: insert row with formulae based on current row, clear selected contents | emsa | Excel Programming | 5 | 04-17-2020 09:03 AM |
How to track changes in a ActiveX Textbox | tommasorossotti | Word VBA | 1 | 02-24-2020 03:57 AM |
ActiveX Textbox properties using VBA | kyeung | Word VBA | 5 | 10-05-2015 12:47 AM |
Display result in textbox based on the input of another textbox | scarymovie | Word VBA | 5 | 05-16-2012 07:05 PM |
How to insert a hyperlink in activex textbox | Joe Patrick | Word VBA | 1 | 10-03-2011 06:03 AM |