Cell Data Change to Trigger Macro

D

documike

Is there a way to trigger a macro whenever data changes in a cell? The
value in the cell will be varying numbers, so it can't be TRUE/FALSE etc.
Thanks
 
E

Earl Kiosterud

Mike,

Yes. Put it in the sheet module, instead of a regular module. It should
look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
MsgBox "You changed cell A1, you did."
End If

If Not Intersect(Target, Range("A1").EntireColumn) Is Nothing Then
MsgBox "You changed something in column A. You sure did."
End If
End Sub
 
D

documike

Sorry, my macro & VB skills are not the best...I know what I want to do but
don't know exact syntax.

I have cell E26 on Sheet 1 that's set to show percentage. The end user is
supposed to enter whatever percentage they want. When they make a change
(I'm assuming Worksheet_Change) I want it to trigger my macro
FilteredDataCopy. I don't need the message box but it's something I can
incorporate also...thanks for the tip.
 
E

Earl Kiosterud

Mike,

The message box was just to illustrate code that runs when the event is
triggered. Paste this into the sheet module for the sheet. To open the
sheet module, double click it in the Project Explorer (View - Project
Explorer).

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E26")) Is Nothing Then
FilteredDataCopy
End If
End Sub

It gets run whenever any cell is changed on that sheet. It checks to see if
E26 was the cell that was changed, and calls your sub. Or you can put the
code of FilteredDataCopy right in place of the call to it, and let it run in
Worksheet_Change.
 
D

documike

Earl, thanks so much...everything is working Ok except for one thing that
has me puzzled

E26 is set as as the format as %. When I use the macro, the percent changes
X100...for instance if the cell was initially 3% , after running the macro,
it changes to 300% and whatever number I enter into E26, it's X100. Is
there something in the Target,Range that needs to be set differently?

It's so close to being finished...it's frustrating...thanks so much for your
assistance.
 
P

Paulw2k

Mike,

Are you sure E26-cell is formatted as a percentage, because the undlying
value of 3% would be 0.03; and the
code line Range("E26").value would be assigned that value.

Check through your FilteredDataCopy-procedure to see what it is doing with
that value.


Regards

Paul
 
E

Earl Kiosterud

Mike,

That's normal. When a cell is formatted for %, the underlying value is 0-1
for 0%-100%. You must type the % with each entry into the cell for it to
behave properly, or 3 goes in as 3, not 0.3, so it winds up 300%. Go
figure.

If you want to not have to type the %, try formatting the cell (Format -
Cells - Number - Custom) with something like

General"%"
0"%"

Now in your calculations, divide the cell by 100 to get the equivalent
multiplier.
 
E

Earl Kiosterud

Mike,

I forgot to mention. If my presumption that you're typing 3, and getting
300% (normal, if you don't include the % symbol), I have a couple of
additional comments. It's not likely the macro that's changing it. The
macro runs immediately, via the Worksheet_Change, and probably just appears
to be the culprit. But it's just the way % works in Excel, macro or no
macro, as I said in the prior post.

I've had success with some code in (guess what) the Worksheet_Change proc
that adjusts for the X100 so the user doesn't have to type the %. Let me
know if you're interested.
 
D

documike

I found the error issue. In my original worksheet that worked fine I had
cell format set to Custom with the value being 0.0%. I must of adjusted the
referenced values by dividing by 100. Looks like it was something I did
quite a while ago and forgot I did it! Old age I guess. Thanks for all the
help. Everything is working wonderful now.
 
Top