+ Reply to Thread
Results 1 to 168 of 168

Keeping information and auto population onto a new line each time

  1. #1
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Keeping information and auto population onto a new line each time

    I have attached a spreadsheet. Inside it describes what I need on the repair form. I need the information that is highlighted in yellow to auto populate on a new line on the first tab. I cant figure it out because it just replaces the same line each time. Please help.

    Repair Form1.xlsx

  2. #2
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    I think there is a bit more thinking required first:

    1. Is it your intention to enter whole of the repair request form at one sitting AFTER the repair OR will the top section be completed first and then the bottom section later?
    2. You have asked for 2 comment boxes to be included in the same cell - should these be on separate lines?
    3. Is there only ever one part used in the event of a repair? - there is only one box - I would have expected user to be able to enter as many as required


    In order that we fully understand your requirements please complete a Repair Request Form (filling in ALL the boxes) and manually enter the same details into the List of Machine Issues EXACTLY as you want to see them and re-attach your worksheet

    thanks

  3. #3
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Exclamation Re: Keeping information and auto population onto a new line each time

    It will all be completed at one time. If possible I would like the top comment box to show up first and then the second. I was thinking the same thing about the parts being used so I have added more lines and I have also added a spot for the button at the top. Please see the attached. I tried to play with the items but couldn't figure it out.Repair Form1.xlsx

  4. #4
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    I am guessing that the same machines will be repaired several times and you would like the data to be saved in such a way, that you can select all the records for a given machine, and see in time sequence, its maintenance history.
    Is there anything else you are expecting from this?

    You cannot do what you are trying to do without using VBA (ie creating and running a macro)
    What you are asking is not complicated, and I will post something for you to look at later today.

  5. #5
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Yes, The machines will be repaired several different times and several different machines. This is where I was running into issues. I couldn't get the information to stay on the first tab after submitted because it would override the information provided. I would like to be able to use the first tab after it is all said and done to isolate certain machines so I can look at the repairs over a period of time.

  6. #6
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    just keeping you informed (please feel free to disagree loudly!)


    More than one of the same part no
    - you have not included a way to include for " multiples" of the same part
    - I am now using column F (on sheet "Repair Request Form") for this

    Individual Part Numbers
    Rather than having multiple lines for parts - we will use the top line only (= ROW 34 on sheet "Repair Request Form" ), and then have a button to add a 2nd, 3rd.. etc (otherwise what happens if there are say 10 parts required?)

  7. #7
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    That sounds good to me. Sounds even better

  8. #8
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Ran into a little hitch, trying to clear out your prior values which was caused by various merged cells in your input area. VBA can be rather reluctant to amend merged cells, so instead of using your worksheet, it is easier to create a new UserForm to capture the data (which I will tackle and test tomorrow am)
    It will also handle multiple parts.

  9. #9
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I didnt even know that merged cells can cause that. Good to know. I will try and avoid that moving forward. Thank you for all your help.

  10. #10
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    What if we created the the macro in a cell then ran it then merged the other cells? Wouldn't that work? For example, undo the merge of the cells, in the first cell of that box, create the macro, run it, and then merge back?

  11. #11
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Here is a first attempt at an input form. See what you think

    The input is not being transferred to any cells yet - we are trying to get the user form to look right first (otherwise we may be creating unnecessary work!)
    If anything needs moving around or adding tell me - that is easy to alter.
    I am assuming that machine hours are "repair hours", and so have put with the input for parts. Parts will be input one part at a time and then you will click on box to input the next one (the field will clear when activated)

    Validation and drop down options are built in to some fields (you may not want it - easy to remove) - but it generally helps quality of input to sense-check certain fields and provide options for others.

    The sheet "validation" has some lists that I have invented for illustration. Validated fields include:
    - Clinic Name (presume you have a full list somewhere)
    - Date fields (assumes only last few weeks will be relevant)
    - Machine SN (if they are your machines, then presumably there is a complete list available)
    - Check that machine "minutes" < 60
    - Part No
    - Quantity (check that a number is being entered)

    When you open the workbook, enable macros and click where indicated to Open the Repair Form.
    Put some data into the various boxes and then click onto the next field to see what happens - some fields like the date will change format etc as you exit the box.

    I have left off a couple of name fields etc (because they were not being transferred with rest of data) - do you want those including? Easy to add them in.
    When we have finalised the Repair Form, we'll add the functionality to make it transfer everything to a Table, and then you can test it in anger.

    Once you are happy, I will give you a few pointers to help you tailor it later if your needs change, or if you have forgotten something.

    Kevin
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I love the concept! It is way better than what I had originally thought. some changes:

    Instead of having a drop down box for dates, can we change that to entering a manual date. Also the part numbers, the same thing, entering the part numbers manually and there needs to be a price for the part. The machine hours just need to be hours without min. Thoughts?

  13. #13
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    They would also have to print off the repair request form so they can have a paper copy.

  14. #14
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    That should be no problem, but glad you mentioned it now.
    When I amend the form for your next batch of comments I will add a "Print" button - but what we need to do is to make sure that the size is correct so that it fits neatly on your standard paper.

    Over here we use A4, you probably use something else.
    When you reply with the other changes required, please include your standard paper size (include measurements and your normal margin allowance, just to be sure) and I will adjust the form to fit, but I will not be able to test it. It may need a couple of attempts to get it right.

  15. #15
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    we use 8 inch X 11 Inch paper with 1 inch margins all around. I will test on my side and print as many as we need.

  16. #16
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Apologies - I seem to have missed spotting your earlier posting #12
    Here are my questions in reply to that.
    I will amend things to exactly as you want AND I am sure you have your reasons BUT I am so surprised by a couple of your comments that I have to ask a couple of questions.

    Instead of having a drop down box for dates, can we change that to entering a manual date - will do.
    - but why waste time (and risk inaccuracy) keying in dates? I have set it up so that the range of dates always starts with today's date and that will change automatically each day.

    Also the part numbers - will do
    - but do you not have a file with all this in?- inputting part numbers is very error prone - surely it would be easier to pick from a list of "verified" part numbers

    there needs to be a price for the part. Will add
    - but again do you not already have all this data tagged against a part number

    The machine hours just need to be hours without min ok - minute box will be removed

    What about the other details such as the name fields - do they need including? (Are these just signature boxes?)

  17. #17
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    If the current dates automatically populate then we can keep it as long as they are there when the spread sheet opens. As far as parts and pricing, we do not have that information for all the locations. We will over time which is one of the reasons for this form. As information comes in for each center then I will be able to save that data and add later to a drop down. Maybe create an add new part? The name field is required along with a signature.

  18. #18
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Look at the formula on sheet "Validation" in cell "D2" (plus D3 etc) - that is what drives the date dropdown options - hence the top one will always be the current date.

    It should be possible to have the stock dropdown AND also let you input anything you want - (a bit like "having your cake and eating it")

    After we finalise the Repair Form and fully tested data transfer to a table, I will add a little button to enable you to add a stock item.

    Final questions (all linked) before I join your input to a Table.
    These refers to 2 cells on sheet "Repair Request Form"
    Cell "E6" - is this a unique in house asset number? [or is it a machine type - can there be more than one machine with the same number?]
    Cell "H6" - is this unique to each machine
    Do you have more than one type of machine? [Just wondering whether it would be useful to bulid up a table containing Machine No/Machine Serial Number/Machine Type - so that you can analyse everything by type of machine - it would be easier to build this in now (even if you do not use it)]

    thanks
    Kevin

  19. #19
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    E6 is just the machine number given to identify the machine which is subject to change depending on location. H6 is unique to each machine. As far as different machines we do have different machines but they are in all different locations.

  20. #20
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    I have combined Clinic Name and Number (tell me if you need them separating out - easy to change back, or can split in the table later perhaps)
    - what do the numbers look like 2 digits? 3 digits?

    I have modified everything as per your requests (I think)

    I have added a print button - I have test printed it here - it looks like there is loads of room. So that we can add several additional lines for parts (what is the usual maximum required 5? 10?).
    It looks like we can almost double the length of the form. Is it similar on your printer?
    Rather than allocate all the space to additional part lines, would you prefer the Problem description boxes to be increased?

    I have amended fields so that you have the option of free entry in most of them. Try them all out and tell me if you like the way they are formatted (eg quantity, prices, dates etc)

    You should be able to enter something in every field to see what it looks like, and then print it.

    Next I will hook it into a table and make any final amendments (based on your reply to this post) and the next version you get will be the one that updates the data table and then we can focus on getting that perfect.

    As a general note
    - if there is anything in the wrong place on the form, it is very easy to move it somewhere else (subject to space limitations) - and you can do that yourselves later
    - what you will find harder is if there is a field missing, that you want to add later - so try think of everything you could need
    Attached Files Attached Files
    Last edited by Kevin#; 01-14-2016 at 02:40 PM.

  21. #21
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    The clinic numbers are 4-5 digest long.

    The max amount of parts are about 10.

    As far as printing, I go to print but the repair form stays in front of the print preview. when I moved it the only thing that was going to print is the button on the first tab and not the actual repair ticket.

    The look of things is nicely made. I like the format and it looks user friendly. Everything looks to be in the right place.

  22. #22
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I just confirmed. It is only printing the "click for repair form" button.

  23. #23
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    How strange!
    Are you clicking on "Print a copy"?
    I have tried it on 2 different versions of Excel and on different versions of Windows (- it does not always happen but every one of those 4 tests worked first time!)
    It should not be too difficult to get to the bottom of this - you are printing the worksheet rather than the form.
    Which version of Windows are you on?
    Which version of Excel?

  24. #24
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I am clincking on print a copy. I am using excel 2010 and using windows vista.

  25. #25
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    WOW that takes me back....
    My windows Vista PC died a few months ago and (to my shame) I have abandoned it, gathering dust. If all else fails, I will give it the kiss of life.. but that is a last resort.

    For testing purposes at your end, do you have access to another PC operating on say Windows 7 which also has Excel loaded. Excel 2010 is fine (but in combination with Vista, who knows!).

    Otherwise I will find a different way to allow you to print.

  26. #26
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I have one that runs windows 7 and I am having the same issue. I will continue to use windows 7 moving forward. This is what I get.

    Doc2.docx
    Last edited by ferriskenny; 01-14-2016 at 06:16 PM.

  27. #27
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    My combo is also Windows7 and 2010 and it works fine - so your issue is probably down to some setting or other. Which is reassuring (it will get sorted) albeit frustrating (it may take some effort!)

  28. #28
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Do you think it could be showing up just in the print preview screen? I tried with my Mac and the button didnt work because it is newer excel.

  29. #29
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    I'll think of a short-term workaround for the printing (we will fix it properly later!)

    I am testing various things (ie trying to break it) at the moment and putting in a few controls to avert dodgy input.
    Plan to get it to you in the next 2 hours (lots of interruptions today)

    I thought you were in USA (per your profile) - you must be awake early if you are

  30. #30
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I am in the USA. I work long hours.

  31. #31
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Attached is UserForm for you to test
    I have deliberately not added input for parts yet (need to discuss a couple of questions with you before I go ahead)
    After you have filled in the other fields click on the update box to put the value in sheet "MachineIssues"
    I am capturing everything in that sheet first, can then do what you like with it elsewhere, once it is captured.
    I have not put all the failsafe checks in yet (otherwise makes testing rather challenging)
    There are various checks to force you to put in entries in key fields
    Important - at this time please always click on one of the valid data choices - if you input rubbish the VBA will crash.
    I have tested input and you can put in free-format text into most of the fields, with the option of the dropdowns when ready.
    Feel free to clear my data from sheet "MachineIssues" - but do not delete row1
    I suggest you test the dropdowns with some real data, if you can. Leave the top headings unchanged but add to/overwrite my entries in sheet Validation.
    So give it a real test with typical data. See if the boxes are big enough etc
    And then check sheet "MachineIssues" - is it capturing what you want (I accept you may want columns in different seqence, but that will be a different sheet, after all input is agreed)
    Let me know of any problems - now is the time to knock them on the head, one by one, no matter how trivial
    Attached Files Attached Files

  32. #32
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    So far everything works minus putting in the parts, part number, and cost. that did not transfer over. See the attached.Repair Form test.xlsm

  33. #33
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    That is as I expected.
    Also try leaving certain fields blank (comments, machine no, machine s/no fields etc) and clicking on Update
    Try keying text instead of numbers into the machine hours field

    Can you input at least 20 -30 dummy Repair Forms with typical entries (based on the past) and see if there is anything that jumps out at you that is missing or needs amending
    Also amend the Validation Sheet - put in some real employee names who will be amending the sheets, real machine serial numbers, real parts & part numbers
    This will help verify if the input boxes are the right size.

    After that, attach the file again with its 20 - 30 entries in "MachineIssues" and an amended sheet "Validation"
    (you can leave all the earlier stuff there or delete it)

    I will look then look at all the "typical data" and see if I can anticipate any future problems.
    thanks

  34. #34
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I had completed all the information three times and then excel keeps failing and restarting. When I try and save it after putting in 15 of them it then stops working. Even after a few times of putting in information.

  35. #35
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Not sure how to help on your Excel issue
    These are tiny routines and so there should not be any resource issues. You are using 2010 and so am I, and so there should not be an issues there either.
    Suggest you switch off your PC and reboot.
    Hopefully just got itself in a knot.
    Kevin

  36. #36
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I got it to work, I entered the information. I kept saving the information after a huge change. See the attached.Repair Form test2.xlsm

  37. #37
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    The part issue is the last thing that needs to be fixed from what I see then I can manipulate the information provided on that tab machine repair tab.

  38. #38
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    1. Thanks for the updated file. The data looks ok to me. But the important thing is what do you think?
    2. I have asked others to test printing the form and they succeeded without any problem - so the reason is most likely something to do with your default printer settings over-riding the code that sits behind the "Print a Copy" button - we will sort that.
    The fallback position is that it would be very easy to dump the input data to another worksheet - ie your old sheet named "RepairRequestForm" when updating the table. And then print from that.
    We should print automatically upon form completion after the parts have been input - do you agree?
    3. Have you thought about how you intend to amend data if an error has been made - are you just going to amend directly into the worksheet?

    Parts
    These need handling differently, because there is more than one item against each repair entry. Here I offer you a choice of 2 approaches.

    Suggestion1
    My much preferred option is that the parts information should be held in a separate sheet, along with the key fields that allow it to be tagged to a repair on a particular machine.
    The 2 tables would be linked so that you can see everything together.
    We can create a report so that you can search against a particular machine and see its history tabulated. (or anything else you could do if everything was in one table)
    Reports would be to screen, but you could also print them.

    Suggestion2
    This option (which will also work) is to put everything in the one table (as you started doing) but you will end with lots of entries against each machine and lots of blank cells.

    One reason I do not like option2 is that the tables will be without any pattern, and you are much less likely to spot errors.


    In 2 separate tables the data will be consistent
    Parts sheet
    - part no
    - part name
    - quantity
    - price
    - date
    - machine
    Other sheet
    -everything that is there now

    Filtering and sorting should also feel easier with more simplified data

    Anyway, it is your data, and you will be looking after its recording etc - so the decision is absolutely yours -
    just let me know which is your preferred route and I will go in that direction

  39. #39
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I think it looks great. We can work out the printing later. Maybe give me some pointers on how to fix.

    When completing the information on the repair form you completed can the information be loaded onto the repair form I created and then the employee can go to that tab to print if there is an issue?

    If there was an issue with the data entered then I will change the information manually on the machine information tab.

    As far as the parts thing. Is there a way that the parts can be entered manually and then added to a new sheet of all parts used?

  40. #40
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    That way we can choose from a drop down list later?

  41. #41
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    The part issue is the last thing that needs to be fixed from what I see then I can manipulate the information provided on that tab machine repair tab. AGREED

    I think it looks great. We can work out the printing later. Maybe give me some pointers on how to fix. YES

    When completing the information on the repair form you completed can the information be loaded onto the repair form I created and then the employee can go to that tab to print if there is an issue? YES we can build that in

    If there was an issue with the data entered then I will change the information manually on the machine information tab. OK

    As far as the parts thing. Is there a way that the parts can be entered manually and then added to a new sheet of all parts used? YES

    That way we can choose from a drop down list later? YES

  42. #42
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Is there anything I can help with?

  43. #43
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Briefly, waylaid there on another project. I will attach workbook with Parts included, and a report generator you to test tomorrow..

    Kevin

  44. #44
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    In the end thought it better if you told me which reports you would like to see, better than me guessing!

    I have added the functionality to allow Parts to be added.
    - having tried a couple of different thoughts out,decided was better to do the input using a single line (although the form that is filled in manually can have lots of lines)
    - I have left the old lines there for the moment - only the top line does anything.

    I mentioned splitting parts and rest into 2 separate table. Until decision made, the form updates both the individual sheets and also the "Joined" sheet - so you can look and decide later.


    Can you try a few test entries as follows:

    - complete section 1 & 2 (except parts)
    - click on "Update Sections 1 & 2"
    (which clears several of the fields other than what I carry into the Parts sheet)
    -input a few parts
    - click on "Add Parts" after each one
    (which clears Parts fields)
    - after input of last part for a Repair, then click on "Next Repair) at top of form
    (which clears all the fields)

    Please note that I have not put any checks to ensure that you click everything in the correct sequence (I will do that if you are happy with everything)

    Then look at the data in the 3 sheets and confirm that you are happy with its format and its content


    Reports
    I think the data entry is fine - it's doing everything I expect
    So we need to add a couple of controls, tidy up the form and that bit is done.What would you like to be able to pull reports for, or look at on the screen
    - everything for a particular machine for a period (from xxx to xxx)
    - everything for a clinic for a period
    - everything for a particular part number
    and which fields would you like on the reports

    One of the reports I have on my list already is the one that looks similar to your original form

    thanks
    Kevin
    Attached Files Attached Files

  45. #45
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Everything looks great. Everything is working perfect. As far as the reports go, I would like to see the three you have provided. Having the machines separated for a period of time, clinic and part numbers for a period of time would be just right. I can then manipulate the information based off of that. If there is a mistake made I will go to the correct tab and make the correction. As far as printing, if we could have a fail safe of the sheet I created to have the information provided would be great. What are your thoughts?

  46. #46
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    The print button still is not working on my side. Just prints the repair form button. On the parts section of the repair form, it only allows me to click on the first line for parts and individually add parts which is fine but if I was able to print the repair form from the print button, it will only show the part in the top line. the rest of the other lines do not work.

  47. #47
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    What you say, sounds fine.
    I intend to focus on this over the next 48 hours and have everything sorted by then.
    Tomorrow morning I will put in various controls which you can test - by 1pm (UK)
    Then I will transfer the input into something resembling your original form for printing your hard copy - all parts wil be printed!(by 6pm)
    The following day will be reports day, plus dealing with any niggles that you have spotted.

  48. #48
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Attached is latest version with added controls.

    - auto-generated "Form Number" added so that you can call back all the details of an individual form later - will also use this to print the actual form
    - all part fields are disabled until you click "Ready to add parts"
    - when you click "Ready to add parts" other fields become disabled
    - as before add your parts one by one
    - to enter a new form click on "Next Repair"
    - after completing input close the form
    - then go and look at the data in sheet "Joined"

    The data is now all in one table. I've given it a lot of thought, and that will be easiest for you.
    I have cleared out all the old test data.

    I suggest that you now start inputting your data for real.
    I have changed quite a lot behind the scenes, but have tested extensively and not encountered any problem
    If you find that something does not look right, then easy to fix directly in the sheet. But let me know.

    It would be helpful if you could enter at least 5 "real" Repair Forms fully (or more if you can) and reply with any comments/observations by 4pm UK time today. (it is now approx 1pm here)
    When I receive that workbook from you I will add the ability to Print the Form
    In the meantime, I will work on printing the form, but will now need to update your "Live" file each time I amend anything from now on. I will also need to test the printing routine against your live data etc

    thanks
    Kevin
    Attached Files Attached Files

  49. #49
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I see the changes. I like them. The last repair for 4, I was able to see all the information on the list of machine issues and all of that is missing. I need that information on that form so I can print that form off as well. What are your thoughts?

  50. #50
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    If you look at sheet "Joined"all the info should be there. If it is not, let me know now!

    That is where we will be getting the info to print from

    thanks
    K

  51. #51
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I am sorry, I messed something up on my side. Everything works great and came over great. I like how it populates as well. It is easy for me to change per machine and manipulate the information.


    Repair Form05.xlsm

  52. #52
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Here is the file with a draft for printing Repair Form

    I have focused on getting everything to function today - will tidy up after your comments tomorrow.
    You can amend fonts etc to suit yourself later.
    Change the form number in B1 and then click update.
    The print range is already set up.
    So hopefully if you just press print in the normal way it will just work
    You need to tell me how it fits on your paper and we can adjust
    Try adding several parts against an item and then you can see multiple lines

    Tomorrow the update button will go, and the form will update immediately when you change the number

    K
    Attached Files Attached Files

  53. #53
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Everything looks great. The only thing that did not work on the print form was the total amount spent after entering more than one part. it only puts in the amount of parts used after the first part entered. Everything else is working fantastic!!!

  54. #54
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Today I will send you various draft reports in pdf format for checking layout etc
    Do not worry about the content - I may be putting stuff in just for testing
    Just ask yourself:
    Does the report look like I want it to?
    Does it have the fields I want?
    Are any selection options ok?
    And I will amend each one to match your requirements
    Your workbook will be updated at the end of this process after you have checked all the report layouts etc

    Attached is the RepairForm as amended with your requests
    Attached Files Attached Files

  55. #55
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    That looks great. As long as I can add company logo later?

  56. #56
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Yes - it will be possible to add the logo
    Moving back to the input of the Repair Form - I am adding instructions directing cursor to next box automatically after you have entered data each time to make it quicker and logical.
    Can you input another form and note down your preferred sequence in a simple list
    like:
    Clinic
    date1
    machine no
    ,,, etc

    thanks
    K

  57. #57
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time


  58. #58
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Thanks

    But what is Ticket No? Is this the Form No?

  59. #59
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123
    yes same number

  60. #60
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Report by Part Number
    (I have added some extra data so that we have a bit of volume to look at - so ignore the content)

    I have selected a few column headings as a starting point
    This report is sorted by part/ by machine (could also add a date sort)

    What I now want to do is transform this into what you want when running it.
    Let me know exactly what you want and I will try to make the fit as good as I can

    What do you want to call the report?
    Do you want to add the company logo to all reports
    Are these the correct columns for this report?
    If not what needs to go, what needs to come in
    (all the columns in sheet "Joined" are available to you to select from)
    Do you want sub-totals of the total cost for each part number?
    Grand totals?
    Spacing
    Etc

    You will have the facility to run it from one date to another.
    Which other selection filter be useful

    (Note - your file will have its own in-built Report Creator, so that you can create your own totally tailored reports later, so I need to get a good understanding of how you want to look at your information. I will set up 3 reports and base the Report Creator functionality on that. Fundamentally all we are doing for each different report is pulling data from the single worksheet and presenting it in different ways)
    Attached Files Attached Files

  61. #61
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I would like to be able to run this report per clinic and per machine. The report can be called Machine Parts and Repair form. The company logo should be on all reports and tabs. The columns are correct for this report. Sub-totals would be nice to be added and the grand totals. Spacing looks fine as long as all information is visible.

  62. #62
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Yes - that is fine

    1 Other than on the original Repair Form, in which type of reports might the "Problem" and "How dealt with" descriptives be useful?

    2 Would it be helpful to you to have the option to issue reports in pdf format for emailing or digital retention purposes?
    Could provide this as one of the options when running any report

  63. #63
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    1- yes it would be useful in the report.

    2- The PDF format is useful for e-mailing and keeping documentation saved. Good Idea.

  64. #64
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Excel is repeatedly crashing on me today - I have no idea why - and it is holding me up. I have not forgotten you. I apologise for the delay.
    Kevin

  65. #65
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123
    i completely understand. I am currently dealing with emergencies at work as well.

  66. #66
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Hello Kevin,

    How are things going with Excel? I hope you were able to figure out why it kept crashing on you.

    Kenny

  67. #67
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Here we go again!
    Re-built from the ground up.
    Click on the RepairForm tab to activate the form
    Looks a little different.
    The Parts are now on a separate form which automatically loads after the first form is completed.
    Parts are not linked at this point to the "Valid" sheet - but it would make sense to do so when parts details available.Easy mod that you can add later when ready.

    Input a few forms and see what you think
    Attached Files Attached Files

  68. #68
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I like it a lot more than the original. After entering more than one form it started to give me errors. The first ticket allowed me to enter parts into the form but the rest did not. I will need to be able to print each ticket off as well.

  69. #69
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    What did it do?
    Did it just not open the 2nd form
    or did it give an error code (if so what did it say?)
    Printing will be as before - vba will send details to another worksheet and print from there - 2 sheets will be updated at once ("Machine Issues" and a "PrintThisForm") - would you like that to be triggered automatically when each form is completed ?

  70. #70
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    The second form wouldn't open and sent me to the VBA for repair on that form. I Just Tried it again a few times and it seems to have fixed its self. Each time a form is filled out it does need to be printed. Triggering it automatically would be good.

  71. #71
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Try this. I have made a minor amendment to the code when the 2nd form pops up,
    Hopefully the issue has now gone
    Attached Files Attached Files

  72. #72
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Tomorrow I will attach a version with an option to print each Form automatically.
    Will also include the functionality to "Amend" a previously input form

  73. #73
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Attached is three examples of the tickets being completed. The first ticket did not work at all, I filled in all the information with parts but it did not populate. The next tow did just fine.MachineIssues02.xlsm

  74. #74
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    I am perplexed because I cannot recreate what your worksheet is showing.
    The form auto-populates with the dates and then there are checks to ensure that there is an entry in every field before it saves.
    Clicking on "SAVE" transfers non-parts details to Sheet "MachineIssues" and, "UPDATE PART" does the same for the parts. So it should be impossible to see what your worksheet is showing.
    If you exit the first form without saving (the X in the corner) then not even the Form number would be saved
    (ie there would not be a record)
    If you exit the second form, the details from the first form have been previously save and cannot just disappear
    (ie there would be a record for the non-parts input only)

    I tried
    - deleting the records in the file you attached and adding forms - with zero errors
    - downloading the file from post#71 and adding a few forms - with zero errors

    Can you download the file again from post #71 and do exactly what you did previously for the first entry and check what is in sheet "MachineIssues", then repeat your other 2 entries made as before and check again.
    In the meantime, I will run through everything again.


    thanks

  75. #75
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I just did it again. The first ticket does not populate on the sheet when you hit the save button and the parts screen does not open. If I go to the machines issues tab and go back into the repair form I enter a second ticket and it allows me to write the next ticket. I deleted everything and started over on the same form and then it started working fine. On my side it is only the first ticket and then we are all good.

  76. #76
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Thanks.
    I am puzzled that it does not happen to me too.
    What is important though is that it always behaves consistently for you.
    I will make a minor amendment for you to test before we move on.
    Kevin

  77. #77
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    The only change here is that there is a button to initialize the Repair Form each time.
    Previously the form was initialzed when the worksheet activated, and then re-activated from the 2nd form.

    Click on the button each time to add a new repair and let me know.

    thanks
    Attached Files Attached Files

  78. #78
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Works perfect!

  79. #79
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Attached is file which includes ability to print the current form.
    Click on ADD NEW REPAIR FORM (which clears any previous form from the PrintForm worksheet)
    Add a form, plus parts and it automatically populates the PrintForm worksheet.
    After you have clicked on "FINISHED" wait a couple of seconds before clicking on the PrintForm worksheet - otherwise you will give Excel indigestion.
    It is already set up to print, just hit print from the PrintForm worksheet.
    Let me know what you think

    PS - "AMEND SELECTED FORM" is disabled for the moment - I am reworking that to take account of the earlier missing first Form - and will post that tomorrow together with the Report Generator)
    Attached Files Attached Files

  80. #80
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Works great. No issues with Entering information. All fields are working correctly.

  81. #81
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Amended now to include functionality to allow old Repair Forms to be amended through the same process and controls as new form creation. Allows errors to be corrected and additional parts to be added.
    If you want to delete a part rather than amend it, just change quantity to zero - it will still be in the database, but can be excluded from all reports.

    Add a few New Forms and then "Refresh Forms Log", before clicking on "Amend" - after that everything is as before.

    Please add several repair forms and amend in a variety of ways to ensure that everything is behaving appropriately. Check input vs output to make sure everything scheduled for change was amended correctly etc.

    On PrintForm have added a couple of buttons to allow you to print either to paper or save to PDF
    Please test both options.

    When you save to PDF, the file is called RepairFormXXXXX.pdf and will either be saved to your default folder or else the last one used

    Next will be the final amendment - the report writer.
    K
    Attached Files Attached Files

  82. #82
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    It seems that we had an error when trying to amend a forms. I was able to get it to work after a few times but it seems that right before or after amending the form excel fails and restarts. Everything works until it fails on me. I will try to fix today on my side. The print to PDF does not work and the print button seems to work.

  83. #83
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Everything seems to be working fine. I had to do a hard reboot of everything. Attached is what I have entered so far. As far as the money, it should have the $ sign for the currency. Everything seems to be working great.MachineIssues04 (2).xlsm

  84. #84
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Is it possible if I enter Machine 1 that it automatically populates with the correlating SN on the repair form and the same thing for the rest of the machine numbers?

  85. #85
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    As far as the money, it should have the $ sign for the currency - fine, a formatting issue within the RepairForm only - will amend

    Is it possible if I enter Machine 1 that it automatically populates with the correlating SN on the repair form and the same thing for the rest of the machine numbers?
    Yes - this is how the Clinic number field is populated. But some comments\questions before locking you in to this:
    1. This brings a requirement to set up a "1 for 1" table of Machine Nos and corresponding Serial Nos ( for all machines) in sheet "Valid"
    2. Best to block free entry into those boxes when entering RepairForm and make entry via drop-down only for this to work well - this forces the table to be kept up to date at all times
    3. Is this information readily available for all of your machines as of today?
    4. Is the machine no an in-house reference number, or a machine type? (my understanding is that machine nos are not unique)
    5. Do you agree with thought process?

  86. #86
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    the machine numbers are not unique but If I use this form in several places then it would be specific to that place. I will be able to set it up later with the unique machine numbers and change it later as long it is formatted to be able to do so. I should be able to use the valid tab to set this up later for multiple places as long as it is set up. Thoughts?

  87. #87
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    agreed - everything in the valid tab can be referenced from other tabs in the future

    Can I clarify that what you are asking is
    - can you set it up so that I can input anything into either field at present (including having a drop-down available for any machine number/serial no combos incorporated in table in sheet "Valid")
    - can it auto-populate the serial-no based on the machine no based on that table
    - can I then go back and amend the input after machines have been given an official unique number?
    - at some point in future (when table of machine nos/serial nos is virtually complete) can entries be forced via that table?

  88. #88
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123
    That is correct to all the above.

  89. #89
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    I cannot resist commenting that you want to "have your cake and eat it". LOL

    There are far fewer clinic name\clinic no combos - so locking this combo down makes sense.
    The way the clinic name & no combo is treated by RepairForm will not give you the required flexibilty, but it can be done in a few different ways. I will have a "play" with various different options and get back to you after I have tested and considered the ramification of each option.

    A related question - but not today's problem
    If you are thinking of going back and amending all the re-numbered machine numbers at some point, it could be quite an onerous task to do them one by one. My understanding is that each machine serial number is unique, and so, when the physical "re-numbering" job is done, there will be an equal number of serial numbers and machine numbers (1 for 1). If this is the case, it would be an easy job to write a macro telling it to automatically correct all the entries in sheet "MachineIssues". All you would need do would be to complete the table in Sheet "Valid" and run the macro, which would then look in each row in sheet "MachineIssues" for each "serial no" in turn and overwrite the corresponding"machine no" column with the new machine no. In fact you could run the macro several times if you build the table in stages. Good idea?

  90. #90
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Yes, each machine will be assigned a number that correlates with that SN. I think That is a good idea. The more I play with the sheet the more things that kinda pop out. It is fine the way it is for now if we do not want to get too far ahead.

  91. #91
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Attached workbook includes requested amendment to allow machine S/N to be pre-filled by entry in machine no box.

    In RepairForm :
    - select a validated Machine No in the Machine Pre-fill box
    - The pre-fill for Machine No and Machine S/N boxes is triggered when next box clicked on.
    - Functionality allows edit to both pre-filled fields
    - Have left the dropdown there - but not looking at anything - would it be helpful if it did?

    Beware - if click again on Machine Pre-Fill , then pre-fill triggered and your edit is over-written!

    Unit price in PartsForm now shows as 0.00 on form activation

    Index
    Have moved several tables from "Valid" to own sheets - easier for updating values, deleting rows etc . Will move rest later (carefully! RepairForm etc looking up values there!)
    Rather than having lots of open tabs, have put an index sheet at front of workbook

    When selecting forms for amendment I have made the sort descending - much more likely to be looking to amend more recent forms as time goes on.

    MachinesLog tab:
    suggestion - may be helpful
    To group similar machines together in the (RepairForm)Drop-down :
    - give them the same machine no in column A
    - then sort the table on that column


    Let me know your thoughts after testing

    Do you require something similar for parts?
    Should the Part Number trigger pre-fill?
    This would be achieved via an additional (optional) drop-down box which would trigger a pre-fill of the existing 4 boxes, all of which could be edited ("have cake and eat it!)
    Attached Files Attached Files

  92. #92
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Looks great! The machines auto populating is awesome! I love the new index page. Parts automatically pre-filling would be nice but that is a huge list of parts and numbers. We can leave that one alone for now. Thoughts?

  93. #93
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    1. Will post an amendment later today including the full parts functionality and then you can test it and prove that it works. When you are ready to use it, then you know it is there. May even help to motivate progress in that direction!! (same amendment as for machine no - which is already proven - makes a lot of sense to do this now)

    2. Whilst appreciating that setting up the Parts worksheet will be challenging, do you really not have anything whatsoever available to get us started to help minimise the input. Where are you getting the part numbers from? Is there no worksheet? (or word document? or anything else in digital format). Even if you only have part numbers or descriptions we could do something. Even one column in the table would be better than zilch.

    3. Will create a macro to build up a list of Parts from what is entered on the RepairForm (ie allow you to dump backwards from sheet "MachineIssues" into the parts table - then at least you get the benefit of using something that has already been entered previously into the RepairForm workbook (this not tricky at all - will probably build it into the Repair Form and then could happen at the time of input).

  94. #94
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I will enter the information for the parts if you build the list jsut like the machine numbers and SN. I will gather that information and put it in when you post the new one. Even if we have to put in temporary parts in the mean time to make sure it works and then I can add parts later as we go seeing there are so many. Would that work?

  95. #95
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I added the parts to the machine tab. This is a smaller list but that is what I have on me right now.MachineIssues05.xlsm

  96. #96
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    A far as I am concerned this is finalised except for the Report Writer. I would like you to add Repair Forms for a few days and see how everything behaves. That will allow for any "funnies" to reveal themselves.
    I have thoroughly tested and I get no errors, no matter what I do. Hopefully you will find the same.

    - Everything is now driven from the one menu.
    - Parts can now be added either via pre-fill or via direct entry, and every box allows editing
    - new function added "Refresh Parts List" which takes all the values entered previously in "MachineIssues" and turns them into a Parts List that link back to the input form (so it learns as you input!). Try this by looking at Parts List and then inputting a few Repair Forms, then run refresh Parts List and compare. It deliberately duplicates so you can delete the one whose price is least typical.
    - some additional input controls added in a few places to minimise risk of bad data entering the system.

    Hopefully it all will work. But you may want a tweak here or there.

    I suggest you get about 50 repair forms entered before attaching the workbook for me to add the Report Writer - testing it with your data before sending it back to you. That will give us a decent amount of data to evaluate the reports.
    Attached Files Attached Files

  97. #97
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Doc1.docx

    This is the error I get when trying to amend a form.

  98. #98
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Before sending you the file, I did a lot of quick tidying up, making everything link better from one sheet to the other. Removing everything from sheet "Valid" etc. I have caused a little problem somewhere.
    I will look into it shortly.

  99. #99
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    That was an easy one!
    I put in an extra line at the very end to make it auto-close the amend form if you say"No" to entering extra parts.
    I should have used "call" before the name of the procedure.
    Amended file attached
    Attached Files Attached Files

  100. #100
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I am having the same issue. I click on the amend form button, it opens the form, i hit save and the error comes up.

  101. #101
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    I have replaced that line of code with a message box asking user to click on the button instead. Not sure why that should make a difference, but it seems to!
    Attached Files Attached Files

  102. #102
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    now its working perfectly. I will spend the morning entering more part numbers without prices because we do not have the prices until we order them. we can enter the prices later when the time permits.

  103. #103
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    After entering parts in a new repair form, all the old parts that were on the last ticket are now on the new repair form when it opens to print. It does not clear the old parts entered on the old tickets.

  104. #104
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Are you happy to fix something in VBA yourself? If you would rather not, send me the file (just conscious you were unable to attach previously)
    It's straightforward.

    One line needs adding back into 2 procedures (this line should have been included when everything combined into one menu).
    Sheets("PrintForm").Cells.ClearContents

    Paste the above line as the first line of the code (below the line starting with Sub...) ending up exactly as shown below.

    How to do it
    Hit ALT + F11 (to open VBA)
    in LEFT window under MODULES there is one called INDEX
    (you may need to double click on MODULES to see its contents)
    Double click on INDEX to select it

    Then in the RIGHT window,
    (which should have about a dozen short procedures, all called something ending in _Click)
    - scroll to the bottom, and it is into the last 2 procedures where you need to insert the line

    To exit VBA
    - right click on the X in top right corner

    Now SAVE the file

    And TEST it.

    Let me know.

    EDIT
    BEFORE YOU START save the file, and then if anything goes wrong, close without saving. No damage done!



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

  105. #105
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    MachineIssuesLive01 (1).xlsm

    Here is the attached file. I entered everything into the index but it kept coming up with errors so I did not save it due to the errors.

  106. #106
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    The file you sent me has the same number of Forms as previous (4)
    If that is incorrect let me know and I will tell you how to copy your data into this file.
    I get no errors, if you do please let me know exactly where they occur.
    K
    Attached Files Attached Files

  107. #107
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    It seems to be working now. I restarted my computer again and this file seems to be doing fine. I am going to delete all entered tickets and I will send to you after a few days of playing with it.

  108. #108
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    OK - if anything occurs to you as you go, let me know, and then I can work on making the amendments before I receive the file.

    If the RepairForm is inconvenient in any way, tell me. Dragging the boxes to a different location changes nothing in the code. So if you want some boxes on the left instead of on the right, that is pain-free. So long as it is within the same form anything can be moved anywhere.

    k

  109. #109
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Found an error. See the attached. What can i enter to fix this line?Doc1.docx

  110. #110
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    For get it. I must have done something while I was entering repairs. I opened the old form and it is working fine.

  111. #111
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    OK - if anything occurs to you as you go, let me know, and then I can work on making the amendments before I receive the file.

    If the RepairForm is inconvenient in any way, tell me. Dragging the boxes to a different location changes nothing in the code. So if you want some boxes on the left instead of on the right, that is pain-free. So long as it is within the same form anything can be moved anywhere.

    k

  112. #112
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Now is the time to delete all previous versions - Excel does not always list on the top line, the file you used last.

  113. #113
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I have done what you asked. I have been entering all new information and for some reason periodically come up with errors. Here are the errors that came up but then when reopening the sheet it went away and didnt have that issue. It happened when I got to the 8th repair form.Copy of MachineIssuesLive02 (1).xlsmDoc1.docx

  114. #114
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    I will look into these errors - Excel can sometimes trip itself up by launching too early into the next command, in which case all that is required is to put a short pause into the vba to allow it to catch up with itself.
    Were you just adding new Repair Forms? (not amending)

  115. #115
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123
    That's correct. I was just adding new repair forms.

  116. #116
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Copy of MachineIssuesLive02 (1).xlsm

    Everything seems to be working great. I have put in about 18-19 new tickets. Looks good.

  117. #117
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Like you say, looking good so far.
    Just wandering if you would appreciate being able to add staff names on the fly?
    How would it work?
    When entering a name not in the Staff List, a message box would pop-up asking if you want to add the new name to the staff list?
    Existing names would also need to pop-up to help you prevent duplicate entries (2 names, only 1 person eg Pete Jones and Peter Jones)

  118. #118
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I think they way it is set now is just fine. I have been playing with the boxes by adding more names, parts, and such just by dragging down the boxes and adding more names. Thoughts?Copy of MachineIssuesLive02 (1).xlsm

  119. #119
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    What you have done all looks good. It is developing nicely.

    Having looked at your data, here are some (random) thoughts to consider as you progress.
    Feel free to ignore them, but if you make things complicated in the physical world, it makes it difficult (when it needn't be) to set up Excel to provide you with what you need.
    I am thinking of how useful your reports can be down the line, and also how easy it will be to group like items etc
    I am not asking for any answers (these are free hints and tips)

    Part Nos
    Seems to be a no pattern.
    Are you planning to standardize in some way so that they are all the same length, follow a structure etc.
    If there is a structure that I cannot see, Excel could pre-fill leading or ending characters etc
    Standardizing could make a big difference later when you want to group like items together etc?

    Part Descriptions
    Some descriptions are identical. But the actual parts must vary in some way - size, material etc
    So perhaps instead of "RO Fitting"
    something like:
    Part No . Desc
    45-9746 RO Fitting 6"
    45-4767 RO fitting 5"
    or
    45-9746 RO Fitting Stainless
    45-4767 RO fitting Brass
    Reports are more easily read with a meaningful description

    Machine Nos
    I have no idea about what type of machines you have or how many different types there are. But I know the manager of a company which takes a varied selection of machines to a wide variety of industrial plants to carry out out machining work on site. What his RepairShop manager is interested in knowing is how each machine type performs, and its related costs and repair history, its worked hours etc
    I would keep one run of numbers, starting at 1 (the next machine of whichever type becomes 2 etc), putting in leading zeros so that all numbers look the same, making sure that you put in enough leading zeros for the future, bearing in mind that machines die and their number retires with them. So if you currently have over 1,000 machines then 00000 to 99999 may be the range.
    Keeping separate logs of numbers becomes a nightmare over time (one type needs many more numbers etc) and achieves nothing that Excel can't tell you using one straight run.
    It would make sense to think of your groupings before you start numbering the machines and perhaps to build the machine type into the number (often a good way because everyone starts to understand when you are talking about machine A10060 vs B00050 that you are referring to specific type). Otherwise add a separate field in the Machine Log
    (NB minor mod required if you go with something like A12345)
    Last edited by Kevin#; 02-08-2016 at 02:46 PM.

  120. #120
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Part Nos-
    Part numbers will vary depending on the location and the type of machines that are in that center. This is something that might be taken company wide and each center will have a list of different parts depending on the need. It will be changed later to fit the needs of that center.

    Part Description-
    This is something that will depend on the equipment in each center seeing each center will have different types of machines and parts used. One clinic might not use the same parts as another clinic.

    Machine Nos-
    The machine numbers will also change depending on the location and type of machines in that clinic.

    The way the form is laid out will give us the option of altering the need of the numbers for all three to fit that center and to make that region or center specific. I am impressed with how it is and the different things we can do with it just based off of the formatting. I have added more part numbers based off of the machines that we are currently using. I am waiting on price verification and that can be added later the way it is laid out now.Copy of MachineIssuesLive02 (1).xlsm

  121. #121
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Part Nos-
    It is helpful if you can impose a structure immediately but that is not always possible.
    Easy to write a little macro that will convert your historic data from the old No to a different new No (based on 2 column of values) at some point in the future and then the historic data retains its usefulness

    Part Description-
    I would try to impose some kind of minimum standardization - viz content, sequence (eg Max field length 40,must contain Desc(max 20) + size/material (max 10) leaving 10 free for local oddities)
    Again later conversion an option

    Machine Nos-
    1.Analytically: If the machines are attached to a clinic, then do not need to build that in to the Machine No - machine/clinic association is automatic in the data against every line. Even if hopping between clinic, their "repair" costs at each clinic are tagged to the machine. Not sure what it would give you extra in analysis terms.
    2.Physically however : tagging machine to mother clinic makes a lot of sense - all the A..s belong to clinic A etc. Also if moved to a new base, change their name to B.... - leaving the numerical bit unchanged so that you can use that to monitor a machine through its life and through the A,B..etc monitor it through different clinics
    2 sounds the way to go.

    All sounds very promising
    Last edited by Kevin#; 02-09-2016 at 03:58 AM.

  122. #122
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I would agree. All sounds very promising.

  123. #123
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    The only thing that is sticking out right now is the $ sign being used. Do you know how I would change that to be automatically $$? I have selected the cells and right clicked and changed it that way but every time a new repair form is entered it does not automatically change. Thoughts?

  124. #124
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    That is down to the way the user form is set-up. I would expect it to print with the $ sign already.
    Does your question just relate to how it looks at input time?

  125. #125
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    1. Repair forms currently completed by hand at each clinic and then mailed in batches to you for input. Correct ? In a few months, might that change to input by each clinic?
    (just thinking about the form numbering system for the future)

    2. Looking at your live data, there appears to be a lot of similar phrases being typed again and again into the Problem Resolution box. Different repairs but very similar comments. It could help to have some kind of a pick box (at time of input) to grab and enter the common phrases to speed up this process, plus free-form text for any non-standard. Potential for big time-saving, better accuracy and consistency. Your thoughts?
    (not for now perhaps, but worth investigating)
    Last edited by Kevin#; 02-10-2016 at 03:27 AM.

  126. #126
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    $$$- Every time I complete a ticket it appears on the machine issue tab with the wrong currency. I tried to format that tab and columns that have money in it so it will automatically be the $ sign.

    1-There are a lot of similarities when a machine is pulled for repairs. I am going off history in one clinic right now to test things. Each issue might have been pulled for a different reason depending on what the machine was doing at that time. The machine could be in complete different states or doing something completely different but mean something else. If the form can hold a years worth of data that would be the way to go. Every year we start over with new information. In this case it would be easy to reset by just deleting the information on the machine issue tab.

    2- I like the idea of having a pick box for a code to express what type of issue it is. For example, If I have a conductivity problem. I would choose the number 5- Condo issue. this would allow me to manipulate the information later to see how many different 5 issues we have in a period of time. We could use the numbers in 5, 10, 15, 20, 25, 30 and so on. thoughts?

  127. #127
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Formatting is driven by user form when value written to sheet "MachineIssues". Will amend when Report Writer is installed. What are you currently displaying?( I see $100.00 which looks perfect - country settings conflict perhaps )
    1 To keep using the same form number for a year would require an amendment to make the non-parts section of the form behave like the parts section. Most of your entries would be amendments. We could pull in clinic and machine details based on entry 1. BUT Why change the number for a new year? The Form No could be the Machine No and it could live as long as the machine.

    2. We would need to allow for multiple pick codes on a single repair. I'm thinking we use the code to drive the associated text into the comment box (like today) - the only difference is that there is no typing, but it remains understandable. Include the code also (which is not really necessary but may still be useful at some point) and then search on either the text or the code.
    Are 99 code options adequate, given range of issues and range of machines?
    (Beware of breaking it down too far, we need 80%+ of problems to fit in a handful of codes for each machine type - otherwise no patterns will be visible, just lots of single items!)
    I have added this to my list of items for further thought.

  128. #128
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    1- Is it possible to keep the form numbers to keep going with out repeating? So that way we don't have to clear the information yearly and have repeat numbers the following year?

    2- I think adding another drop down box for the staff to pick a code would be the best route to go with the description of the issue being typed in section 1. This allows for the reporting to be more detailed later. I think 25 codes would be more than enough.

  129. #129
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    1 Form No - at the moment we are treating each repair as a unique event against which we are capturing various values.
    Plan is to send out the RepairForm printouts (or PDF's) to clinics. And they will just file them. At some point they will want to refer to a few of them. How do they find the one they want quickly? Also a copy is being kept centrally. How will you find the one you are looking for later? A unique form no makes that simple even if you file sequentially and they file by machine no.
    An alternative is to view the event as the machine itself (either annually or in perpetuity), each repair is a sub-event, which you can number (using the Form No) or you can use the Section2 date instead which is also unique to that machine. How do you file the paperwork if you need to refer to it later?
    Putting the physical paperwork aside, for a second: the Report Writer is going to allow you to dump either to screen or paper quickly anything you want - including a summary for each machine over a period of time, or a clinic etc - it will be very flexible. Also searching will be powerful.
    My recommendation for the moment would be to live with the existing Form No until there is a reasonable amount of data to look at. Converting the data to look at it differently will be easy. Let's set up a play-file to look at different alternatives and decide then.

    2 Do you want a dropdown just for section2 or does section1 also need one?
    It would be useful if you begin building a "Code" list in the workbook.
    Insert another sheet and name it "IssueCode" (no spaces), so that it contains a few typical items
    WITH: Column A = 2 digit code (use format cells, numbers, special format "00"), Column B = (brief) issue text.
    I will think it through and try some ideas out, and then you can test it alongside the Report Writer next week.

  130. #130
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    1- We file the paperwork for both the repair form and the itemized work by machine number and date. Looking forward to the play-file.

    2- We just need a drop down box for the over all ticket with the code. Attached areCopy of MachineIssuesLive02 (1).xlsm the codes.

  131. #131
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Ok - wil look at all that tomorrow

  132. #132
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    There will be more codes as we go and I start to find more issues.

    Copy of MachineIssuesLive02 (1).xlsm

  133. #133
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Attached is the form with Codes added to the repair form when entering a new form. I need to configure it to the machine issues sheet. Let me know your thoughts. Copy of MachineIssuesLive02 (1).xlsm

  134. #134
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Problem Codes :
    1. I am thinking you are likely to want to record more than one Problem code against a repair ticket
    (if I take my car for a service the brakes may be faulty , but on inspection it may also need new tyres)
    2. Rather than add another column to "MachineIssues, we will try it first by using an extra box on the RepairForm to allow you to pick as many codes as you want. Excel will transfer these as the first items in the Problem Resolution box, which you can then add to.
    3. If, after looking at the reports, you prefer to have these in a column all to themselves, another column can be added

  135. #135
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    I have added Issue Code functionality and it works well
    I have asked someone (who has not seen it before) to test the Report Writer today to see if he can break it
    It will be attached for you to test tomorrow morning
    Functionality includes
    - create any number of report templates
    - ability to select any columns (from sheet "MachineIssues") in any sequence
    - pre-select up to 5 levels of sort in any report
    - choice of records to appear on report (all clinics, specific clinic etc)
    - from date... to date...
    - sub-totals
    Also can tailor all reports using Table Filter Options (before printing)
    Very flexible

  136. #136
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    That sounds good. I will then spend the next week on putting in information and adding new repair forms.

  137. #137
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    My tester made a couple of useful recommendations now implemented and being tested. Will attach file later today.
    Have you entered any more forms? The version you last posted ends with Repair Form No 00023
    If so, can you attach your latest file by 7pm UK time (and do not add/modify any items after that) so that the file I attach later this evening contains up-to-date records
    thanks

  138. #138
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I have not added any new forms. We are going through a huge construction and I was waiting to add more forms until the new one comes out.

  139. #139
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Been out of town myself, and ran into an issue that something was taking far too long to run which I think I have resolved. Here are a few reports so that you can see how it's all looking.
    Having tried a few reports I have decided that the only way to go with Issue Code is to give them their own column, otherwise awkward to report well on them without being creative.
    Attached Files Attached Files

  140. #140
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I hope this one goes through. the reports look great. Is it possible to alter the reports as I go later down the road? Such as isolating the reports per machine for a selected amount of machines if I did not need them all?

  141. #141
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    My PC is refusing access to this site. Says under malware attack. iPad less fussy!
    Yes you will be able to select what you want.
    Also all reports can be altered and new ones created

  142. #142
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Mine is doing the same thing because I am using google chrome. I am now using internet explorer and it is working fine.

  143. #143
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Firefox, Chrome and IE are all blocking me - all because of google I think. Hopefully more access tomorrow!

  144. #144
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I had to change my IE to MSN.com

  145. #145
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    A lot has changed from the previous version.
    From now on it is important that nothing is amended manually in sheet "MachineIssues" - all amendments should go via the input form to ensure that the data is recorded in a consistent manner, with consistent formatting etc. If individual cells are amended in sheet "MachineIssues" it is likely that some of the reporting may look peculiar and it will be very difficult to work out why, when we have lots more entries in the worksheet.

    Suggest you first try running the reports that have been set up and try various options etc. Sub-totalling may require further work – tricky to automate with so many things variable.

    Then =try creating new reports, or modifying existing ones. Move columns around. Delete columns etc. When you create a report, you will see the columns build in Row2. The first few columns are automatically included – they can be deleted or moved around.

    Look carefully at the instructions within forms – sometimes you need to single-click other times double-click to make things happen. Also make sure that when you try to select items that they are highlighted in blue.

    Try printing to paper and to PDF. I have set it up so that the PDFs open to screen (they are also saved to your default or current folder) – means you can save them to preferred location.

    Monetary values should now appear as $
    A field for Issue Code has been added
    Also added a field for total Parts Cost for each Repair Form – so that the part details not required to get at summary value. Automatically added (on subNo 00) at time Repair Form is input.



    No doubt there will be a few hiccups!
    Kevin
    Attached Files Attached Files

  146. #146
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I just started to run the reports and it gives me a debug error when trying to print to PDF and same thing when clicking print to paper. Also when I select run report by machine number and have machine 1 selected it gives me an error saying to to select at least one when I have one selected.

  147. #147
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I just started to run the reports and it gives me a debug error when trying to print to PDF and same thing when clicking print to paper. Also when I select run report by machine number and have machine 1 selected it gives me an error saying to select at least one when I have one selected.

  148. #148
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Made a minor amendment on paper print sub. There was an "s" missing in one line. That error may have left Excel confused. PDF is working fine.

    make sure you delete previous file
    Attached Files Attached Files

  149. #149
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Everything seems to be working great. The only issue with the reports is when I enter the dates for example 1/1/16 to 1/31/16 it still gives me everything outside of those dates.

  150. #150
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    I will look at that. I amended the "to" and "from" dates to show only what was in the worksheet. They are derived from the values in the same filtered column using the min and max functions - so it is a bit of a puzzle!
    Excel is putting the correct condition into the filters but the dates in the filters are not being selected. I am guessing it is treating the filter values as text, but I have no idea why, yet.

  151. #151
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    I will look at that. I amended the "to" and "from" dates to show only what was in the worksheet. They are derived from the values in the same filtered column using the min and max functions - so it is a bit of a puzzle!
    Excel is putting the correct condition into the filters but the dates in the filters are not being selected. I am guessing it is treating the filter values as text, but I have no idea why, yet.

  152. #152
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    The code copies date-filtered data from "MachineIssues", pasting it into "ReportRun".
    This is achieved by copying the "visible" cells (after filter applied).
    A line of code cancels the filter in "MachineIssues" after copying the data.
    I have now learnt that if that line is inserted before the data is pasted, it nullifies the filter!
    One minor modification added - the box to click to run a report is now disabled after running it once. VBA can become confused if box is clicked a 2nd time without a reset.
    Please delete the previous file.
    Attached Files Attached Files

  153. #153
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I was able to create a report and use the dates that are auto populated in the date field the report runs fine but when I select certain dates it gives me another debug error. I tried to add the file but the forum is acting up.

  154. #154
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    To be more clear. I enter the dates, select my report, hit the submit button, it generates behind the screen, then I go to select the drop down box to filter data and it gives me the debug error.

  155. #155
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    I get no errors whatsoever no matter what is selected and so I have not been able to re-create your debug error.

    1 Which report are you selecting?
    2 Precisely what else are you selecting afterwards?
    3 Does Excel/VBA tell you where it is encountering a problem?
    I will try those values and see if I can re-create.

    If you close Excel and re-open the file can you re-create the same error?

    thanks

  156. #156
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I created a new report to use. after selecting the date range and the report and hitting the "select report and hit here button" I then select the the drop down box to filter data, Thats when I get the error. It then takes me to VBA and highlights the last line before end sub.

    Private Sub cSelectField_Click()
    Dim selectionRangeName As String
    Dim selectionRange As Range
    selectionRangeName = "z_" & Replace(cSelectField, " ", "")
    Set selectionRange = Range(selectionRangeName)
    Me.lSelectValues.List = selectionRange.Value
    End Sub

  157. #157
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    The fact that the error occurs with a new report is helpful.
    The code is probably looking for something it cannot find - possibly column that I have selected every time (by chance) in testing.
    Which columns are included in your new report? Please list them in the same sequence as the report.

    thanks

  158. #158
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    The fact that the error occurs with a new report is helpful.
    The code is probably looking for something it cannot find - possibly column that I have selected every time (by chance) in testing.
    Which columns are included in your new report? Please list them in the same sequence as the report.

    thanks
    ps - I too am struggling to post - takes multiple attempts each time.

    and then having told me it's failed it appears twice!

  159. #159
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Section2 Date Clinic Name Clinic Number MachineNo Machine Serial No Machine Hours Resolution Part Description Part Number Quantity Total Cost

  160. #160
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    I created the report several times in both Excel2010 and Excel 2016 and did not get your error once.

    But I did notice something. I opted to filter on "Clinic Name" first - when I selected a specific clinic, a message box popped up telling me to select something (it did the same with more 2 clinics selected).
    All the other filters were fine and the problem disappeared after a different filter was chosen first, and after clearing the filters, it was happy to allow Clinic Name to be used first. This did not happen at all after the file was saved and re-opened. I have tried both Excel 2010 and Excel 2016 and it behaved identically in both.

    The code is working correctly but Excel/VBA is holding something in memory immediately after generating the report that needs clearing so that it goes to the next box with a clear head every time! Although manifesting itself in a different way to you, it is probably the same issue. I will post an amended version tomorrow morning - at the same time, I will check and update any other boxes that could be afflicted in a similar way.

    Can you run one test for me -
    - delete the report you created, Save the file, Close Excel, Open the file again
    - recreate the report, do not run it but save the file, Close Excel, Open the file again. Try running the new report and use the filter.

    thanks

  161. #161
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I am still getting the same error. When I select the dates I can see in the background that only one repair form loads when it should be several. I am trying to load the file but am having issues loading on the forum.

  162. #162
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Having a copy of the file won't help me solve this puzzle. So do not worry about posting it.

    Hopefully the dates issue is is something to do with US vs UK date settings. All the macro is doing is automating the standard Excel filter.

    Another little test

    1. Run the first report ("All Fields") and close the userform by clicking on the X
    2. Stay in sheet "ReportRun"
    3. Go to cell A1 (=Section2 Date) and set the autofilter to on (tab Data, select autofilter)
    4. manually set the filter as follows: Select the 5th and 6th of February entries using the checkboxes and you should see only those entries
    5. now try what the macro is doing as follows: (Instead of using the checkboxes), click on "Date Filters" then select "Between" and put in the same 2 dates (you can select them from the dropdown). My guess is that there are no visible results
    6. If so do the same as 5 but input the dates manually as 05/02/2016 and 06/02/2016 (ie UK notation). My guess is that the entries for the 5th and 6th of February are now visible.

  163. #163
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    When I filter the dates, there is only one repair that comes up for Jan, not the rest. When I did the dates manuallyin UK notion it also does not provide everything.

  164. #164
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Apologies for delay, I was dragged out of town unexpectedly yesterday.

    I have tried several ways to replicate your errors and I cannot. So a new approach avoiding the userform.
    New sheet "ReportRunOptions" has been added. This contains all the same options as the userform but drives them from a worksheet instead.
    Can you let me know in which format the dates appear in row 3 (hopefully your defaults)
    When making date selections, if there are any issues, try inputting the date in this style Feb 06 2016 (Excel should then treat it properly)

    Please try all the options on the new sheet and let me know how it goes.

    thanks

    EDIT: I should have said to only try the options from the new sheet. On index, the option = Run Reports (Sheet)
    Attached Files Attached Files
    Last edited by Kevin#; 02-26-2016 at 01:35 PM.

  165. #165
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I get the same Issue. I think it might be from the beginning of the document. I didnt realize that UK notion was different. When I look at everything that was input for January 1/1/16 to 1/30/16 it still gives me an issue and only brings up one repair and thats it.

  166. #166
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Keeping information and auto population onto a new line each time

    Try February dates.
    You are getting same result as me and is what it should be giving based on the data - so that is good.
    It is looking at Date2 (not Date1) for the filter. If the underlying data is not correct, that is easily fixable.

  167. #167
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    I understand what the issue was with the original report. I was using the form for MMDDYYYY and not DDMMYYYY. After switching to DDMMYY I was able to run the report and get all the tickets needed. How would I change it to be MMDDYYYY? so it will not be confusing if I share this with others in the organization?

  168. #168
    Forum Contributor
    Join Date
    10-06-2015
    Location
    USA
    MS-Off Ver
    MS office 2010
    Posts
    123

    Re: Keeping information and auto population onto a new line each time

    Hey Kevin,

    I am having issues with this now. I am trying to start fresh with the data that is being dumped into this every month. I would like it to be specific for each clinic. When I delete the parts and all previous information on all repairs, put in a new repair, up date the repair list after each repair form, the document crashes. Some time I get a debug error. Can you help?
    Attached Files Attached Files

+ 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. Auto date population
    By biznez in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-27-2015, 04:26 PM
  2. Auto population on a form
    By twiglett811 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 05-05-2014, 05:51 AM
  3. [SOLVED] Auto-population Troubles
    By dreinisch in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 10-10-2012, 12:56 PM
  4. Auto Population
    By JustinZ in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 10-26-2009, 09:29 AM
  5. [SOLVED] Help :-( - Auto Population
    By Stacey in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-09-2006, 11:45 AM
  6. population list box without repeating information
    By medicenpringles in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-23-2005, 11:05 PM

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