I need to create a form that will export to a spreadsheet.

I am trying to create a front-end form that can be filled out by a user, which will then populate a back-end spreadsheet the user cannot edit. This spreadsheet needs to follow a standard layout that I have already created, and the form will populate predetermined cells (or will create cells following the layout) based on user input from the form. I also need the form to be able to add/repeat questions, as well as duplicate sections in the spreadsheet based on user input requirements ("Do you need to add a section? [Yes/No]" //If [Yes], then duplicate section, repeat questions in form; if [No], then export data to spreadsheet, exit form). The spreadsheet layout consists of sections. The number of sections/cells per section required will vary from user to user, but the type of data will typically remain the same, and therefore should be able to choose the name of the field from a list of some sort (drop down?) However, the user may need to create a custom name for a field. I also need the form to be able to automatically adjust cell sizes based on the amount of text for that value. It is important that this form is printable, and does not separate sections from page to page. Furthermore, each "Notes" field will vary in size, and should be automatically resized to only show the existing text, plus one blank line for hand-written notes. I would also like the form to prompt the user to indicate whether a field is needed (some fields will be permanent, and the user will not be prompted about these fields). If the field is not needed, I need the form to exclude it from the final output.


Spreadsheet screenshot first page.png

The image contains the basic premise of the final layout for the data. In the red bracket (forgive my terrible drawing), you will see three sections. Each section is its own entity, but contains the same type of data. Users will typically only need these fields, but may need additional fields depending on their project. If the user needs additional sections, the form should duplicate one section at a time. The top section that is not outlined by the bracket will not need to be duplicated, and will only ever exist on the first page of the spreadsheet.

Please note: I am not asking for anyone to do the work for me. I just don't know where to start, or even if this is feasible in Excel. I am willing to use VBA, but bear with me as I haven't used VB in nearly 20 years.

Thanks for all your help.

upload a workbook - see big yellow banner for instructions - show 10-20 rows of data stored as you envisage it.
what you appear to be wanting to create is a 'pseudo' database - the layout shown is not a compatible format for storage - the layout resembles a report format which you would generate from your data.
you use the word 'form' are you envisaging creating this on the sheet or utilising a VBA UserForm for data input. ?

You are correct, torachan: I would like to generate a report format from the data users input. As for the form, I do not want users to see the spreadsheet itself, only the output/final report from their input, so I suppose I would be using a VBA UserForm. I don't much care what the spreadsheet containing the data fields looks like The final report, however, must follow the layout I have given in order to facilitate readability. Ideally, the final report would be output into a PDF.

interesting project - not a 5 minute solution - check back in a couple of days should have draft together by then.

I suspect much of the data can be determined from drop-down lists so can you provide the data

e.g. Material, Tool Name, Tool Type, Tool Size ......

and can you provide idea(s) of how the Userform should look as the output is quite complex.

@auglocqnuk, the attached is a suggested approach - to enter data first 'CLEAR' the form - this will give you a sterile form and enable the 'ADD' button - fill your header data plus machine data - press 'ADD' - you will be asked if you want to enter another machine item 'YES' will leave the header data in place (just then fill the machine data, again press 'ADD') - if you answer 'NO' the line will be saved and the form will clear.
To update or delete select via the combobox then the listbox and modify your date and press 'UPDATE' or just press 'DELETE' to remove from sheet.
To print just use the combobox (only interested in recovering the drawing # - the listbox will fill with all machine ops related to drawing # - do not select from listbox, just press 'PRINT' - a PDF should find your desktop.
This all will need fine tuning, it is only presented as a concept, the PDF format will need 'page breaking' sorting as the header is a different size to the operation boxes so more code needed to control presentation.
p.s. form made to cover all items of data - put 'none' or just a comma in textboxes that are not applicable - there has to be some character in textbox to enable a save.
@John's comments regarding combobox selection would give you an added feature well worth considering to aid data entry consistency.

I broke it! What did I get wrong/miss? Setups.xlsm is the "before" version, Setups error.xlsm is the "after" version. Thanks for the help!

nothing too drastic - the only thing I have corrected is the positioning of (cells data) in the 'Print' sub - you had not got the offsetting correct.
Try the attached, I will leave the 'page break' coding until a final layout format is decided.

Not quite sure I understand, there is no date input, or do you mean print everything just in reverse order, can you upload a file with a couple of examples,

Hi torachan. I've been ill, so I haven't been working until today. I've managed to make some headway. In relation to the code, however, I'm slightly lost in some areas. For example, you mentioned (regarding the form I broke), that I had not gotten the offsets correct. I'm doing a side-by-side comparison, and I am a little lost in the area of the offsets. I know this is a big ask, but could you write comments into the code for each line? For the most part, it's fairly straightforward, but in other cases the comments would be of great help. Especially if you could help me link the code visually to the form function (I work really well with visual references). Take the code for the print button, for example:

Please Login or Register  to view this content.

I'm not sure how the highlighted parts work. How do the variables work? How do they relate between the form and the cells in the spreadsheet?

I'm not sure if my question is coherent enough to follow (I'm still a bit fuzzy from my illness), but comments in the code itself would really be of great value to me as I relearn to code.

Thanks so much!

i will look at this tomorrow as today is very busy for an 'old guy'.

Give the attached a try, I have resorted back to my original (rather than sorting out your mods) just to get the PDF to landscape and the addition of incorporating a picture.
Place your .jpgs in an easy to access folder - the selection of your picture should put the file path in the adjacent textbox (this textbox is not manually editable) the path will be saved to the sheet and can be altered using the update routine.
The reason previously that tools were out of sequence was the addition of extra columns - the last column is reserved for the row counter - this must always be the last column and its referencing within the code altered accordingly if you add columns.

upload your file that is showing fault as the one I uploaded is OK at my end - the 'P' & 'Q' are the Wingdings3 defaults for the rotation symbols - probably better if you select from a combobox.
my copy put the correct notes in the in the fields you mention
my apologises, I see what you mean (did not see that initially as all enteries were just 'Test'.
alter the last number of third row in this part of the print routine from an eight to a seven (this just controls the vertical offsetting)

change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Cool, so that edit worked, but the page layout is still broken. I haven't changed any code other than what you already provided for the fix to the text fields. I've found that there's a VBA function for page breaks. However, when trying to implement it in the print command, the code doesn't change anything. I'm guessing I need to place a version of this function in the filter somewhere, but I'm not sure how/where?

Please Login or Register  to view this content.

B

I could also use a "Cancel Add" button. I can add the button easily, but I'm not sure how to actually code it. If I'm not mistaken, that would involve removing a row that contains null fields, which are currently not allowed. I could remove that if statement, but that would still leave me with an entry that I then need to find and delete.

Currently, all data relating to a part/tool is listed when selecting a part in the Print and Search Facility. Can I limit that data to only the part number and tool data, or even list the tool number in the first column?

Finally, in testing the form on different computers, I've found that it's set size is a bit cumbersome on some screens (especially laptops). I've tried to add code to be able to resize the window, but I can't get it to work properly, so I end up removing the code and starting over.

I'm reading "Mastering VBA 2019 For Microsoft Office 365 2019 Edition" in my spare time, but as it turns out I don't really have much of that these days.

I can't thank you enough for all the aid you've provided me.

I will look at page break and possibility of printing individual/selected parts (each will influence the other).

started to look at 'broken page' layout - first question I should have asked - if you are printing out to paper, what size paper are you using (as we universally use A4, which I believe is not used in USA)

in this block of code add the two separate extra lines that alter the row height - then see if by adjusting the size you can span the page break of your particular paper size.

Please Login or Register  to view this content.