[VBA/Macro] Assistance on Form/data logging in between sheets

0

Hello, reader.

I made an Income / Expense / Savings tracker on Excel for Mac [through O365].

Everything else works fine, but I was trying to make it more foolproof by adding a home page which doubles as a form. 

For context, I have a "logs" worksheet -- which has three tables (Income / Expenses / Savings) of same column numbers. To use the tracker, the user must log manually the details in the Log worksheet so that Excel can record it and reflect/visualize the data in a separate sheet. Normal details are: Date / Amount / Category / Remarks.

A sample expense log could go like: | Sept-15 | 10$ | Food | Pizza
A sample Income log could go like | Aug 31 | 100$ | Salary | Monthly Pay for August

While it works, I figured it will get ugly once there are too many logs -- plus there might be a risk of accidental editing of the previous logs.

I plan on putting a blank table on the home sheet, with 5 column that the user can fill out:

Type { income / expense / savings } [Could be a drop down list]

Date [Looking for a way to automatize this]

Amount [user will input the amount for the corresponding transaction]

Category [user will select a category label depending on the "Type" chosen]

Remarks [User will input this manually]

What I dont understand yet is I want to make a macro button that the user can click, and Excel would automatically copy the data on the Home sheet, and add it to the appropriate table in the "log" sheet, while clearing the home sheet in the process to be readied for another input.

Can anyone help me on this? I'd really appreciate it. Feel free to suggest ideas as well on how I can improve my file :)

Answer
Discuss

Answers

0

Frankly, I don't believe that you will bring this project anywhere near to completion without resorting to VBA. When you do you will have to redesign much of the setup. Therefore it's important to make such a decision as early as possible, that is as soon as you find yourself playing with the idea.

One of the changes you will have to make is to split your log. Given that you have many more expense entries than entries for income (though I hope and wish it would be the other way around) your current setup will soon hit its limits. It's just too inflexible. You will need two separate worksheets. Definitely.

Once you have that the better VBA solution would be a user form where you can easily make your entries and post them to the logs. In fact, you hardly need to look at them ever again - once you resolve the issue of having dynamic ranges which don't end at row 558. That's much easier when using VBA.

But your idea of a Home page can't be implemented without VBA. You will need it to transfer the data from the input sheet to the log. However, Excel does offer another solution, the Data Entry Form. Find instructions to set it up here.

For entering a current date you wouldn't need VBA. Ctl+; (press the semi-colon key while holding down the Control key) inserts the current date in a cell. No VBA can be faster. Make sure you set the cell's number format to display the date in your preferred format.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login