Combining 5 Macro Buttons into one

[Ted Dennis]

I currently have 5 buttons all with a Macro attached on a spreadsheet. I want to combine these into one button. This will make I a lot easy for the End User.

Can I do this by just taking the 5 parts of vba and creating one piece of code.

Sorry if this doesn't make sense new to VBA

Thanks

Ted

[mikeTRON]

Yes you can.

[HerryMarkowitz]

You can use;
Call method
or
Application.Run method.
Check following link;
http://www.excelforum.com/excel-prog...acro-name.html

[buran]

also, you can create another sub, that calls that calls the first 5 one by one e.g.pseudo code:

Please Login or Register  to view this content.

[Ted Dennis]

I have put the 5 pieces of code together, as below is this correct

Please Login or Register  to view this content.

[Ted Dennis]

Sorry, but ive just been thinking this through I suppose given that all of the parts are specific to the active cell that is being selected I wont be able just select one cell or all of the cells as it just wont work. Could this issue be overcome

Thanks again

Ted

[HerryMarkowitz]

Maybe...

Please Login or Register  to view this content.

[buran]

first of all, do you understand that first 3 macros will overwrite each other (either if it is the active cell or some other selection)...

4 macro is actually doing what .Value = .Value has already done - converting formulas to values

Finally the 5th macro will overwrite the first three...
so the result of running these 5 macros one after another will be exactly the same as running just the last (5th) one.


The idea to have them on different buttons is to insert different formula in different cells...
I assume you would like to have them work on different cells actually, not the same selection

@ Harry - It's bad coding practice to chain macros like this.
If you want to run them one after another - just create separate macro that will run the other 5. However look back to start of my comment

[Ted Dennis]

Buran, I realised exactly what you've said see my comment above. The process the users is doing is basically working across a spreadsheet and at this point each time they select a cell they would click on the appropriate button, but each button specifies the active cell is there anyway round doing this without having to select each cell as each action is basically looking up another part of the spreadsheet relative to the cell.

Thanks again

[buran]

given that all of the parts are specific to the active cell that is being selected I wont be able just select one cell or all of the cells as it just wont work

Sorry for misunderstanding. I decided that you want to run it for the selection, not just the active cell (i.e. it's possible to have selected 10 cells, but active cell is always a single one within the selection).
can you specify how the cells are related, e.g. if user select one cell (or more) then run macro 1, second macro runs on cell on the same row and 3 columns to the right, etc....?

[Ted Dennis]

Hopefully this should make sense

Button 1 - Is a vlookup bringing back a date from another worksheet that's why the this has to be an active cell as it s looking for a name that exists in this spreadsheet.
Button 2 - is the same as above but is bring back a date
Button 3 - as above
Random Number is as it says generating a random number
Button 4 - Is an If which decides an outcome dependant on the various details pulled through by previous buttons.

As far as I understand (limited knowledge on the vba front) I need to have active cell as this is a lookup

Any help would be great

Thanks again

Ted

[buran]

I see what each macro is doing. however if you apply them on same cell one after another, final result will be the same as applying only macro 5. And you said you realised that too. So it makes sense to apply 5 macros with one click, but each macro on different target cell. my question was how you decide on each cell, relative to the first one. In other words if you run macro 1 on cell a1, which cell should be used for macro 2, etc.

[Ted Dennis]

I see what each macro is doing. however if you apply them on same cell one after another, final result will be the same as applying only macro 5. And you said you realised that too. So it makes sense to apply 5 macros with one click, but each macro on different target cell. my question was how you decide on each cell, relative to the first one. In other words if you run macro 1 on cell a1, which cell should be used for macro 2, etc.

Macro 1 on A1, 2 on A2, 3 on a3 etc

Hope that clarifies

Thanks

[mikeTRON]

What exactly are you trying to accomplish?

It would make most sense to post a sample workbook to the forum.