+ Reply to Thread
Results 1 to 13 of 13

Help needed to modify current userform to find and enter corrected data rows

  1. #1
    Forum Contributor
    Join Date
    09-14-2012
    Location
    Tucson, Arizona, USA
    MS-Off Ver
    Office 2010
    Posts
    105

    Help needed to modify current userform to find and enter corrected data rows

    I'm working with spreadsheet with 15 columns but only the first 2 need to be searchable (it can have up to 376 rows). What I want is to make a userform with drop downs for the first 2 columns (dates and times) providing searchable criteria and the last 13 (the actual data) to populate as editable text boxes upon locating the row in question. I've done some looking around and can't seem to find a baseline macro that works for me. I've included a file with just the sheet I'm working with at this time, but can provide the entire workbook if need be. Thanks for any help anybody can provide whatsoever.

    ThermalTest.xlsm
    Last edited by LoneWolf3574; 12-16-2012 at 07:41 PM. Reason: Modified Title to Better Suit Question

  2. #2
    Forum Contributor
    Join Date
    09-14-2012
    Location
    Tucson, Arizona, USA
    MS-Off Ver
    Office 2010
    Posts
    105

    Re: Help needed to create a userform to find and edit data

    Is there a way to use the userform I already have created to do this task?

  3. #3
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Help needed to create a userform to find and edit data

    Something like this:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    09-14-2012
    Location
    Tucson, Arizona, USA
    MS-Off Ver
    Office 2010
    Posts
    105

    Re: Help needed to create a userform to find and edit data

    That works great, but it creates a new data row when I click 'Add'. Is there some way to make it replace the data it finds with the "corrected" data instead of having it add a new row when I click add?

    The following coding only replaces whatever happens to be in the first row after I added another command button (cmdReplace).

    Please Login or Register  to view this content.
    Last edited by LoneWolf3574; 12-15-2012 at 08:00 AM.

  5. #5
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Help needed to create a userform to find and edit data

    Use the proper methods to populate comboboxes.

    Please Login or Register  to view this content.
    Do not split date & time in two columns.
    If date is in column B:

    Please Login or Register  to view this content.



  6. #6
    Forum Contributor
    Join Date
    09-14-2012
    Location
    Tucson, Arizona, USA
    MS-Off Ver
    Office 2010
    Posts
    105

    Re: Help needed to create a userform to find and edit data

    snb - I'm afraid I don't understand what you're saying. My issues are not with how the comboboxes are/operate or how the data is added to the sheet (they work, obtained from http://www.contextures.com) but with finding a specific row of data based on the date and time already entered in columns B and C, then replacing that data with the "corrected" data in columns D through P. zbor was good enough to provide a solution for the first part of my problem - finding the data, but replacing the found data row is now the issue.

    Your date time comboboxes are really nice, a method I definitely will consider integrating into my next project, but this sheet deals with the month the workbook is designated for. The second code you posted returns a "Compile error: Variable not defined" after correcting for a couple of typos (Array(txtChWtrSup.Value and "CommandButton").

    I've included the updated workbook below

    ThermalTest.xlsm
    Last edited by LoneWolf3574; 12-17-2012 at 04:56 PM. Reason: typos

  7. #7
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Help needed to modify current userform to find and enter corrected data rows

    removing 'option explicit' prevents that 'warning'

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    Why not populate the comboboxes from the data on the worksheet?

    That will make it easier to update data on the worksheet.

    PS Keep Option Explicit, it will help you find typos etc
    If posting code please use code tags, see here.

  9. #9
    Forum Contributor
    Join Date
    09-14-2012
    Location
    Tucson, Arizona, USA
    MS-Off Ver
    Office 2010
    Posts
    105

    Re: Help needed to modify current userform to find and enter corrected data rows

    snb - Now I get a "Run-time error '438': Object doesn't support this property or method". It highlights the .Offset line

    Norie - If I'm understanding what you're saying correctly, the coding provided by zbor does just that. With the search command, the comboboxes are used to search for dates and times, populating the textboxes. With the add command, the comboboxes are used to fill designated spreadsheet cells with the data date and time.

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Help needed to modify current userform to find and enter corrected data rows

    I mean to have one combobox with both date and time.

    Then you can simply use the combobox's ListIndex property to locate the relevant row.

  11. #11
    Forum Contributor
    Join Date
    09-14-2012
    Location
    Tucson, Arizona, USA
    MS-Off Ver
    Office 2010
    Posts
    105

    Re: Help needed to modify current userform to find and enter corrected data rows

    I apologize for my confusion here since I may not be on the same page as you and I'm still new to the intricacy's of macros (I've only learned what is relevant to what I'm doing so far). I don't mean to frustrate anybody with my question, perhaps if I approach my explanation and what I'm trying to achieve from a different angle.

    I have a userform that my coworkers and I use to enter data into a spreadsheet. The spreadsheet has been locked due to tinkering in the past by a couple coworkers that ultimately resulted in a large loss of data, taking an enormous amount of time to rebuild. Occasionally, one of us enters the data incorrectly and it needs to be corrected. I am able to locate the data row in question by using the combobox drop downs, but have encountered the stumbling block of replacing the found data with the new corrected data from the userform. It instead, depending on how I alter the coding, either enters the data in the first data row or the first empty data row and not the row the found data is located in. The workbook I originally included is taken from that workbook for the sake of focusing on the worksheet in question, but I can upload the entire actual workbook if it helps.
    Last edited by LoneWolf3574; 12-18-2012 at 04:53 AM. Reason: grammer

  12. #12
    Forum Contributor
    Join Date
    09-14-2012
    Location
    Tucson, Arizona, USA
    MS-Off Ver
    Office 2010
    Posts
    105

    Re: Help needed to modify current userform to find and enter corrected data rows

    I've been busy with personal matters, so I apologize for taking so long on getting back here.

    I've made the combobox work with regards to 2 columns showing but have run into issues in with how it shows within the combobox. I created a DateTimeList in the Name Manager and had it reference =OFFSET(Thermals!$B$11,0,0,COUNTA(Thermals!$B:$B)-1,2), however, it shows the date and time in alternating rows when you open the combobox in the userform. By adding the coding below, it gives the date as dd/mm/yy and shows the time as a number with decimal places (8.33333333 instead of hh:mm).

    Please Login or Register  to view this content.
    Taking a step back to see if I could figure out the search and replace issue, I am still running into the problem of the found data row being entered into a new row instead of replacing the found data with the edited. I tried to use the code below the coding that enters a new row with nothing happening at all except for clearing the form of all but the date and time.

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

  13. #13
    Forum Contributor
    Join Date
    09-14-2012
    Location
    Tucson, Arizona, USA
    MS-Off Ver
    Office 2010
    Posts
    105

    Re: Help needed to modify current userform to find and enter corrected data rows

    I got some help on the first code on another forum, but I still can't get the userform to populate after clicking the search command unless I use 2 comboboxes for the date and time.

    Please Login or Register  to view this content.
    http://www.mrexcel.com/forum/excel-q...-userform.html

+ 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