+ Reply to Thread
Results 1 to 19 of 19

SetFocus not working on TextBox after AfterUpdate

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    SetFocus not working on TextBox after AfterUpdate

    This is driving me crazy. I have googled and found multiple similar threads but none of their solutions work for me. (The most promising was a solution from AndyPope of moving the SetFocus code to UserForm_Activate)

    I am creating an Add Contact form. There is a checkbox for Individual (if left unticked then contact is a business entity - not an individual).

    If Individual = True and If the txtNameFirst control has no value in it Then after the end user tabs away from the txtNameLastRegistered, the form should SetFocus on the txtNameFirst textbox (entry for this field is mandatory only if the contact is an individual - otherwise this field is made invisible)

    But it is not working. I tick chkIndividual, I enter a name in txtNameLastRegistered, I tab away, code is called from txtNameLastRegistered_AfterUpdate (which includes the SetFocus code) but the form appears without focus in any of the controls!

    My code below:
    Please Login or Register  to view this content.
    Last edited by mc84excel; 03-30-2014 at 07:49 PM. Reason: correct grammar
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  2. #2
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: SetFocus not working on TextBox after AfterUpdate

    Setfocus works but after focus is set next control in tab order will receive focus - unless it is hidden or disabled by called code. It is effect of buffered tab keystroke.
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  3. #3
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: SetFocus not working on TextBox after AfterUpdate

    Quote Originally Posted by Izandol View Post
    Setfocus works but after focus is set next control in tab order will receive focus - unless it is hidden or disabled by called code. It is effect of buffered tab keystroke.
    That explains it.

    Do you have any thoughts as to how I might be able to work around this?

    I have already tested KeyDown and that event fires before AfterUpdate. So if I use KeyDown to negate the Tab key (from the txtNameLastRegistered textbox) then the txtNameLastRegistered_AfterUpdate won't fire.

  4. #4
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: SetFocus not working on TextBox after AfterUpdate

    Do not try to set focus there. You may highlight control - change colour for example - and prevent submission of data while control is in error. Submit button code may be used to set focus to any control in error.

  5. #5
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: SetFocus not working on TextBox after AfterUpdate

    Quote Originally Posted by Izandol View Post
    Do not try to set focus there. You may highlight control - change colour for example - and prevent submission of data while control is in error. Submit button code may be used to set focus to any control in error.
    But I'm not trying to highlight/prevent an error as such.


    I'm trying to prompt the end user to complete the FirstName field by setting focus in that textbox (I want to avoid highlighting) if all of the following conditions are met:

    Individual=Yes
    FormStage=1 (which is triggered by value entered into LastName)
    FirstName field has no entry

  6. #6
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: SetFocus not working on TextBox after AfterUpdate

    To make it easier for some kind forum user to find a solution, I'm uploading a dummy example of the form for testing.

    The goal is if all of the following conditions are met:

    1. Individual=Yes
    2. FormStage=1 (which is triggered by value entered into LastName)
    3. First Name field has no entry made

    Then the First Name field should have the SetFocus.
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: SetFocus not working on TextBox after AfterUpdate

    What I can't understand is this:

    If the user opens the form, enters a Last Name and then clicks on Individual (or tabs away from Last Name and then click on Individual), the First Name appears and it DOES have SetFocus.

    So why doesn't the SetFocus work if the user clicks on Individual checkbox BEFORE entering a Last Name?


    UPDATE: After experimenting further, I'm convinced Izandol is onto something. If Individual checkbox is clicked first and the user TABS away from Last Name, the SetFocus is lost. If they CLICK into another field instead, the SetFocus is set to First Name like it should.
    Last edited by mc84excel; 04-08-2014 at 10:13 PM.

  8. #8
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Angry Re: SetFocus not working on TextBox after AfterUpdate

    Tried changing the tab order so that the Contact Code is the next in the tab order after the Last Name (in case switching visibility on the First Name txtbox was causing problems). Didn't work.


    In desperation I've tried to clear the Keyboard Buffer using the code in this thread http://www.mrexcel.com/forum/excel-q...rd-buffer.html

    I call the function in the txtNameLastRegistered_AfterUpdate but the SetFocus still won't work.

  9. #9
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: SetFocus not working on TextBox after AfterUpdate

    Ha! I've finally worked out a solution. :D

    Here's a coding challenge - a rep is waiting for the first forum user to work out how I did this!

  10. #10
    Registered User
    Join Date
    01-29-2015
    Location
    Pune
    MS-Off Ver
    2010
    Posts
    14

    Re: SetFocus not working on TextBox after AfterUpdate

    Quote Originally Posted by mc84excel View Post
    Ha! I've finally worked out a solution. :D

    Here's a coding challenge - a rep is waiting for the first forum user to work out how I did this!
    Hi plz share the solution

  11. #11
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: SetFocus not working on TextBox after AfterUpdate

    Quote Originally Posted by deepika.shinde View Post
    Hi plz share the solution
    Well OK. But I withdraw the coding challenge now that I'm revealing the answer.

    In the end, the work-around was quite simple (if somewhat unorthodox!). All I needed to do was: 1. change the TabIndex order at runtime, 2. SetFocus on any visible control then 3. setfocus on the control that the event is running on. This forces the SetFocus to the next control in the new TabIndex order!

    Example below:
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    01-29-2015
    Location
    Pune
    MS-Off Ver
    2010
    Posts
    14

    Re: SetFocus not working on TextBox after AfterUpdate

    Thank you for the code..
    This helped me.

  13. #13
    Registered User
    Join Date
    06-18-2017
    Location
    United States
    MS-Off Ver
    2016
    Posts
    2

    Re: SetFocus not working on TextBox after AfterUpdate

    You can overcome the problem with a pair of subs. In this example of a working pair of scripts, I have a time card entry form.
    TextBox1 is the hours in. TextBox2 is the minutes in. Because of idiots entering incorrect values, like spaces, or fat-fingering
    numbers, I had to write a validation. But I wanted it to clear the invalid text and set the focus back into the textbox where
    the invalid text had been entered. The form functioned with the same problem as noted in this thread. When the hours were entered
    incorrectly, it would clear the text, display a message box, and it was supposed to set the focus back on the hours box.
    Unfortunately, the buffered tab would make it go to the minutes box.

    So to correct this problem, I disabled TextBox2, which was the next tab index position. That prevented the focus from going there.
    Then I cleared the text value from TextBox1, to eliminate the invalid text.
    Next I set the focus back to TextBox1.

    Lastly, I did a separate sub routine to reenable TextBox2 once the sub routine of clearing and setting focus on TextBox1 was complete.

    The end result is what you are asking about. The two routines are below. And the order of operations matters. You have to clear the value
    before setting the focus. Otherwise you end up in a loop; an escapable loop, but a loop nonetheless.

    ------------------

    Private Sub TextBox1_Enter()
    TextBox2.Enabled = True
    End Sub

    ------------------

    Please Login or Register  to view this content.
    ------------------

    Works beautifully. Hope it helps, whoever has need of it.
    Last edited by davesexcel; 01-29-2020 at 03:47 AM.

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: SetFocus not working on TextBox after AfterUpdate

    alex, welcome to the forum

    Thanks for the contribution, Im sure members will find it useful

    (did you notice that this thread is over 2 years old though?)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  15. #15
    Registered User
    Join Date
    06-18-2017
    Location
    United States
    MS-Off Ver
    2016
    Posts
    2

    Re: SetFocus not working on TextBox after AfterUpdate

    Quote Originally Posted by FDibbins View Post
    alex, welcome to the forum

    Thanks for the contribution, Im sure members will find it useful

    (did you notice that this thread is over 2 years old though?)
    I did notice that the thread was older. But since I was looking for this information myself and found a few tips on the thread that led to an answer, I figured someone else might stumble across the thread as well and find the working solution useful.

  16. #16
    Registered User
    Join Date
    06-16-2015
    Location
    Denver, Colorado
    MS-Off Ver
    2013
    Posts
    1

    Re: SetFocus not working on TextBox after AfterUpdate

    Alexfrazier, thank you for posting your solution. It does work beautifully and if you hadn't posted it I'm sure I would still be struggling with the setfocus issue. Two years + two years, and still relevant and much appreciated.

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: SetFocus not working on TextBox after AfterUpdate

    Zep thanks for the feedback

  18. #18
    Registered User
    Join Date
    03-12-2020
    Location
    Setúbal. Portugal
    MS-Off Ver
    2019 Pro CR
    Posts
    3

    Re: SetFocus not working on TextBox after AfterUpdate

    I figured someone else might stumble across the thread as well and find the working solution useful. That's the spirit. You nailed it.

  19. #19
    Registered User
    Join Date
    06-26-2022
    Location
    Medellín, Colombia
    MS-Off Ver
    365
    Posts
    1

    Re: SetFocus not working on TextBox after AfterUpdate

    Quote Originally Posted by alexfrazier View Post
    You can overcome the problem with a pair of subs. In this example of a working pair of scripts, I have a time card entry form.
    TextBox1 is the hours in. TextBox2 is the minutes in. Because of idiots entering incorrect values, like spaces, or fat-fingering
    numbers, I had to write a validation. But I wanted it to clear the invalid text and set the focus back into the textbox where
    the invalid text had been entered. The form functioned with the same problem as noted in this thread. When the hours were entered
    incorrectly, it would clear the text, display a message box, and it was supposed to set the focus back on the hours box.
    Unfortunately, the buffered tab would make it go to the minutes box.

    So to correct this problem, I disabled TextBox2, which was the next tab index position. That prevented the focus from going there.
    Then I cleared the text value from TextBox1, to eliminate the invalid text.
    Next I set the focus back to TextBox1.

    Lastly, I did a separate sub routine to reenable TextBox2 once the sub routine of clearing and setting focus on TextBox1 was complete.

    The end result is what you are asking about. The two routines are below. And the order of operations matters. You have to clear the value
    before setting the focus. Otherwise you end up in a loop; an escapable loop, but a loop nonetheless.

    ------------------

    Private Sub TextBox1_Enter()
    TextBox2.Enabled = True
    End Sub

    ------------------

    Please Login or Register  to view this content.
    ------------------

    Works beautifully. Hope it helps, whoever has need of it.


    Thank you! it is the easiest way to solve this problem.

+ 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. VBA Setfocus to next textbox
    By pjbassdc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-07-2013, 12:47 PM
  2. AfterUpdate/BeforeUpdate and SetFocus
    By odekkers in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-05-2010, 03:32 PM
  3. AfterUpdate event for dynamic TextBox
    By moyo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-20-2007, 02:23 AM
  4. TextBox.SetFocus not working
    By vesoljc in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-11-2007, 01:49 AM
  5. Form, SetFocus and AfterUpdate issue
    By Piers 2k in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-08-2005, 09:55 AM

Tags for this Thread

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