EXCEL MACRO 2007

[BobF]

I'm creating a spreadsheet for cost estimating and I have run into some
problems with the VBA code for macros to make the sheet do what I want.
I wrote yesterday for a macro that would raise the value of any given cell
by a certain percentage. I got the macro, and it worked, but if the control
button is pushed more than once the value continues to go up. (That makes
sense). Then I found that the "Undo" stack list is cleared when you run a
macro so this presents a problem. I need to be able to "Undo" if the control
button is pushed more than once. Also, if the cursor is inadvertantly in any
particular cell on the spreadsheet and the button is pushed the value will
increase by the predetermined %. I would like to restrict the range to a
certain group of cells only, and within that group I can pick which cell I
want to increase the value of. I went into the EXCEL registry to increase the
stack list number, but this still does not allow the "Undo" of a Macro.
Any advice?

 

[Don Guillett]

As always, post your macro for comments. No mind readers here.
You could put in a msgbox asking if you really want to increase and you can
restrict the range
Look at INTERSECT

 

[BobF]

This is what I was given from this site:

Public Sub ChangePercent()
Dim rng As Range

On Error Resume Next
For Each rng In Selection
rng.Value = rng.Value * 0.5
Next rng

End Sub

I'm not a programmer, so I don't really know how to proceed at all.

 

[Chip Pearson]

Try

Public Sub ChangePercent()
Dim rng As Range

On Error Resume Next
Application.EnableEvents = False
For Each rng In Selection
rng.Value = rng.Value * 0.5
Next rng
Application.EnableEvents = True
End Sub


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)