Excel Question

[Chris W]

I presume this is possible, but have no idea how to go about it...

I want to create a button on a spreadsheet that when clicked will clear the contents of certain cells.

Anyone know how to do this, or can at least point me in the right direction?

TIA

[The Jackal]

Originally Posted by Chris W

I presume this is possible, but have no idea how to go about it...

I want to create a button on a spreadsheet that when clicked will clear the contents of certain cells.

Anyone know how to do this, or can at least point me in the right direction?

TIA

g00gling for : excel macro new button

http://www.google.co.uk/search?q=exc...UTF-8&oe=UTF-8

do you want to finish the search yourself I dont fancy doing any vis basic .net

EDIT
This one likes like being your friend
http://articles.techrepublic.com.com...9-1060039.html

[Chris W]

Thanks,

I wasn't quite clear though... I would like a button inside the spreadsheet, as opposed to on the toolbar.

Also, just to complicate things, i'm using excel for mac- it needs to be compatible with mac and pc

[AntiSilence]

Originally Posted by Chris W

Thanks,

I wasn't quite clear though... I would like a button inside the spreadsheet, as opposed to on the toolbar.

Also, just to complicate things, i'm using excel for mac- it needs to be compatible with mac and pc

Can the Mac version use VBA? It can probably be done on the PC using VBA, but I'm not sure what code would be needed since I've never used Excel on the PC, nevermind on a Mac

[The Jackal]

Originally Posted by Chris W

Thanks,

I wasn't quite clear though... I would like a button inside the spreadsheet, as opposed to on the toolbar.

Also, just to complicate things, i'm using excel for mac- it needs to be compatible with mac and pc

Holy moly a mac user : Unfortunately I dont have a mac and have strayed away from macs : I'm gonna be as useful as g00gle on this one mate...

Best of luck finding a solution

[jswalker2]

Well i have just had a quick google and have found this

"VBA on the Mac is based on VB 5 (which is what Excel 97 on Windows was), whereas on the Windows side it is VB 6."

So as far as i can tell, the procedure would be exactly the same for both pc and mac, and they would be compatible between both systems.

To draw a button on the spreadsheet you will need to get the Forms toolbar up, and then select button and drag on out on the sheet. To attach a macro to it, right click on the button and click Assign Macro and then choose the macro you wish to use.

For clearing cells in vb, you can either record a macro, do what you need to do, and then stop the recording and it will write the code for you, or the vb code for it would be

Range("A1").Select
Selection.ClearContents

But you can choose whatever cells you need to, and repeat this as many times as needed until all of your cells have been cleared. To select a range of cells the code would be

Range("A1:B2").Select

The above code would select a group of four cells A1, B1, A2 and B2.

I hope that all helps, and i hope it works as i don't have a mac to test this on

[Chris W]

Thanks! Am off to test it now so fingers crossed!!!

---------- Post added at 17:26 ---------- Previous post was at 17:14 ----------

works a treat many thanks

[jswalker2]

No problem, glad i could help

[liamboyle06]

Can I hijack this thread for another Excel Question.

Using Excel 2003, I have a pivot table and am trying to work out percentages of a subtotal, but when I format the Pivot to show percentages, it shows percentages of the overall total, is there anyway I can change it to show percentages of the subtotals instead.
Attached is the info I am working on to have.

[liamboyle06]

pleaes help, still trying to figure it out

[gazzae]

I had a quick look but couldn't figure out what you wanted to achieve.

Maybe you could upload it again and highlight the cells that you are having problems what and put in the figure you want it to calculate to beside them.

[liamboyle06]

Originally Posted by gazzae

I had a quick look but couldn't figure out what you wanted to achieve.

Maybe you could upload it again and highlight the cells that you are having problems what and put in the figure you want it to calculate to beside them.

Hope this helps a bit more, thanks for looking though.

[gazzae]

Right click on the column - Field Settings - Options - Show data as "% of total". Is that any good?

---------- Post added at 15:09 ---------- Previous post was at 15:04 ----------

Actually pull your closure reason up onto the top row and then do the same but select % by column