+ Reply to Thread
Results 1 to 44 of 44

TAB and Entered Randomly Not Working in Userforms

  1. #1
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    TAB and Entered Randomly Not Working in Userforms

    Firslty forgive me if this has been posted. I have been surfing a resolution for some time, and while I see several suggestions none seem to get to the bopttom of the problem. So here it is:

    Excel 2007 (but I think the issue is across versions)

    I have userforms in my excel vb app containing a number of controls. The issue I am seeing predominantly relates to a small form that is used to login to the app..i.e. username and password. It has a frame with a combobox and textbox within, and another frame with two command buttons. It couldn't be more basic.

    The issue is that randomly I am not able to tab or enterkey away from the textbox, thus passing focus to my 'OK' command button. tab's just insert a Tab in the field, Enter key has no effect.

    Before responding to this post bear in mind the following:

    - The textbox controls are correct to allow this to work...tabkeybehavior etc.
    - Just to make sure I set these values in code also in the form activate event
    - The issue only seems to happen on the first time the form is used when opening excel and sometimes not even then
    - I have used vba.replace to force the tab to shift focus to the command button, which does work, but interestingly when the commandbutton gets focus I can't press it with the enter key...only a mouse click
    - Enter key should behave like a tab but the cursor just stays in the textbox
    - Now here's the especially interesting aspect of the issue; If I click away from excel to another app (e.g.IE) and back again, it works fine. Does anyone have code that can emulate this seemlessly? i'd bite your hand off for it
    - I have also noted the changes to the forms code, sometimes makes the problem go away, albeit temporarily

    So in summary, i am pretty convinced I'm not looking at code or parameter issue. I am more and more convinced that it is just a Excel VB bug. Has anyone else had this issue, and if so did you find a viable solution or workaround?

    Thanks in advance

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    Can you attach a sample workbook?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    I have attached the workbook. It's not quite done yet, but I only have some tidying up to, so pretty much what is there is what it will be. The file itself is a whereabouts sheet. The version I have attached I can get to fail every time as follows:

    - Open the workbook. As its the first time of use the uname and pw will be admin/admin. I used to have the problem on this form, but this seems to have stopped
    - After looging in you will be prompted to load a year (after which the wb save as to that year), then the first admin user details
    - Once you have completed the admin user creation that user will be set up with a pw of changeme
    - you will asked if you want to enter bank holidays but you can ignore that
    - Once this is done you will be return to the worksheet
    - Close and save the workbook
    - The next time you go in the 'Admin' user won't be there but your newly created user will. Use changeme as a pw and you will go to the change pw screen.
    - This is where I get the issue. pressing tab just enters a tab chr in the fields and enter does nothing

    Hopefully you will the issue. if it works for you then I will throw myself off a cliff ;c)

    Thanks

    Steve
    Attached Files Attached Files

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: TAB and Entered Randomly Not Working in Userforms

    Steve

    When I try to open the new file from the recent files menu or on double click Excel just hangs.

    If I open Excel then open the file the cursor seems to be scrolling/jumping about in column A, then Excel just hangs.

    What is the code in the workbook open event meant to do?

    Seems to be a lot of looping in it and I'm pretty sure one of those loops is where things are hanging.

    By the way, when I do finally manage to open the worksheet and open UserForm6 (took me a while to find that) I couldn't replicate the problem, Tab worked fine and I was able to select things without problem.

  5. #5
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    Thats weird. i just opened the file directly from the forum thread and it opens fine to the login screen. The first time of running all that should happen is that it goes to cell A7 of the whereabouts worksheet. if it's empty, which it is in this version, the code just moves around a few key cells so establish team numbers and sizes. What do you get if you just open off the forum?

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: TAB and Entered Randomly Not Working in Userforms

    It wasn't the uploaded file I was having problems with, it was the newly created file.

  7. #7
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    BTW, the two key forms are userform5 (inital login form) and userform9 (change of password) form

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: TAB and Entered Randomly Not Working in Userforms

    PS Must be a nightmare finding the right form, you should probably give the forms more descriptive names.

  9. #9
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    Aaah, that might explain your issues. Interestingly before I posted the whole file i produced a cut down version. when i did this it worked fine. It's not a problem I've had before. Try running the full file and you will see the issue...hopefully

  10. #10
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    Quote Originally Posted by Norie View Post
    PS Must be a nightmare finding the right form, you should probably give the forms more descriptive names.
    Haha, yeah I know, but when I started with this i was only going to have a couple of forms ;c)

  11. #11
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: TAB and Entered Randomly Not Working in Userforms

    do you really need the frames?
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  12. #12
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: TAB and Entered Randomly Not Working in Userforms

    ludders

    What 'full file'?

    The only problem I had was with the newly createe file hanging.

    Joseph

    That was the first thing that sprung to mind.

  13. #13
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    Do I need them?.....No, Do I want them?....Yeah I think they look pretty. Do you think they are contributing to the issue?

  14. #14
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    Full file = the Whereabouts.xlsm I attached to my second post

  15. #15
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    When you say 'Newly Created' file, do you mean the file that is created when you load a new year?

  16. #16
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: TAB and Entered Randomly Not Working in Userforms

    As I said earlier I had no problem with that file, it was the file that was created after I'd entered the required information that I had the problem with.

    As for frames, they can be a bit unpredictable

  17. #17
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    Ah, i'm thinking you are referring to that file. I also bet your dates have listed in US format? i always have issues with date format on excel. Just when i think I have forced them to show one way of another excel goes and proves me wrong. So the looping your are talking about is that when the file opens it establishes the start and end of the leave year 1st March to 28/9th Feb. It's looking for 01/03 in the first 5 chrs of the cells in col A. If you dates show 03/01 and 02/28 then that is where the issue is...damn, I really thought I had forced excel to store the dates in UK format

  18. #18
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    OK, I tried removing the frames and it made no difference

  19. #19
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: TAB and Entered Randomly Not Working in Userforms

    Why would my dates be in US format?

  20. #20
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    I'm just speculating. When the created file that you said is looping opens it looks in column A for left(activecell.value,5)="01/03". If you loaded a new year it would have to be there if dates were in UK format. I assumed that if it didn't it was because of a date format issue.

    BTW if I run the change password form (userform9) on its own it works fine. It's only when it comes up during the login process the issue occurs

  21. #21
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    OK, so i haven't changed anything other than to put a few stops int he program so I can test to see if tabkeybehavior is changing itself. I backed them out, and now when I run the attached file I get the same issue when i do the first admin/admin log in. Can I ask one of you guys to take a look and see if ti does it for you please?

    Thanks
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    Just to add, if I just tab through when its blank it works OK, but as soon as I type my first character then tab creates a character rather than tabbing of the field

  23. #23
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: TAB and Entered Randomly Not Working in Userforms

    What is the code in the workbook open event meant to do?

    I'm pretty sure whatever it is can be done without all the selecting and looping.

    For example, if you are looking for a specific date you could calculate how many days it was from the date in A7 then offset by that no of rows.

    Please Login or Register  to view this content.
    Also, you mention the password change userform, again I had no problem with that.

  24. #24
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: TAB and Entered Randomly Not Working in Userforms

    What exactly is the issue?

    I can tab/select in the first with no problem in the latest file you attached.

    Tab doesn't create a new character after I've filled in the login details.

    I can submit the login form, enter a year, add a new person etc all with no problem.

    The only problem I have with the foms is when trying to set the bank holidays I can't see the dates.

    Finally I've found the problem.

    When I open the newly created file and goto change the password then a character is being added when I hit TAB.

    However when I close that form, stop all other code and show it again everything is fine.

    Are you closing the othe forms when opening that form?

    Are you actually changing TabKeyBehaviour?

    I commented out the code for the activate event and everything seems fine.

  25. #25
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    The whereabouts itslef works by the user right clicking the worksheet in the day they want to enter the status. This brings up a userform (userform6). To ensure that the right click event only applies to the correct cells, the open routine identifies which row the 1st march is on, and which row the 28/9 feb is on. i do this by going to A7 and looping down (for 01/03), then A381 and looping up (for 28/9/02). In both cases it's only a handful of rows. To allow for 01/03 starting on different days of the week, the actual sheet is bigger than 365/6 days, hence the reason for the check.

    The only other looping is by column to establish what the last 'right clickable column is, for the same reason.
    The only time I had the problem you are experincing is if I run the open routing manually when a blank sheet is open. i have never had it continually loop when valid dates are in column A. Do you have 01/02/yyyy in column A of the worksheet?

  26. #26
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    Quote Originally Posted by Norie View Post
    What exactly is the issue?

    I can tab/select in the first with no problem in the latest file you attached.

    Tab doesn't create a new character after I've filled in the login details.

    I can submit the login form, enter a year, add a new person etc all with no problem.

    The only problem I have with the foms is when trying to set the bank holidays I can't see the dates.

    Finally I've found the problem.

    When I open the newly created file and goto change the password then a character is being added when I hit TAB.

    However when I close that form, stop all other code and show it again everything is fine.

    Are you closing the othe forms when opening that form?

    Are you actually changing TabKeyBehaviour?

    I commented out the code for the activate event and everything seems fine.

    Yes it's the only open form. I was using hide, but then tried unload me for previous forms with no effect. The problem seems to move around. You will also find when you get this issue that all you have to do is move away from excel to another program (outlook, word, whatever) then come back it magically starts working normally

  27. #27
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: TAB and Entered Randomly Not Working in Userforms

    I don't get the problem now that I've commented out th code in the activate event.

    By the way, you really don't need looping to do any of the things you describe.

    PS You also don't need SetFocus, to set a particular control to have focus when a form open right click the form, select Tab Order and do it there.
    Last edited by Norie; 04-11-2013 at 11:22 AM.

  28. #28
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: TAB and Entered Randomly Not Working in Userforms

    I didn't see any weird behavior using 2010

  29. #29
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    Haha, I'm sure there are better ways of doing a lot of things I've done. I don't touch vb for months so i go with what I know.

    You need to be a bit careful when you change things and it starts working. i've found I make a change, it starts to work, then at some random time it stops working again. It's almost as if just changing something...anything has some affect...but only temproarily.

    Re the setfocus. The tab indexes are set up correctly on the form, but before I started using Unload Me i was using Hide. When i did this and left a form and return to it it retained the last focus status. Going through and replacing the hides with unload me is one of the tidy up tasks...as are program comments etc

    Also regarding the bank holiday form. I was a bit scared to use the excel VB calendar object. I read up on it and found that they have updated that control. the one I am using is the old one. Apparently in the later versions of excel you can't use that control at all, which is probably why you are getting a blank box. I'm on a coporate network here so i am working on the basis that all of my users have access to that control....hopefully!

  30. #30
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: TAB and Entered Randomly Not Working in Userforms

    I'm not getting a blank box, I've got the control.

    I'm just not seeing any dates, which makes selecting a bank holiday a bit of a lottery.

    You really should think about doing something about all the selecting/looping etc.

    That could actually be causing problems.

  31. #31
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    Here's an interesting point. Being that my work PC is dog slow, I just went in to the field that I am having problems with. When I just tab through it all is good. When I type one character, for a split second I see the hourglass, then it's screwed. Something is definately going on the background. I even put a msgbox showing the tabkeybehavior value in it and it always stays as false. It's going to be the end of me.

  32. #32
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: TAB and Entered Randomly Not Working in Userforms

    Are you changing TabKeyBehaviour at any time?

    What code is associated with the control(s) that you are having problems with?

    Have you tried putting some breakpoints(F9) in the code?

  33. #33
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    Quote Originally Posted by Norie View Post
    I'm not getting a blank box, I've got the control.

    I'm just not seeing any dates, which makes selecting a bank holiday a bit of a lottery.

    You really should think about doing something about all the selecting/looping etc.

    That could actually be causing problems.
    That suggest to me that the date value is not being return from the control. The list box to the left uses balck text. That's not a problem here, albeit very weird.

    I'll see what i can do with the looping, but I'm not sure I'll have time to do something about it. I fairly satisfied it has nothing to do with my tabbing issue...which seems to becoming my lifes work!

  34. #34
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: TAB and Entered Randomly Not Working in Userforms

    No the date value is returned, I'm just not seeing the dates in the calendar grid.

  35. #35
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    There is no keypress or equivalent code at all. The only reason I put the msgbox in with the change event was so i could save the file and run it fresh. The problem only seems to occur when it goes through to theat form from file opening. the funny thing is that putting the msgbox in the change event means that it shows after every keypress. When I did that it worked fine. When I take it out it's back to being screwed again. Grrrr

    The only time a change tabkeybehavior is to hardcode it on the activate to ensure it was set to false, just in case it was changin randomly before that. however, as per my other post, if I alt tab away from excel, then back again, the problem goes away. What code issue could possible cause that!!

  36. #36
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    OK, I must be confused. When you push the Add button it should show in the list box on the right hand side of the form. The date you are adding is the year and month at the top of the control, plus the day that is dark (i.e.pressed) in the day buttons below. Which part cant you see?

  37. #37
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    Just to add...the silly way the control works is that if you change the month no days is selected on that month, if you then press the add button it retains the date the control was on before changing the month rather than returning null. It's a bit clunky. I could probably do something to make it nice. I have to shoot so I can get the start of the Master ;c) Thanks for you patience this after noon. if you get any brainstorms please let me knwo

    Many Thanks

    Steve

  38. #38
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: TAB and Entered Randomly Not Working in Userforms

    This is what the bank holiday form looks like for me.
    Attached Images Attached Images

  39. #39
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    Quote Originally Posted by Norie View Post
    This is what the bank holiday form looks like for me.
    Hmm, yeah that looks like a control issue. I certainly haven't set colours like that. You'll have to trust me that it looks ok here ;c)

  40. #40
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: TAB and Entered Randomly Not Working in Userforms

    Aye, I'll believe you.

    Actually I do because I've had the same problem before.

    Also can't get the properties of the calendar to stick, need to code them.

  41. #41
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    I've had a look around and can see some other posts with this issue, both excel and access...no real answers though ;c) I'll wait to I'll put it out there for play test until I worry too much. I'm actually also blocking out the tab issue as well ;c) The issue doesn't seem to happen in the main app, only the first login ash an issue of first change password. If it creeps in to other forms I will spend more time on it. I've already spent more time on this than half the code. Once again thanks for your help. I'll keep an eye on this thread, so if you get any revelations please share.

    Thanks

    Steve

  42. #42
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    Steve

    In the past when I've encountered a problem like this I've kind of given up and rebuilt the form from scratch.

    It's worked for me in the past, not just with userforms.

    Just had a thought, are any of the controls linked to cells?

    Also, any event code, other than that for the userforms?

  43. #43
    Registered User
    Join Date
    04-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: TAB and Entered Randomly Not Working in Userforms

    Well, the form links to a cell range that contains the users. It might be worth doing just as you suggest. nether form I am getting the issue with contains a lot of code, or will take long to redo. I guess it would be OK to copy the code from the old form anyway. Thanks for your help

    Quote Originally Posted by Norie View Post
    Steve

    In the past when I've encountered a problem like this I've kind of given up and rebuilt the form from scratch.

    It's worked for me in the past, not just with userforms.

    Just had a thought, are any of the controls linked to cells?

    Also, any event code, other than that for the userforms?

  44. #44
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: TAB and Entered Randomly Not Working in Userforms

    had the same issue,
    do not know what triggers

    included tabkeybehaviour = false into form initialisation
    seems to happen much less frequently than before but does not resolve

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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