Updating record from Userform

  • Hi, I'm pretty new to forms and I've been struggling with trying to get a user form to update the active record it's displaying. The form includes a listbox displaying the list of records within the sheet and the form fields display the data relating to the selected record in the listbox.

    Unfortunately I can't workout how to use an update button to update the cells regardless whether changes were made or not to the content because it's a dynamic list in the form. I've attached my sample workbook.

    Any help on this will be massively appreciated.

  • Re: UserForm - Updating active record from user form.

    Seeing as you already have code to update a particular row...

  • Re: UserForm - Updating active record from user form.

    Thanks for this quick response, that worked a treat with a small tweak.

  • Re: UserForm - Updating active record from user form.


    ...responded without testing it properly

    Sorry, but there's nothing wrong with the code I posted.

    If it 'jumps' then either an error has been raised or else you are using controls bound to a range - either would have been an existing issue with the code which I didn't check as it wasn't an issue flagged by you in your original post.

    Unfortunately, am unable to check now as I'm using a phone.

  • Re: UserForm - Updating active record from user form.

    Well this is what I have however after it goes through the first .Range... it jumps to refreshing the form which to me seems to be related to the lstMyData (second piece of code).

    The code it bounces to to reload the form:

    The final result overwrites the changes made in the form with data in the worksheet.

  • Re: UserForm - Updating active record from user form.

    You're beginning to see the payback for the convenience of Bound Controls. You update a cell which triggers a refresh of the listbox and your code cascades from there...

    My opinion only, but using bound controls when the data is updated is more trouble than it is worth. I would seriously suggest you try another approach. It takes a little setting up, but once done, it makes things a lot easier.

    If you're willing to try...

    • Remove the setting for RowSource for myLstData
    • Add a new worksheet and name it, for example, 'Map' (This can be hidden/very hidden when the workbook is released to users)
    • Add formulas to Row1 referring to the column headings on sheet ES (Or else just Copy/Paste - but formulas are easier if something changes later)
    • In Row 2 add the control names under the headings they relate to (Item - Control1, Service Offering - Control6 etc...)
    • Create a named range on Row 2, Name it for the user form (Currently UserForm1). It should extend as far as column headings exist.

    Code changes

    The UserForm_Initialize event is used to load data to the listbox. It also stores the row number in Column 1 (Hidden).

    MyLstData click event.

    This simply loops though all the controls on the userform, searches for the control name in the 'UserForm1' named range. If found, it uses the column number where the name was found and the row number stored on Column 1 of the list box to load data from that cell on ES.

    And Finally, the Update code - exactly the same as the Click event, but it writes to the worksheet

    2 additional comments:
    The maximum number of columns in an unbound list control is 10. That is usually more than adequate.
    You should consider changing your naming convention for the controls to reflect the data stored in them. For example, Item - txtItem rather than Control1. Makes it a lot easier to see what's related to what.

    Only suggesting such a radical change as using the bound control will cause more and more issues as the code evolves...

    Attached workbook incorporates those changes - only a couple of controls defined on Map sheet, just to test.

  • Re: UserForm - Updating active record from user form.

    This is amazing! Thanks for your expert help on this. It makes total sense to do it this way rather than the over complicated and limited bound controls. I've renamed the controls as you mentioned, which does make it easier to identify what they relate to. I can manage with the limitation of 10 columns.

    Thanks again for all your help!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!