+ Reply to Thread
Results 1 to 14 of 14

Userform Screen Updating - stop disappearing between each data input

  1. #1
    Forum Contributor
    Join Date
    12-14-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    106

    Userform Screen Updating - stop disappearing between each data input

    HI I have a userform that contains various listboxes, text boxes and option buttons.

    At end of entering data I have a command button called next entry and one called exit. When I click on next entry I want the data within the userform to reset to what it is when it is initialised.

    Currently my code is short and sweet:

    Please Login or Register  to view this content.
    This works great but I have the annoyance (or maybe I am being fussy) of the userform disappearing for a split second and then reappearing. Can i use screen-updating or some other code to stop this happening?

    Cheers
    Last edited by chrisjames25; 09-20-2013 at 08:04 AM.

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

    Re: Userform Screen Updating - stop disappearing between each data input

    it would be better to create a routine that resets all the controls instead of unloading/reloading the form
    Josie

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

  3. #3
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Userform Screen Updating - stop disappearing between each data input

    chris,

    assuming that the code to initialize the form is contained in the standard initialization routine, I think you could just do

    [CODE]
    Call UserForm_Initialize()
    [CODE]
    If you like my contribution click the star icon!

  4. #4
    Forum Contributor
    Join Date
    12-14-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    106

    Re: Userform Screen Updating - stop disappearing between each data input

    HI Ollie

    SOrry whilst I thought this had cracked the problem of disappearing reappearing userform I now have the issue that because I am not unloading the form it is retaining the existing data in the userform rather than resetting it to what it would really be when it initialises.

    Any thoughts?
    Please Login or Register  to view this content.

  5. #5
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Userform Screen Updating - stop disappearing between each data input

    The common solution is to create a routine called "UserForm_Initialize". This routine will automatically be called when you display the form. In this routine add instructions to initialise all fields with their default values.

  6. #6
    Forum Contributor
    Join Date
    12-14-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    106

    Re: Userform Screen Updating - stop disappearing between each data input

    Cheers Ollie

    I have a user_initalize section. Not sure how to set default values though.WIthin this initialize I have populated listboxes etc but thought if I defined a default value it would unpopulated my list source. Is this incorrect.

    Please find my code below. If you could amend with one default value for say Category Lst = Select Category that would be awesome then I can do the rest.

    Please Login or Register  to view this content.
    thanks again

  7. #7
    Forum Contributor
    Join Date
    12-14-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    106

    Re: Userform Screen Updating - stop disappearing between each data input

    Also Im guessing I have coded some of the above in the wrong place in a userform or badly so any tips would be gratefully received.

  8. #8
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Userform Screen Updating - stop disappearing between each data input

    Hi Chris,

    Without a copy of the workbook it is a bit hard to understand what you are trying to achieve.

    Anyway, the statement

    Please Login or Register  to view this content.
    assigns a named range as a source to the combo/list box CategoryLst and is a correct approach

    to clear the value from the previous record all you need to do is add the following line

    Please Login or Register  to view this content.

    As said at the beginning, restructuring your code would require a copy of the workbook to understand what you are trying to achieve.

  9. #9
    Forum Contributor
    Join Date
    12-14-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    106

    Re: Userform Screen Updating - stop disappearing between each data input

    Hi Ollie.

    SOrry should have attached my worksheet much earlier.

    Attached it to this post.

    Basically the aim is once I press the finish button that all listboxes reset to "Select..." but do not actually select the "Select.." Also the option button resets to plug and textbox for Qty goes blank.

    I want this to be achieved without the screen going away and reappearing. The vbnullstring works well at deselecting but not returning the rowsource back to the select option within the range.

    Cheers for your perseverance. Potting Sheet 2013-changev23.xlsm

  10. #10
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Userform Screen Updating - stop disappearing between each data input

    Chris,

    still working on it.

  11. #11
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Userform Screen Updating - stop disappearing between each data input

    Chris,

    I have made most of the changes needed in the attached version.

    There is however one more change that you will need to make yourself!

    You have used frames to group the data into a logical order. The downsize of grouping controls like that is however that cursor movement does not happen in a normal manner for the last field in a frame. As a result, the on change or after update event does not get fired and fields will not get populated properly.

    I suggest to remove the frames so that you can rely on the one change and after update events

    I hope the changes made by me are clear enough for you to continue with.

  12. #12
    Forum Contributor
    Join Date
    12-14-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    106

    Re: Userform Screen Updating - stop disappearing between each data input

    thank you so much for this. it is so much appreciated. Especially all the detail into what the code is doing etc. Will take your advice and delete the frames.

    quick question. which line of code selects the list box to the default "Select......" Cant see it in the code even though it clearly is there. Also what part of the code is realising that the "Select...." is not an acceptable in put and rasining the query upon pressing enter. Previously it only rasied this if empty but now seems to recognise these as bad entries too. THis is exactly what wanted - just interested in how it was achieved.

    many thanks again.

    chris

  13. #13
    Forum Contributor
    Join Date
    12-14-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    106

    Re: Userform Screen Updating - stop disappearing between each data input

    Only me again. See how u have done both things now. Excellent. Another new thing learned. THank you so much again. I would add more rep to you but it tells me I have to share love b4 I can. will return and add more later once someone else has stepped up to plate on any other issues I may have shortly.

  14. #14
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Userform Screen Updating - stop disappearing between each data input

    Hi Chris,

    Glad to hear it is working for you. You are welcome.

+ 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: 4
    Last Post: 11-28-2012, 06:09 PM
  2. Turning off screen updating deletes source data
    By jc0045 in forum Excel Programming / VBA / Macros
    Replies: 33
    Last Post: 03-23-2011, 12:42 AM
  3. Stop leading zero disappearing
    By philipb123 in forum Excel General
    Replies: 2
    Last Post: 08-11-2008, 08:29 PM
  4. Listbox data disappearing (from screen only)
    By TommySzalapski in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-03-2005, 09:58 AM
  5. [SOLVED] How do I stop an apostrophe from disappearing?
    By jpeck in forum Excel General
    Replies: 3
    Last Post: 02-02-2005, 10:06 PM

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