UserForm Income / Expenditure Database?

Hi guys,

Good day. I am currently working on a new excel VBA database project where I have a userform for Income and Expenses calculations and upon hit the submit button I want the data to insert in an excel sheet named DataBase where I will have two tables for the respective Income and Expenditure.

The brief about my project is –

• I have created a userform to record and calculate “Expenditure and Income” of a business.
• The userform has various expenditure types and Income types along with quantity and amount.
• I want the data to insert (once hit the Submit button) in an Excel Sheet called DataBase (as you suggested). However I want the excel sheet to have 2 separate table as Expenditure and Income and the data should inset into the respective table.
• I have also inserted one TextBox in the Userform as a date field where I used Today function to record current system date to the both tables?

Can someone please help me how to achieve this? Please see attached the demo file. Also please feel free to manipulate it if you have a better idea…

Thanks.

Originally Posted by Richard Buttrey

Hi,

The first question I'd ask is what are your reasons for creating two data tables. Why not create a single database which will be more flexible, particularly if you want to use a Pivot Table for any subsequent analysis.

Just add an extra column to record the category of transaction. i.e. Income or Expenditure. Hence your database columns would be

Date
Category
Type
Quantity
Amount
Total
Notes

The next point is do you really want to clutter up your user form with separate boxes for each Type. The only conceivable reason for doing this might be if any one transaction consisted of more than one type. In any case I'd still only have a userform which contained boxes for the fields I mention above.
And if this is a form merely for capturing data records, do you really want the Grand Profit, Tax & Net Profit since these are presumably outworkings of the data you capture and can be calculated in the background. In any case you don't appear to be recording the GP Tax & NP anywhere in the database.

Finally I'd just ask whether you really need a User Form. Personally when I build data capture macros like this I create a 'new record' row of cells above the database into which the user enters the various values. Then a 'Submit' button macro merely adds a new top line to the database, copies the new record row and pastes it as values to the newly introduced row.

Hi Richard,

Thanks so much for the reply. Ideally I would like to have something like you suggested in the first part of the answer. But I was not quite sure how to arrange that especially record the category. If would please provide a rough working file would truly appreciate that.

About the Grand Profit, Net and Tax part is just an extension to have a quick overview what's on the for at that point. Its not necessary and not required to capture of-course. Thanks again and hope to receive a working demo file please. Cheers.

Hi alansidman,

Thanks for those links. I will definitely have a look into those. Also, if you could please provide a rough working file would truly appreciate that. I am asking the same to Richard as well. Thanks.