Budget Workbook Cost Splitting Concept

Atroxell

Active Member
Joined
Apr 18, 2007
Messages
422
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

MorganO

Active Member
Joined
Nov 21, 2006
Messages
483
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

Active Member
Joined
Apr 18, 2007
Messages
422
Well, while that's not the answer I was hoping for, it's definitely worth reading! :biggrin:

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

Active Member
Joined
Nov 21, 2006
Messages
483
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
 

Forum statistics

Threads
1,181,102
Messages
5,928,063
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top