Macro to pull all amounts into a message box or userform and ask me which ones to edit etc

[tonywatsonhelp]

Hi Everyone,
I have a list of direct debits,
sometimes I need to edit or adjust the amount i'm paying for one or more of them for that month
I need a macro that can ask me which ones I want to change and by how much and then add reduce the following months amount by that amount?

So here in detail what I need,

Below is an example of what I have

So here's what I need the macro to do,
I'm going to say messagebox but if its easier to do it with userform please just guide me through.
run macro
Macro looks along row 36 and in Columns AO,AQ,AS,AU,AW to see what ones have a values in them
(some might be minus values some will be blank, if it helps the first black cell out of them means theres not anymore after so if AS="" then AU & AW must also be blank However AV. AT or any other cell might not)
So Messagebox says "Which Value/Values would you like to change?)
Messagebox Shows List of all the values and has an input box next to it for me to add the new amounts
I then press OK
Macro changes all the cells I picked to the amounts I said,
Macro then adjust the values in Row 37 to show the changes I made so 36+37 still = the same
so if I pick AO36 and change it from 10000 to 5000 then the 5000 gets added to AO37
and so on.

I hope thats clear really stuck on this please help if you can thanks
Tony



[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]ROWS/COLUMNS[/TD]
[TD]AO[/TD]
[TD]AP[/TD]
[TD]AQ[/TD]
[TD]AR[/TD]
[TD]AS[/TD]
[TD]AT[/TD]
[TD]AU[/TD]
[TD]AV[/TD]
[TD]AW[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]10000[/TD]
[TD][/TD]
[TD]5000[/TD]
[TD][/TD]
[TD]2000[/TD]
[TD][/TD]
[TD]1000[/TD]
[TD][/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]10000[/TD]
[TD][/TD]
[TD]5000[/TD]
[TD][/TD]
[TD]2000[/TD]
[TD][/TD]
[TD]1100[/TD]
[TD][/TD]
[TD]550[/TD]
[/TR]
[TR]
[TD]Result if a chose AQ36 as row tow change and asked it to be 3000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD][/TD]
[TD][/TD]
[TD]3000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD][/TD]
[TD][/TD]
[TD]7000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sijpie]

Hi Tony,

to make it easier for you to differentiate which direct debit you are changing, is there a header row with names for the DD? Otherwise you will only see figures.

a. One option is to create a userform for you to do this, as in a messagebox you cannot have combobox or something similar.

b. An alternative would be that you click on a cell (say AQ36), which then immediately pops up a messagebox to make a change to that cell only (and the corresponding AQ37 cell)

What would be you preference? b. is very easy to code and for you to maintain. a. looks pretty and can be run from anywhere in the workbook, but takes more setup and coding.