PDA

View Full Version : auto update with cell value change



krishhi
01-06-2010, 07:45 PM
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
01-06-2010, 08:39 PM
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
01-07-2010, 01:42 AM
Here is an example



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


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
01-07-2010, 05:04 AM
Thank you very much XLD, I will check it out.

bdsii
01-07-2010, 07:25 AM
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
01-07-2010, 07:40 AM
Sure, something like



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

bdsii
01-07-2010, 07:42 AM
great, thanks !

bdsii
01-07-2010, 07:48 AM
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
01-07-2010, 11:16 AM
anyone ?

Bob Phillips
01-07-2010, 11:18 AM
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

bdsii
01-11-2010, 04:35 PM
Thanks xld, you are always a lot of help !! :-)

Ted
04-20-2010, 02:12 PM
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
04-20-2010, 03:48 PM
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
04-20-2010, 04:23 PM
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
04-21-2010, 01:53 AM
Try something along the lines o


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