+ Reply to Thread
Results 1 to 9 of 9

User form that can search and amend

  1. #1
    Registered User
    Join Date
    10-06-2010
    Location
    Dunstable,England
    MS-Off Ver
    Excel 2010
    Posts
    95

    User form that can search and amend

    Hi Guys

    I have created a form that writes data to a worksheet. I want to add the function of being able to amend the data that was added.

    Ideally the user will click the View/Amend button select a week and the form will be populated with that weeks data.

    Does anyone have an example of a form that has this function? or can you give some guidance of how I would get started?

    My form is attached. I have removed all of the irelevant tabs and the only tab relevant in the form is the Adjustments tab.

    Many thanks

    Nods
    Attached Files Attached Files
    Last edited by nods; 11-11-2010 at 12:38 AM.

  2. #2
    Forum Contributor
    Join Date
    05-09-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    315

    Re: User form that can search and amend

    Hi

    I've added the following code to the View/Amend button. This will load the values from the worksheet to the form. The process for saving updated values will be the reverse.

    Hope it helps.

    Dion


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

  3. #3
    Registered User
    Join Date
    10-06-2010
    Location
    Dunstable,England
    MS-Off Ver
    Excel 2010
    Posts
    95

    Re: User form that can search and amend

    many thanks your code works great with just one exception.

    If you select a site and week - click View/Amend it populates the form perfect, however if you then change any of the values it rewrites the data in all of the fields instead overwriting it. This causes duplications in the worksheet.

    Is there a way to get it to overwrite the data to prevent duplications or get it delete the data and then write the data again?

    Hope that makes sense.

    again many thanks for your help.

  4. #4
    Forum Contributor
    Join Date
    05-09-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    315

    Re: User form that can search and amend

    Hi

    I'm not sure what you mean because my code didn't save any values to the worksheet, it simply loaded it into the form. Are you referring to your code behind the Save button?

    I've updated the Save button code so it overwrites instead of creating new entries (as I said, it's exactly the opposite of the code to load the data).

    Dion
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-06-2010
    Location
    Dunstable,England
    MS-Off Ver
    Excel 2010
    Posts
    95

    Re: User form that can search and amend

    Many thanks Dion. Your code works perfect. The only thing I would want to change is to stop the text boxes from clearing when you select the week. So that the user has the abilty to copy a previous weeks data into a current week.

    If i'm honest this code is new to me and I would like use the principles in the rest of the forms in the project. Would it be possible to add some comments to the code so that I can get to grips with it?

    Many thanks again. I wasn't expecting you write the code for me but you have been a massive help.

    Nods

  6. #6
    Registered User
    Join Date
    10-06-2010
    Location
    Dunstable,England
    MS-Off Ver
    Excel 2010
    Posts
    95

    Re: User form that can search and amend

    Hi. I have spotted another problem. When I select a new week and try and add new data it doesn't write the data to the work book.

    Do I need to use the code you gave me in addition to the code I had already written for the save button?

    I am guessing that your code just saves over the historical data that is already there?

    What I am trying to acheive is:

    The user can select a site and week. click view/amend and the form will be populated with that weeks data. The user can then amend the data and click on save and the data will overwrite the old data (this works fine).

    the user also has to be able to add new data by slecting the site and week, filing in the form and clicking save or by selecting a week and site that has already been inputed, clicking view/amend to populate the form then changing the week drop down to the new week and clicking save.(this is so the user doesnt have to enter the data every week but can chose a historical week change the data if neccesary and copy it to the current week)

    Hope that makes sense. Any help would be apreciated. Also if you could put some comment on the code so that I can get to grips with it that would be great. I intend to use the principles for a more complex form and would like to understand how it works.

    Many thanks

    Nods



    code for the amend button

    Please Login or Register  to view this content.
    Code for the save button

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    10-06-2010
    Location
    Dunstable,England
    MS-Off Ver
    Excel 2010
    Posts
    95

    Re: User form that can search and amend

    I solved the problem by having a button that writes the new text to the workbook and a button that overwrites the text which you kindly provided the code for.

    .Apologies but I miss understood you earlier post.

    Some guidance or comments on how your code (in the post above) works would be really useful so that I can apply the principles to a more complex form.

    Many thanks once again

    Nods

  8. #8
    Registered User
    Join Date
    10-06-2010
    Location
    Dunstable,England
    MS-Off Ver
    Excel 2010
    Posts
    95

    Re: User form that can search and amend

    Sorry can somone explain how the following bit of code works please?

    Please Login or Register  to view this content.
    I have the code working for my form accept this line. I can get my form to display the N/A if no data is present?


    Code for my new form looks like

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    10-06-2010
    Location
    Dunstable,England
    MS-Off Ver
    Excel 2010
    Posts
    95

    Re: User form that can search and amend

    Hi I had the below code working fine in Excel 2010 but when opened the same file in 2007

    I got the varible not defined error on this bit of code

    Please Login or Register  to view this content.

    Would anyone know why this is? Many thanks?





    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)

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