Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-28-2022, 12:02 PM
DrTyDawg DrTyDawg is offline If/Else clear current activex textbox Windows 10 If/Else clear current activex textbox Office 2016
Novice
If/Else clear current activex textbox
 
Join Date: Jan 2022
Posts: 2
DrTyDawg is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 01-28-2022, 10:14 PM
gmayor's Avatar
gmayor gmayor is offline If/Else clear current activex textbox Windows 10 If/Else clear current activex textbox Office 2019
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
Reply With Quote
  #3  
Old 02-01-2022, 09:16 AM
DrTyDawg DrTyDawg is offline If/Else clear current activex textbox Windows 10 If/Else clear current activex textbox Office 2016
Novice
If/Else clear current activex textbox
 
Join Date: Jan 2022
Posts: 2
DrTyDawg is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
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

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.
Reply With Quote
  #4  
Old 02-01-2022, 03:59 PM
Guessed's Avatar
Guessed Guessed is offline If/Else clear current activex textbox Windows 10 If/Else clear current activex textbox Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
Reply

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
If/Else clear current activex textbox How to track changes in a ActiveX Textbox tommasorossotti Word VBA 1 02-24-2020 03:57 AM
If/Else clear current activex textbox ActiveX Textbox properties using VBA kyeung Word VBA 5 10-05-2015 12:47 AM
If/Else clear current activex textbox 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

Other Forums: Access Forums

All times are GMT -7. The time now is 11:23 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft