+ Reply to Thread
Results 1 to 8 of 8

Find and update table row data using Tag reference

  1. #1
    Registered User
    Join Date
    02-24-2019
    Location
    UK
    MS-Off Ver
    MS 365 PC & Mac (assume PC unless otherwise stated in post)
    Posts
    96

    Find and update table row data using Tag reference

    I came across this methodolgy in another forum post and really like the simplicity of it using the 'Tag' reference.

    My aim here is to find the matching row with the same Action # as specified in TextBoxActionNo, and then edit all of the fields in that row according to any changes the user may have made. The code below only works for the first TextBox, Column/Tag 3 in this case as I don't want to change the Action # (column 1) and column 2 has a formula in it.

    I think the problem is as soon as the code updates the first reference the rest of the form clears, and I am not sure why...?

    Any help is much appreciated

    Please Login or Register  to view this content.

  2. #2
    Forum Contributor
    Join Date
    01-02-2020
    Location
    Idaho, USA
    MS-Off Ver
    365
    Posts
    273

    Re: Find and update table row data using Tag reference

    Can you please upload a sample workbook with any personal data removed?

  3. #3
    Forum Contributor
    Join Date
    01-02-2020
    Location
    Idaho, USA
    MS-Off Ver
    365
    Posts
    273

    Re: Find and update table row data using Tag reference

    I see from your code that the controls that have a tag value must be text boxes and the tag value is the column you want to put the value of the text box in. So apparently you have text boxes with tags for each column you want to update.
    I like the way you can use tags like that... it's very convenient! I've done simular things many times. The only reasons I can think of that it only works on the first text box is either:
    1. The other text boxes don't have a tag
    2. They all have the same tag so it's updated the same column over and over
    3. You have a control that does not have a value or text property but still has a tag value (like a command button), which would error out but you have an On Error line in your code somewhere. But I can't troubleshoot that without a sample file or more info
    There's nothing in the code that should clear you form though, so I can't troubleshoot that either without a sample or more info

  4. #4
    Registered User
    Join Date
    02-24-2019
    Location
    UK
    MS-Off Ver
    MS 365 PC & Mac (assume PC unless otherwise stated in post)
    Posts
    96

    Re: Find and update table row data using Tag reference

    Thanks for the pointers, maybe the issue has something to do with me using ListBoxes?

    I attach my work in progress file, I warn you it is not pretty...(yet)...

    To recreate the issue, you need to open form, then select an action from main list box in middle of screen, make some changes to it in the boxes in the bottom of the screen then click update. As you will see it only seems possible to update project phase (the first assigned TAG), the others do not update.
    Attached Files Attached Files
    Last edited by chris01395; 11-27-2020 at 07:28 PM. Reason: more detail

  5. #5
    Forum Contributor
    Join Date
    01-02-2020
    Location
    Idaho, USA
    MS-Off Ver
    365
    Posts
    273

    Re: Find and update table row data using Tag reference

    chris01395,

    So here's what I found. When you click the update button it runs fine for 1 loop, but then the Listbox1_Click event runs which sets all the list boxes at the bottom back to what is on the sheet. I'm not quite sure why but it does. So after the first loop, the listbox values go back to what is already on the sheet, so the values on the sheet are set to the list box values after they have been changed back to the sheet's values. If that makes sense lol
    Anyway, I tried setting the EnableEvents property of the application to false so that wouldn't happen but that didn't work.. not sure why there either.. So here's what I did:
    1. In the module I created a public variable.. (Public StopCode as boolean) I don't know if you need the code in that module, so if you don't need that code you don't have to have it, but you can't get rid of that variable.
    2. I changed the Update button code to this:

    Please Login or Register  to view this content.
    The error handler is just in case something were to happen your listbox1 click event will still work.

    Then at the top of the Listbox_Click event I added this line:
    If StopCode = True then Exit sub

    That stops the ListBox1_Click event from running until StopCode is set back to False, which happens at the end of the Update button code.
    It works good. I uploaded the file with the fixes I mentioned.
    I hope it solves your problem. If it does, I hope you will add to my reputation because I just started helping out here.. lol
    If you have questions concerning this same problem, please let me know!
    Attached Files Attached Files
    Last edited by achammar; 11-27-2020 at 09:23 PM.

  6. #6
    Forum Contributor
    Join Date
    01-02-2020
    Location
    Idaho, USA
    MS-Off Ver
    365
    Posts
    273

    Re: Find and update table row data using Tag reference

    Oh, I forgot to mention that I added a sub to your module that opens the form. (CTRL + q runs that macro while your sheet is displayed and the form will open)
    Also, just in case it's something that you are interested in... you could put the code that is in your close button in the Query Close event, then it wouldn't matter how they closed the form.. but that's another subject.. lol

  7. #7
    Registered User
    Join Date
    02-24-2019
    Location
    UK
    MS-Off Ver
    MS 365 PC & Mac (assume PC unless otherwise stated in post)
    Posts
    96

    Re: Find and update table row data using Tag reference

    Thank you achammar your input is so much appreciated and pleased to see you were able to solve my original problem.

    So is the stopcode essentially preventing any other code running while the update code runs?

    Happy to 'add reputation' for you, I never actually knew that was a thing, so thanks for bringing that to my attention also.

  8. #8
    Forum Contributor
    Join Date
    01-02-2020
    Location
    Idaho, USA
    MS-Off Ver
    365
    Posts
    273
    Quote Originally Posted by chris01395 View Post
    Thank you achammar your input is so much appreciated and pleased to see you were able to solve my original problem.

    So is the stopcode essentially preventing any other code running while the update code runs?

    Happy to 'add reputation' for you, I never actually knew that was a thing, so thanks for bringing that to my attention also.
    The stopcode variable just prevents the list box1 click event from running until the update code is finished. Im glad it fixed your problem!

+ 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. Find Database & Update the table in Bulk Method
    By benwanheda in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-07-2017, 05:42 AM
  2. [SOLVED] Find and update table cell
    By omagoodness in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-26-2016, 05:29 PM
  3. Replies: 0
    Last Post: 08-21-2015, 03:07 PM
  4. [SOLVED] VLookup Question - If i update the lookup table i do not want to update old data
    By mrabattoir in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-28-2014, 03:40 AM
  5. Replies: 1
    Last Post: 11-28-2013, 02:10 AM
  6. Update table by name, not by cell reference
    By jwhitwell in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-17-2010, 08:49 PM
  7. Replies: 0
    Last Post: 06-01-2006, 10:50 AM

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