Avoid writing 100's of UserForms with a few 'main' ones

[PDBartlett]

Hello

I have a spreadsheet where the user can enter a figure for over 100 items on a Profit & Loss Account via a UserForm for up to 5 years. At the moment I have a UserForm for each item, or at least I have 10 currently working which are: VAT Sales Type 1-5 and Non VAT Sales Type 1-5....

The user enters data for year 1 and the following years calculate based on a percentage increase/decrease that they also enter.

Simple stuff except I have to create a UserForm for every item on the P&L (e.g. Direct Costs Type 1-5, Admin costs Type 1-5 etc etc...) and change the code ever so slightly so that it inserts the data into the correct cells on the worksheet. I also have to change the name of each TextBox on the UserForm so that the new code works (over 100 TextBoxes per UserForm).

I believe there is a way, but I don't know how, to have one main UserForm for all VAT Sales, one main UserForm for all Non VAT Sales etc, but I don't know how this would work in terms of inserting the data into the correct cells.

Like I said I can finish this spreadsheet but it will end up with over 100 individual UserForms, any help on how to create some 'main' UserForms would be seriously appreciated!

Thanks

[Richard Buttrey]

It would help if we could see the layout you're using, but first impressions are that there are two tasks going in here. One is to capture original data, and second to have it appear in specific places on a worksheet.

If that's the case these two tasks need to be separated. The input should build a simple 2 dimensional table of the sort that could be used by a Pivot Table, i.e. where each column field represents a particular type of data and not simply different values of the same type of data.

Once you have this regularised table then you can easily use functions in your reporting 'mask', or indeed use a Pivot Table to analyse the data.

One way or another though I can't see that it's necessary to have all those forms. Upload the workbook if you need more specific advice.

[PDBartlett]

Forum Upload.xlsm

I have uploaded a smaller version, just deleted everything not needed for the moment.

You can see I've started the VAT Direct Costs, which will be my 11th UserForm.

Hope that helps

[Richard Buttrey]

Hi,

See attached.

Personally I think you are complicating this too much by using 'busy' user forms.
The approach I always adopt unless clients specifically demand user forms is along the lines I show here on the Data sheet.

Use a data entry area like row 4 for capturing initial data and have a small macro that adds this to the database underneath. I've not yet added any code to the button but I guess you see the idea. You can use validation drop downs as in B4:C4 and E4 could easily be made an IF() function to derive the correct % dependent on the Sales / Type.

As I mentioned previously, once you have this table of data then the formula on the Master sheet are relatively trivial. But you also now have the option to use Pivot Tables for analysis and these are extremely powerful.

Hope it gives you some ideas

[PDBartlett]

It's a good idea, Richard, but the user won't see the 'master' spreadsheet or anything like that. On the main workbook I have a dashboard whereby they can click buttons to navigate around without having to see all the rubbish, it will brings reports up etc. So having UserForms to easily enter data is ideal for this situation, I was just hoping to cut down on the work load!

[Richard Buttrey]

Hi,

OK, fair enough, but don't you just need a user form that will capture the details for the few cells in my example?
The approach I showed could still be used to build the database albeit one hidden away in the background.

For instance instead of having a form which contains a complete matrix of 120 sets of boxes one each for each month/year/value/% permutation, just have four boxes, one each for Date (you don't need to bother about which year since that's a function of the date), Sales/Vat, Type & Amount. The % seems to be dependent on the Sales/Type so there's no need to manually capture this since it can be worked out.

The form could be used as both a Data Entry form, but also as a 'Database enquiry form' with a simple switching macro to change the mode and edit the database if that's necessary.

[PDBartlett]

Ahh ok I see what you mean. Having the user to actually select what item they want to input/change (sales, costs, expenses etc) would save a lot of time! Although I still don't understand how I would make it insert into the correct cell?

However there is a slight catch... if I produce a UserForm based on your style on the attached spreadsheet, the user can enter a % increase/decrease whereby the following year (same month) will change - easy enough. BUT there is going to be a Master Assumptions sheet where ALL sales calculated going forward will equal a certain percentage and ALL direct costs will equal another percentage and so on for each item.

How would I make the workbook always use the Master Assumptions percentage unless the user enters a different one in the userform?

Thanks

[Richard Buttrey]

Hi,

Re entering %s. You'd simply read the default value into the box when the form loads. The user could then change it if necessary or leave it.

Don't get hung up on inserting the relevant data in the correct cell just at the moment. Getting the data into your report with appropriate functions is the easy bit. The immediate task is to be able to create the database table along the lines I showed you. i.e. you are using the Userform to build the table.

Believe me this is a very common requirement - and indeed the process is often complicated way too much by mistaken assumptions in the original design of the system. So much so that I have this standard reply that I often use in this forum.

You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.