macro with button...

Posted by Stacy on November 24, 2001 10:58 AM

Can anyone PLEASE help me? I want to create a button on my toolbar that will insert the rounding formula into an existing formula in a cell or cells that I highlight. Can this be done by macros and then assigning the macro to a button? I can't just format the cells to show no decimal places, it won't foot...

For example:

cell A3 contains... =55.03*29.645

I want to be able to highlight cell A3, click the button, and the result in A3 would then be:
=round(55.03*29.645,0)
I also want to be able to create a 2nd button that would result with:
=round(55.03*29.645,2)

Posted by Bariloche on November 24, 2001 1:04 PM

Stacy, this should work:

Sub MultiRound()

Dim RoundTo As Single
Dim CellContents As String

RoundTo = InputBox("Round to how many decimal places?")

For Each cell In Selection

CellContents = Right(cell.FormulaR1C1, Len(cell.FormulaR1C1) - 1)
cell.FormulaR1C1 = "=Round(" & CellContents & ", " & RoundTo & ")"
CellContents = ""

Next cell

End Sub

With this code you will only need one button and the number of decimal places can be whatever you like. If you're set on having two toolbar buttons then just set "RoundTo" equal to 0 in one macro and 2 in another. But I think this will be a little more useful. This could be spiffed up a bit by the addition of some input verification and error handling code but as long as you're the only one using it and you know under what conditions to use it it should be fine.

enjoy

Posted by Stacy on November 25, 2001 10:21 AM

THANKS SO MUCH!!! IT WORKED LIKE A CHARM...

RoundTo = InputBox("Round to how many decimal places?") For Each cell In Selection CellContents = Right(cell.FormulaR1C1, Len(cell.FormulaR1C1) - 1) cell.FormulaR1C1 = "=Round(" & CellContents & ", " & RoundTo & ")" CellContents = "" Next cell