+ Reply to Thread
Results 1 to 7 of 7

Populate Userform with values from table

  1. #1
    Forum Contributor
    Join Date
    11-08-2017
    Location
    Murfreesboro, TN
    MS-Off Ver
    MS 365
    Posts
    168

    Populate Userform with values from table

    Hi. I have a sample spreadsheet with userforms that I need help with.

    Userforms:
    ContactSearch
    ContactEdit

    ContactSearch:
    There is a button called "Open Contact" that currently navigates to the record in the table using the following code:

    Please Login or Register  to view this content.
    I want it to open the "ContactEdit" form and populate the fields with the items from the table. How do I change the code to make that happen?

    Then, I want to re-save the changes to the same line in the table. I copied the "ContactAdd" form, so the "CmdSaveChanges_Click" code puts the changed information on the next open line instead of on the same line it came from. How do I change the code to do what I want? (I didn't input all of it here since it is so many lines. All of the lines are basically the same, so here is a snipet)

    Please Login or Register  to view this content.
    Attached Files Attached Files

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

    Re: Populate Userform with values from table

    Hi

    I have done the following for you:

    Created a Global variable "Entry"

    Added an additional Column in your Named Range ContactSearchResults this Column Store the Row Number of the Entries.

    Added an additional Column to your listbox on your search screen. Column Width is set to 0 so it is hidden

    Selecting an Entry in the List box sets the value of Entry to the Row Number of the Entry. Entry = Listbox.value

    I have added the code to your Edit Userform to load the data from the selected Entry.

    I have also added the code to save your edited Userform.

    This code is messy but is in line with your existing code:

    My normal Code to load the Userform requires each Control be numbered to match the column where its data is.
    Textbox10's data is in column 10, Combobox11's data is in column 11 etc.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mehmetcik; 03-11-2020 at 08:19 AM. Reason: Save Edited Userform corrected.
    My General Rules if you want my help. Not aimed at any person in particular:

    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.

  3. #3
    Forum Contributor
    Join Date
    11-08-2017
    Location
    Murfreesboro, TN
    MS-Off Ver
    MS 365
    Posts
    168

    Re: Populate Userform with values from table

    Thank you mehmetcik. This is very helpful.

    All of that works, but when I click the "Save Changes" button on the ContactEdit userform, it still adds a new row to the table instead of overwriting the existing row.

    How do I change this code to make it overwrite? I know it has to do with the LastRow portion, I just don't know what to change that to. (I am only posting the first part of the code since the bottom part is just more of the same.)

    Please Login or Register  to view this content.
    Also, I have this code to clear the form, but it does not clear everything. I found this on another site. How can I change this to completely clear the form?

    Please Login or Register  to view this content.
    I really appreciate your help.

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

    Re: Populate Userform with values from table

    Sorry.

    I updated that afterwards. Upload a new sample from Post Number 2.

    Try this:
    Please Login or Register  to view this content.
    Alternatively:

    Create a Change Flag. Set Your Emtry to an empty row and load a blank entry.

    Global CF as Boolean

    Please Login or Register  to view this content.


    '*************************************************************************************************
    Last edited by mehmetcik; 03-11-2020 at 01:38 PM.

  5. #5
    Forum Contributor
    Join Date
    11-08-2017
    Location
    Murfreesboro, TN
    MS-Off Ver
    MS 365
    Posts
    168

    Re: Populate Userform with values from table

    Thank you! This works great. I appreciate your help.

  6. #6
    Forum Contributor
    Join Date
    11-08-2017
    Location
    Murfreesboro, TN
    MS-Off Ver
    MS 365
    Posts
    168

    Re: Populate Userform with values from table

    Sorry to open this back up, but I am having trouble with the changes in my actual spreadsheet. The changes in Sample2.xlsm above work great in the sample, but it crashes Excel in my actual spreadsheet every time I click to add a contact to the table, from both the ContactAdd and the ContactEdit userforms.

    I suspect it has something to do with the Global Variable "Entry", but not sure. Is there another way to accomplish the copying without that variable?

  7. #7
    Forum Contributor
    Join Date
    11-08-2017
    Location
    Murfreesboro, TN
    MS-Off Ver
    MS 365
    Posts
    168

    Re: Populate Userform with values from table

    Nevermind! I figured it out. User error. Shocking, I know.

+ 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. [SOLVED] Populate UserForm List with values from an Array
    By plans in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-22-2019, 10:15 AM
  2. Populate new row in table from a form (not userform) on another worksheet
    By joeystraw in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 12-01-2018, 10:59 AM
  3. [SOLVED] populate userform with data from pivot table
    By Mathijs3 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-09-2016, 10:30 AM
  4. Populate userform from row values based on combobox
    By robertogavilan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-20-2014, 06:32 PM
  5. VLookup Excel table to populate combobox in userform
    By trecie13 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-14-2013, 02:17 PM
  6. Replies: 1
    Last Post: 11-12-2011, 05:38 PM
  7. Populate Excel table with userform data
    By waspandbee in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-21-2010, 09:34 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