+ Reply to Thread
Results 1 to 12 of 12

UserForms

  1. 01-17-2011, 12:19 PM #1
    j_Southern
    j_Southern is offline
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    UserForms

    Dear members

    I have a userform with a listbox and several textboxes.The user selects an order no from the listbox and the textboxes are populated with details from that order. The user then manualy fills in some other textboxes and clicks a button whereupon all the details are fed onto the next vacant row of a worksheet. All the textboxes are then returned to a null and the userform becomes available to record the next order. Or at least thats what is meant to happen, and indeed did happen whilst I was working in the VBA environment.
    When I went live however the first record was dealt with properly but the text boxes did not populate, for subsequent entries. Here is the basic code I am using.
    Please Login or Register  to view this content.
    I have cut out a lot of the code detail so I hope it still makes sense.
    I suspect that the problem lies with the Sub I have marked "a" but I dont know how to get round it.
    Can some body please point me in the right direction
    John
    Reply With QuoteRegister To Reply
  2. 01-17-2011, 12:43 PM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,446

    Re: UserForms

    I'm sure there will be people who can address the problem by reviewing the extract of code you have provided but, for me, it would be a lot easier to test and investigate with a copy of the spreadsheet, forms and code ... with some sample data.

    Without it, I wouldn't want to hazard a guess.

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 01-17-2011, 01:07 PM #3
    j_Southern
    j_Southern is offline
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: UserForms

    Dear TMShucks
    I have attached the whole workbook. The password is magpie.
    The download button activates ODBC connections to SAGE LINE 100 but there is already data in the download sheet.
    If you press "enter orders" you see the userform I refer to. The first time of use it populates when you select an order from the listbox. After clicking "EnterRecord" the text boxes remain empty on selecting an order.
    I hope this makes things clearer.
    John
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 01-17-2011, 01:26 PM #4
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: UserForms

    Your workbook is protected
    Last edited by royUK; 01-17-2011 at 01:32 PM.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example
    Reply With QuoteRegister To Reply
  5. 01-17-2011, 02:27 PM #5
    jwright650
    jwright650 is offline
    Valued Forum Contributor jwright650's Avatar
    Join Date
    12-10-2010
    Location
    Va, USA
    MS-Off Ver
    Excel 2003, Excel 2010
    Posts
    606

    Re: UserForms

    So basically, you want to clear the form after you hit enter and place the cursor back in the first textbox so the form is ready for the next record to be entered?

    If so, you will need to add some code to the command button1's events to set all of the textboxes and comboboxes back to "" and set the focus back to the first textbox.

    EDIT: Unless I'm looking at this wrong, the code the OP posted is much different than the code in the Workbook attached
    Below is the code for the CommandButton1 on Userform1

    Please Login or Register  to view this content.

    EDIT#2.....nevermind me, the code to set all data back to "" is in Module1
    Last edited by jwright650; 01-17-2011 at 02:47 PM. Reason: added comment
    Life is like a roll of toilet paper. The closer it gets to the end, the faster it goes.
    John Wright
    Reply With QuoteRegister To Reply
  6. 01-17-2011, 02:56 PM #6
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: UserForms

    John can you open the workbook? I'm getting a message that it is password protected.
    Reply With QuoteRegister To Reply
  7. 01-17-2011, 03:07 PM #7
    jwright650
    jwright650 is offline
    Valued Forum Contributor jwright650's Avatar
    Join Date
    12-10-2010
    Location
    Va, USA
    MS-Off Ver
    Excel 2003, Excel 2010
    Posts
    606

    Re: UserForms

    Quote Originally Posted by royUK View Post
    John can you open the workbook? I'm getting a message that it is password protected.
    royUK, the password is "magpie"

    This one looks a bit complicated for me, I'm not sure that I can follow it all the way through.
    Reply With QuoteRegister To Reply
  8. 01-18-2011, 04:19 AM #8
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: UserForms

    I'm not sure what is supposed to happen, but using the Enter order button clears the manual entries.

    There is so much code there with no notes what you are doing. I would suggest you start working through the code, there's lots of work needed to improve it - annoying screen flickering because the code selects sheets, Auto_Open instead of WorkBook_Open, unnecessary coding that needs deleting etc
    Reply With QuoteRegister To Reply
  9. 01-18-2011, 04:59 AM #9
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,446

    Re: UserForms

    @RoyUK: agreed. Apart from that, the OP's problem is that after selecting and entering the first record, sleecting a second record does not populate the text boxes on the form.

    I tried to step through but have to admit I got lost in the maze.

    I also cheated a bit and commented out the hiding of the sheets and quitting the application as it was getting difficult to tweak the code without getting locked out.

    Regards
    Reply With QuoteRegister To Reply
  10. 01-18-2011, 05:19 AM #10
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: UserForms

    I can't understand why the EnterRecord macro sets the ListBox BoundColumn Property. Removing that code seems to allow the TextBoxes to populate.

    As I said the code needs some serious reviewing. much of it is over-complicated and could be more efficient, e,g,

    UserForm3

    Please Login or Register  to view this content.
    This eliminates using GoTo, screen flickering and is much faster
    Reply With QuoteRegister To Reply
  11. 01-18-2011, 10:06 AM #11
    j_Southern
    j_Southern is offline
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: UserForms

    royUk,TMShucks and others,
    Thank you for your interest in my problem. I feel a bit squashed by the mauling I have had, but will take on board the criticisms. If the problem persists I will start a new thread.
    john
    Reply With QuoteRegister To Reply
  12. 01-18-2011, 10:09 AM #12
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: UserForms

    Nobody was attempting to "maul" you. Basically, what I'm saying is tidy up what you have so far - see the difference in my code above; add some comments to the code so that we know what you are trying to do. Also, try removing the BoundColumn code as I suggested
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1