+ Reply to Thread
Results 1 to 7 of 7

User form needs to update entry rather than add a new one

  1. #1
    Registered User
    Join Date
    07-24-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    73

    User form needs to update entry rather than add a new one

    I have a user form transferring data to a new line in a spreadsheet. It's something like as follows:


    Please Login or Register  to view this content.
    I have added a function to update existing data entries. when a user clicks on the active line, the data from the active line is put back into the user form, so that the user can change and update the data:


    Please Login or Register  to view this content.
    However, at the moment instead of updating the same record, my code is adding a whole new entry.

    could anybody suggest a modification which makes it so that this function replaces the existing entry?


    Linked: http://www.mrexcel.com/forum/excel-q...ml#post4176860

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

    Re: User form needs to update entry rather than add a new one

    You need a Public flag to monitor when you read an existing entry and a public variable to store the row number

    Then you can use your existing code with a small modification.
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  3. #3
    Registered User
    Join Date
    07-24-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    73

    Re: User form needs to update entry rather than add a new one

    Thanks mehmetcik :

    I'm worried that the code I've posted makes it looks like I understand more than I do - could you please illustrate this with an example?
    As brief as you'd like.

    Many thanks for your response.

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

    Re: User form needs to update entry rather than add a new one

    In Normal Module

    Please Login or Register  to view this content.

    In Macro Module
    Please Login or Register  to view this content.

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

    Re: User form needs to update entry rather than add a new one

    You are making a lot of basic errors in your Code.

    You should read all your data from excel to visual basic in one read action

    Please Login or Register  to view this content.
    Then you transfer your data into your textboxes using something like

    Please Login or Register  to view this content.
    But your textboxes need to have consistent names


    You can load List and comboboxes the same way

    Please Login or Register  to view this content.


    You write your data back to excel in the reverse manner.
    Last edited by mehmetcik; 06-05-2015 at 06:53 PM.

  6. #6
    Registered User
    Join Date
    07-24-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    73

    Re: User form needs to update entry rather than add a new one

    Thanks for your replies mehmetcik - I will review my code so as to make it more efficient and less error strewn!
    Wiith your help I have been able to implement the 'entry update' which now works!

    One issue!

    Currently the database does not seem very good at recognising the data already there. When there was data in each line it worked well and a new line appeaered beaneath the previous.
    However I then deleted all data to start from scratch but this doesn't appear to have reset the lines of entry which are still being added in on the 10th row, even though the 9 rows above are now empty.

    Is this rectifiable?

    I appreciate your help
    Last edited by henryBukowski; 06-09-2015 at 05:37 AM.

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

    Re: User form needs to update entry rather than add a new one

    The last row is not reset by excel until it is saved and re-opened.

    Easiest way to sole this is to ammend this line:

    Please Login or Register  to view this content.
    Try this instead

    Please Login or Register  to view this content.

+ 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: 08-28-2013, 05:04 PM
  2. user form entry
    By niiflo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-22-2013, 05:22 PM
  3. Match new password to specific user and update list via custom user form
    By Sargekd in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-03-2011, 09:40 AM
  4. Filling form fields via worksheet dropdowns, user update via form, change form color
    By Demosthenes&Locke in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2010, 08:58 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