Results 1 to 6 of 6

Thread: Modify/Delete Data Row

  1. #1

    Modify/Delete Data Row



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a user form that fills in a row of a spreadsheet. Each new user form entry is added to the next empty row. In the first column of each row I have a "line item number "that populates with each new data entry. Is there a method of making that a hyper link that would open a user form that would open, pre-populated, with that rows data? I would then have buttons on that userror form: Edit: which would allow me to over write the data of this particular row with whatever information I changed in this form. Delete: which would delete this particular row from the spreadsheet and move all the rows below it up, and a Cancel button that would just close the form. I know very little about vba....please be gentle

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    You could use a double click on the cell.
    Use the Worksheet_BeforeDoubleClick event to show the form
    and UserForm_Initialize to load the data from the activecell row.

  3. #3
    That sounds like a good idea.

    How do I create a double click event? Where do I enter things?

    When I use a UserForm_Initialize...how will the form know which row to load?

    What would I put on the Edit button so the original data in that row gets overwritten with the new data?

    Do you know what I would add to the Delete button to have that specific row delete from the spreadsheet? Will the rows below it move up?

  4. #4
    I don't see how to edit posts....sorry.

    I figured out how to create the double click event and I can get the Userform I want to show up to open.

    How do I get it to populate with the double-clicked row data? And then the rest of the things I want to accomplish?

    Thank you.

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Your user form currently writes values, I'm guessing from text boxes, to columns of the worksheet by making the cells.value equal to the textbox.value.
    In the initialization of the user form do things the opposite way round and make textbox1.value = cells(activecell.row, whatever column).value
    etc., etc.

    This will load the form with data from that row and you can do whatever you want with it then write it back to that activecells row. rather than the next available row which would only be for new records.

  6. #6
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Without the double click, on a row to edit, how do you bring up the user form to add a new record ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •