auto update with cell value change

[krishhi]

hello,

How to write a macro to update the values without running the macro. i mean, if i change a cell, it will effects after i run the macro, but i will to change the values without running the macro.

Any help?

[rbrhodes]

Hi,

I think yoe want a Worksheet Change macro. Post an example of what you have/want and I'll show you how

[Bob Phillips]

Here is an example

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
' do your stuff
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[krishhi]

Thank you very much XLD, I will check it out.

[bdsii]

Learned something new today!

Quick Question xld.....if there are, say 5, non-continuous cells that you want to run the macro when they are updated, how would that be coded ?

Or would you need to copy the code above 5 times, one for each cell ?

thanks!

[Bob Phillips]

Sure, something like

[vba]

Const WS_RANGE As String = "H1,M3,T9,L5,A9" '<== change to suit
[/vba]

[bdsii]

great, thanks !

[bdsii]

OK, 'nuther question....if I want to run one macro if cell B5 is updated and ANOTHER macro if cell B19 is updated, what would that look like ?

i tried copying the above code and using a different name and assigning it as a Change but did not run. ??

[bdsii]

anyone ?

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range("B5")) Is Nothing Then
With Target
Call Macro1
End With
ElseIf Not Intersect(Target, Me.Range("B19")) Is Nothing Then
With Target
Call Macro2
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

[bdsii]

Thanks xld, you are always a lot of help !! :-)

[Ted]

What to modify in above code if the cell which has to trigger the macro is linked to another cell.

Details of the Problem.
Cell A2 is linked to C2 i.e. in Cell A2 the formula is "=C2"
Whenever I change C2, A2 is updated and that should trigger the macro.

I cannot make it simple by placing the trigger on C2 itself.

I am using above examples but isnt working in my particular case. Help appreciated.

[rbrhodes]

Hi Ted

Sorry but this doesn't quite logic out. If you change C2, A2 changes.


"I cannot make it simple by placing the trigger on C2 itself." Huh?

How does C2 get changed?

[Ted]

When I used the Event Sub "Worksheet_Change", if A2 was changed through C2, the macro did not trigger. The above examples only work if A2 is changed itself but not through any other cell.

I used the following code to get my work done, offcourse with the help of experts here.

Private Sub Worksheet_Calculate()
If Range("A2").Value <> 0 Then
' do your stuff
End If
End Sub

[Bob Phillips]

Try something along the lines o

[vba]
If Not Intersect(Target, Me.Range("A:C")) Is Nothing Then

For Each cell In Target

'do stuff with cell not Target
Next cell
End If
[/vba]