Budget Workbook Cost Splitting Concept

[Atroxell]

Hi All!

I am working on refining a budget workbook and I have run into a road block where I need some code...

In an attempt to keep it short, here's how the workbook is designed so far:

It contains a worksheet for each cost center within the department. On each worksheet is an area designated for each month of the year. As time goes on and the expenses mount, a Summary area to the right side of each month shows the amount of budget remaining for the month, the dollars spent for the month and where that cost center is for remaining dollars in the annual budget.

Of course, after doing a worksheet for each cost center and building the summary reporting pages for the workbook, someone has come along and added a new twist-there has to be the capability to record when a cost is incurred and split among cost centers. Lucky for me, the actual expense doesn't have to actually be divided amongst sheets or anything like that, just the fact that the split occured and how it was done. So, here's what I was thinking:

I can build a "simple" button that would appear in a certain cell in the active row when the user is entering data. If there's to be a split for the cost, the user would click on the button and a user form would pop up with a text box to enter how the splits occured. After entering the information, the user clicks "OK" on the userform and the split is recorded on a completely separate worksheet (we'll call it "Splits" for simplicity). This worksheet contains all of the splits, regardless of cost center it occured in. When the data is populated to the worksheet, the location of the data on the "Splits" worksheet is recorded and a hyperlink is built in a cell on the original worksheet where the expense was entered. That way, when the user needs to see how the split occured, they can find the record of the expense and click the hyperlink to view the split.

The tricky part is I don't want the button to be visible anywhere but on the active row, and once there's a hyperlink in the relevant cell the button will never show again.

Does this make sense? I hope so...

Al

 

[MorganO]

Al,

Attempting to design a button as you described below is going to be quite difficult. You would have to use a command button, and attaching these to an exact cell location dynamically is not easy because the command buttons are positioned using pixels locations within the worksheet screen and not cell locations.

I have seen code that can resolve cell locations to pixel locations but it is quite complex.

I would recommend instead using a static button for this purpose - like a macro placed in an existing toolbar (Tools|Customize|Macros|Custom Button or Custom Menu Item).

When a user needs to do a split they could press the custom button and the code attached to it could do as you have detailed above. The code will know what cell is active thus you don't need a seperate button for each line. If they press the button and a split has already been applied to the cell, the code would not run.

Hope this helps.

Owen

 

[Atroxell]

Well, while that's not the answer I was hoping for, it's definitely worth reading!

I will build it out in your suggested manner and post the problems/results that I get as I go along. Hopefully this won't take me more than a couple of weeks.

(It may take longer because this is a "nice to have someday" project from the boss and not a real "dying to have it yesterday" project--so i have to work on it in my "free time".)

Thanks for the direction!

 

[MorganO]

If you are really interested in going the command button route, here is a link to the code I remembered:

http://www.cpearson.com/excel/FormPosition.htm

It discusses positioning userforms to specific cells, but it can most likely be adapted for command buttons as well.

Will be about a week before I can get back with you if you have further questions, am heading out of town with no internet access!

Take care.

Owen