VBA form that creates a new sheet within the Excel-document, containing the form-input

[backwrdsman]

Hi!

Excel-beginner here. I'd like to know if it's possible to create a VBA form (or form of any sort)
inside an Excel-book, that can take the input data and create a new sheet with the
information each time it's used?

The sheet should be saved with the same name provided in the textfield "Name" in the form.

After each filling of the form and clicking "Save" the form should go blank again and next time you
use the form it creates a new sheet, filling the Excel-book with new ones every time.

Is this possible? Can you please point me to any good tutorials. I've googled and found some
tutorials about creating VBA forms but none that present the possibility to add a function that
creates a new sheet inside the same document. Also many of them seem a bit vague in how
I'd "link" the form to the sheet and vice versa.

I haven't got that much experience in programming other than html/php..

The purpose of this is to be able to customize the form-fields and making it easier/faster
to enter information about certain projects. Also to be able to return and add more info/pictures/
calculations later on, in each "project" (sheet).

That's why I want every project to be saved in the same Excel-book. But if anyone have other ideas
how to pursue this (outside of Excel) I'd be glad to hear them

I hope you can help me somehow! Thanks.

 

[Dendro]

Yes, it's all possible within excel and i might suggest using MS acces if the data is merely used to store it in a database without much calculation.

How do you do this in excel?
in short: press alt+F11 and create a userform by rightclicking in the macro-tree on the left and select "userform", add some buttons and fields, give them a good name (other than the default commandButton) by right-clicking the button and selecting properties and entering the (Name) value. Double click on them to add code behind the buttons (here you will assign the field-values to a cell in a new sheet). Tip: switch back to 'objectview' by rightclicking the userform in the macro-tree on the left. If you want to know how to create a new sheet, press record macro and create a sheet. Now open this macro and use this code at the start of your 'enter' button-code.
This seems like a good tutorial:
Excel VBA Userform - Easy Excel Macros

Give it a try and post the result you have here ( even if you only managed to create the visual aspect of the userform), so I can take a look at it and guide you further.

 

[backwrdsman]

Hey thanks!

I should be done with this under the day and will return and show you the result later.

Another question about recording macros - if I have a pre-formatted sheet (different
colors and fonts) will this be containted in the macro and follow every new sheet as
well? It would be great if this is possible.

thanks

Yes, it's all possible within excel and i might suggest using MS acces if the data is merely used to store it in a database without much calculation.

How do you do this in excel?
in short: press alt+F11 and create a userform by rightclicking in the macro-tree on the left and select "userform", add some buttons and fields, give them a good name (other than the default commandButton) by right-clicking the button and selecting properties and entering the (Name) value. Double click on them to add code behind the buttons (here you will assign the field-values to a cell in a new sheet). Tip: switch back to 'objectview' by rightclicking the userform in the macro-tree on the left. If you want to know how to create a new sheet, press record macro and create a sheet. Now open this macro and use this code at the start of your 'enter' button-code.
This seems like a good tutorial:
Excel VBA Userform - Easy Excel Macros

Give it a try and post the result you have here ( even if you only managed to create the visual aspect of the userform), so I can take a look at it and guide you further.

 

[Dendro]

if you create a new sheet, it's a totally new sheet without any formatting.
What you could do is record a macro and copy your empty pre-formatted sheet - instead of just creating a new sheet - and use this code at the start of your 'enter' button-code. Or you could record while you format a new sheet(more work but you wouldn't need a empty pre-formatted sheet ready to copy in your workbook). Play around with the 'record-macro'-button and see what comes out, if you need help cleaning up the code come and post it here