+ Reply to Thread
Results 1 to 16 of 16

UserForm CheckBoxes populate sometimes with random boxes checked

  1. #1
    Registered User
    Join Date
    05-22-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    11

    Question UserForm CheckBoxes populate sometimes with random boxes checked

    Hello everyone.

    I have searched for this problem and solution across the web. I have tried the few solutions I found and nothing works. The occurrence also appears to be random...so here we go.

    I have some User Form with MultiSelect listboxes. When the user double clicks in a range specific to these forms, if the cell was blank, the form should populate with no values = true. Yet, I often get the forms opening with boxes checked, no discernable pattern to which are checked. I clear the selections, save, reopen...flip a coin, could be all blank (good), could be random checked boxes (bad). Same goes for a cell with the concatenated data to be read back in for editing.

    If I run in debug mode, OR set a stop in the code, and press F5 to run, the bug never happens. I tried adding time delays, no change. I tried to write to a not visible list then copy to the visible list, no change. I tried to open a msg box saying something like "Did you really want to edit this cell - but really I am just building in a stop to fix a bug I created and am not smart enough to fix", no change although (and not the real message), users DID NOT WANT TO have to click OK to close a message...they said with 1000's of lines to work through, it had to go anyway.

    The column PROGRAMS is the one I see it most in....MITIGATION STRATEGY sometimes...Planned Activities not yet, and there are Check boxes in the TrigE form, I don't see it there (I think).

    Like I said, it often takes multiple attempts to make it happen. Sometimes it only happens when I use another form then one of these listboxes. My boss is beginning to think all those years I spent studying engineering has been for naught if I can't solve problems like this - I fear I am beginning to agree with him. (:

    Any thoughts? I've attached everything, and removed the proprietary data but what you have is valid. If there is something there, not a worry. My work is in risk and it is always supposed to be transparent so nothing is in the files not already available to the public.

    As with my first and only other post, it I broke a rule, I will fix immediately.

    Thanks to everyone, your help when I searched for other problems I had has been perfect.

    Brian
    Attached Files Attached Files

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

    Re: UserForm CheckBoxes populate sometimes with random boxes checked

    Brian

    Try unloading the userform(s) instead of hiding them.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    05-22-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    11

    Re: UserForm CheckBoxes populate sometimes with random boxes checked

    Hi, and thanks Norie

    As a test, I went to the UserForms_Programs form and made the hide an unload. Here is the best example of random. I saved and closed the file after the change, then re-opened. Double click in J11, hoping to see only "Toxic Reduction" checked, and I got three checked. I attached the result.

    For Programs, there looked to be only one Hide that I needed to change. In the other forms, I have added the option to allow resizing of the forms because some managers now have smaller tablets and the forms were too big for them, that is why I used hide. In the case of the listbox forms, they are small enough to not need resizing so I guess unloading is not an issue, except it didn't make the problem go away.

    Norie.PNG

    Thanks for the quick reply too!!!

    Brian

  4. #4
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,137

    Re: UserForm CheckBoxes populate sometimes with random boxes checked

    Hi Brian

    Is it possible that you have some public or global variables in the workbook that are not cleared when the userform is closed?

    If so clear them on Userform activation.
    My Rules if you want my help:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

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

    Re: UserForm CheckBoxes populate sometimes with random boxes checked

    Brian

    How can I replicate the behaviour you describe?

  6. #6
    Registered User
    Join Date
    05-22-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    11

    Re: UserForm CheckBoxes populate sometimes with random boxes checked

    See, and first thanks. That's the issue. It is random. I have had users never have the random checkboxes checked, and others all the time.

    *** I have noticed one pattern, it almost always happens in the first few rows, but not lower down. I have a line of code to reset the active cell to the cell they user double clicked in, incase they move around the worksheet looking for information to help them. It that maybe an issue?

    So I tried a repetitive exercise. Blank program cell, Xclick, NOx and SOx and Non-Agricultural Source Material (NASM for short). Clicked Clear, Save, Repeat. Pattern arose.
    Got the same boxes checked even though blank. Then after a few tries, the next line below NASM was checked. Repeat the clear, save Xclick
    Then after 2 or 3 repeats, the next line checked instead - On Farm Deadstock. Repeat process of clear cave XClick
    Then go back to NASM

    The NOx and SOx stayed checked.

    I went into debug and stopped at the first available line in the "Public Sub Programs(Optional Target As Range)" . I put a stop at the if. Then stepped the exact same as the above. No wrong checkboxes. The stop somehow is causing allowing the code to function as I want (although it may still be no the best coding style, but I am learning) ***

    I can double click on an empty cell in the Programs column, see it with false checks, save them anyway. Go to another blank row, double click, no boxes checked. GO back to that first one, maybe the same are checked, maybe not. Maybe others, maybe none as it should have been (even thought I saved it with the false ones).

    I even just trying the same cell over and over. Blank => nothing checked. Check a few or one, same. Double click, the saved ones are checked, plus one or more others. Clear. Save. Double click, then who knows.

    I appreciate that it is hard sometimes to replicate. At a connected screen meeting last week, the problem never happened on my hosting and users thought I was crazy. I sent them the file, and they worked along, and they say the issue. I can't believe I made it thru 1.5 hours without it, yet when I tried your very valid option, I got the bug.

    I wish I could make it happen for you. I really wish non of us would have it happen. Thank you for caring.

    Brian
    Last edited by brianalton; 11-20-2017 at 07:53 PM. Reason: *** show more information and maybe pattern and ANOTHER TEST

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

    Re: UserForm CheckBoxes populate sometimes with random boxes checked

    Brian

    Why are you loading and showing the Programs userform in its own Initialize event?

    By the way, when you set the value of a control on an unloaded userform it gets loaded into memory.

  8. #8
    Registered User
    Join Date
    05-22-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    11

    Re: UserForm CheckBoxes populate sometimes with random boxes checked

    Thanks...that was me wanting to see EXACTLY when the checks occurred. I also ran debug.print, set watches, and checked the setting for each of the array entries before the UserForm shows, and it always shows those wrong checks are set to False. It is when the form shows they are wrong. Unless I step thru or stop then hit run.

    I will take the show away and just load. Thanks....Should I be reloading the file after changes???

    Brian

  9. #9
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,137

    Re: UserForm CheckBoxes populate sometimes with random boxes checked

    I do not understand your logic.

    How can you Load Useform_Programs in the Useform_Programs initialization program?

    How can you Show Useform_Programs in the Useform_Programs initialization program?

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    05-22-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    11

    Re: UserForm CheckBoxes populate sometimes with random boxes checked

    Thanks.

    Well, the Show was my test to see that the initialize was not where the random TRUEs were occurring. So, me bad. I will take it out.

    I thought I had to load the UserForm to Initialize it. But, being new. I took those lines out of the "Sub UserForm_Initialize()"

    For the 10 tries I just ran, no errors until I chose to CLEAR the selections then saved. Then reopened that cell, and the false TRUEs were back. We are getting closer to the problem.

    I am also in the process of changing the PUBLIC variables to Private and declaring them on the form to see if the same memory locations or variables are causing the issue.

    THANK mehmetcik, I will be right back.

  11. #11
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,137

    Re: UserForm CheckBoxes populate sometimes with random boxes checked

    1. What is the point of offset(0,0)?

    2. I do not see two listboxes.

    where is the second listbox? ahh it is way way to the right. are you actually using that listbox?

    3. This code loads your listbox(s) without those loops.

    Please Login or Register  to view this content.
    Last edited by mehmetcik; 11-20-2017 at 08:52 PM.

  12. #12
    Registered User
    Join Date
    05-22-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    11

    Re: UserForm CheckBoxes populate sometimes with random boxes checked

    You are amazingly fast. I replaced that section with yours. I cleared contents for the entire column. Saved file. Exited Excel, Opened the file.

    Started in J11, and the results were again random. J11 ended up with 2 checked, then next line a different 2, then the remaining until J18 were blank, and J18 was then populated with the same as J11. I changed to Private for the variables used in the module.

    I then changed the Subs to all be Private. When I called Programs from the MCRF_Code module, I even using the application.run method, I can't call it. Is it ok for sub Programs to be public? That wouldn't be the issue, I guess. The stepping or stop then run still is the only way to make it work. Can I do a timed stop in the background without a message box? I have seen posts talking about waiting for system to catch up, that is why you see the commented out sleep. Or should there not be a need to stop then continue? Again thanks Reputation * on its way for your patience with me!

    Thanks

  13. #13
    Registered User
    Join Date
    05-22-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    11

    Re: UserForm CheckBoxes populate sometimes with random boxes checked

    I thought I would update the uploaded file with all the suggestions (well for now, in the Programs FORM) for specifics.

    You are all amazing so thanks for your patience.

    Brian
    Attached Files Attached Files

  14. #14
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,137

    Re: UserForm CheckBoxes populate sometimes with random boxes checked

    Ok lets start afresh.

    The Userform activation macro does not select any of the programs the way that I am opening it.

    You have however programmed userform activation program too, I would combine them to simplify the coding.

    The Activation Macro does not look like it is acting on the listboxes, however perhaps I need to check for textbox change events.


    So how do you open the userform?

    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    05-22-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    11

    Re: UserForm CheckBoxes populate sometimes with random boxes checked

    Hi, the call is in RED below (or somecolour as I am colour blind and MS doesn't always tell you the name as you hover to select)

    This is in MCRF_Code module. The double click is picked up in MySheet module. Please let me know what else I can point out to help...did I say thanks???

    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    05-22-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    11

    Re: UserForm CheckBoxes populate sometimes with random boxes checked

    I want to thank everyone for your amazing support, replies, and patience.

    I implemented every suggestion, and my results were still unpredictable. But it appears the problem is not history. The last changes I made were to create duplicate list boxes, populate them then copy to the original list boxes. I was about to hide the new list boxes, then noticed THEY never had phantom checked boxes. I remembered another post regarding the two list box option, and the user found the newly created ones they were going to hide worked as they should. The comment was sometimes you have to delete code and recreate it. It would seem the new list boxes WERE my new code, so I kept the originals hidden, and its all good.

    I admit that recreating code to solve problems (but exactly the same lines) leaves this mechanical engineer scratching his head, but the 4th rule of engineering holds. 3rd rule is you can't fix stupid, and the 4th...If it ain't broke now, don't fix it again, but still be sure to blame someone else for putting you through the pain.

    In this case...all of you get thank...But mehmetcik and Norie for the WIN!

    Brian

+ 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. Replies: 1
    Last Post: 09-24-2015, 11:50 AM
  2. Replies: 1
    Last Post: 08-31-2015, 09:44 AM
  3. Replies: 14
    Last Post: 01-21-2015, 04:53 PM
  4. Need to Count number of Check Boxes checked on a userform
    By UMBiii in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-12-2014, 03:44 PM
  5. Replies: 4
    Last Post: 12-30-2013, 10:10 AM
  6. Dynamically create and Place Controls such as CheckBoxes,Text-Boxes in a UserForm
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 35
    Last Post: 05-11-2011, 11:34 AM
  7. Populate Userform with Checkboxes for each value in column A
    By Bluewhistler in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-20-2010, 03:02 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