+ Reply to Thread
Results 1 to 9 of 9

Userform - Exit control on TextBox values

Hybrid View

robertvdb Userform - Exit control on... 04-21-2021, 04:12 AM
torachan Re: Userform - Exit control... 04-21-2021, 07:51 AM
romperstomper Re: Userform - Exit control... 04-21-2021, 08:21 AM
torachan Re: Userform - Exit control... 04-21-2021, 10:55 AM
romperstomper Re: Userform - Exit control... 04-21-2021, 11:29 AM
Corbett Re: Userform - Exit control... 04-21-2021, 11:06 AM
ByteMarks Re: Userform - Exit control... 04-21-2021, 12:28 PM
torachan Re: Userform - Exit control... 04-23-2021, 04:52 AM
robertvdb Re: Userform - Exit control... 04-26-2021, 06:31 AM
  1. #1
    Registered User
    Join Date
    04-03-2021
    Location
    ghent, belgium
    MS-Off Ver
    2010
    Posts
    19

    Userform - Exit control on TextBox values

    Hi all,

    I have a Userform with about 100 textboxes, in which the user has to put a certain number, formatted as 0.00%

    On the first textbox, I control the valid entry with the code below. This works fine, so I'll use the code on the 99 others as well. Is there any way to automate this, rather than copying this code 99 times ?

    Thanks in advance.

    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.TextBox1
    .Value = Format(.Value, "0.00%")
    End With
    End Sub

  2. #2
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,411

    Re: Userform - Exit control on TextBox values

    See the attached - you can put the textboxes into a class module -unfortunately the Exit event does not work in the class but if you doubleclick on your textbox after entry it works.
    torachan.
    Attached Files Attached Files

  3. #3
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,014

    Re: Userform - Exit control on TextBox values

    It is possible to create a class that will handle the Exit event for textboxes added at runtime, but the code is on another forum so I can't post a link.
    Everyone who confuses correlation and causation ends up dead.

  4. #4
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,411

    Re: Userform - Exit control on TextBox values

    @rorya, you are a tease - give us a clue - I am of an age with little time left to be searching for the 'holy' grail.
    torachan.

  5. #5
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,014

    Re: Userform - Exit control on TextBox values

    Quote Originally Posted by torachan View Post
    @rorya, you are a tease - give us a clue - I am of an age with little time left to be searching for the 'holy' grail.
    torachan.
    Jaafar Tribak at Mr Excel.

  6. #6
    Registered User
    Join Date
    04-19-2021
    Location
    Dublin, Ireland
    MS-Off Ver
    O365, Win10 and Mac
    Posts
    47

    Re: Userform - Exit control on TextBox values

    The _exit event doesn't work on a class since focus changing is really an event at form level. You need to monitor each keystroke and mouse click to see when the focus has changed. To make the attached into a real system you would need to also monitor when buttons and other controls get the focus.
    Attached Files Attached Files

  7. #7
    Forum Expert ByteMarks's Avatar
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    O365 32bit (Windows)
    Posts
    3,090

    Re: Userform - Exit control on TextBox values

    A non-class method might be to have the text boxes contained in a frame and then run something in the frame's exit event?

    Private Sub Frame1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim ctrl As Control
    For Each ctrl In Me.Frame1.Controls
        If TypeOf ctrl Is MSForms.TextBox Then
            ctrl.Value = Format(ctrl.Value, "0.00%")
        End If
    Next
    End Sub
    Last edited by ByteMarks; 04-21-2021 at 06:51 PM.

  8. #8
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,411

    Re: Userform - Exit control on TextBox values

    @rorya, Jaafar Tribak at Mr Excel interesting application, I did not understand any of it but have filed it for future ref.
    @ByteMarks, method works a treat by moving focus onto a control outside of the frame.
    torachan.

  9. #9
    Registered User
    Join Date
    04-03-2021
    Location
    ghent, belgium
    MS-Off Ver
    2010
    Posts
    19

    Re: Userform - Exit control on TextBox values

    thanks Bytemarks, your suggestion solved my problem ! Regards.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Exit Userform control and revert to previous control value code - ESC key equiv
    By ddeluca in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-31-2020, 08:18 PM
  2. textbox exit event by clicking another control
    By Rhudi in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-29-2019, 10:09 AM
  3. Textbox exit event prevents to exit userform
    By smjjvh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-21-2014, 06:08 AM
  4. [SOLVED] Exit Userform TextBox using message box
    By Stratfordoaks in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-24-2013, 01:34 PM
  5. Userform Textbox Exit Event to Run Application
    By baisty182 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-29-2010, 12:30 PM
  6. Userform textbox event that fires after I exit the textbox
    By jerseyguy1996 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-04-2010, 08:08 AM
  7. Exit Event with a single textbox control
    By ChazE911@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-08-2006, 09:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1