Update macro on start up

K

Khalil Handal

I have a macro with the following code
range("h5").value=application.sum(range("a2:f3"))
If a value in the range A2:F3 is cahnged I need to run the macro again so as
to change the value in cell H5.
Is there something like "automatic update"??? so that I don't need to run
the macro whenever a value is changed.
 
K

Khalil Handal

Hi,
The idea originaly is to have the value in cell H5 being there
without seeing the original formula and avoiding also to protect the sheets.
I hope that this made it clear why to use VBA Code in the first place
instead of just having the formla in H5 which is simpler.

Khalil
 
D

Dave Peterson

Right click on the worksheet tab that should have this behavior. Select view
code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'one cell at a time only!
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a2:f3")) Is Nothing Then Exit Sub

On Error GoTo ErrHandler:

Application.EnableEvents = False
Me.Range("h5").Value = Application.Sum(Me.Range("a2:f3"))

ErrHandler:
Application.EnableEvents = True

End Sub

Notice that if you use this instead of a simple formula, you'll see that
edit|undo is killed.
 
K

Khalil Handal

It worked fine. Thanks a lot.
May sheet containes more than one cell that has different formulas and needs
to have the same treatment.
Can this be done?? and How??
maybe 2 formulas and I can follow the same for the rest!

Khalil
 
D

Dave Peterson

It can be done by modifying the code--not duplicating the procedure.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'one cell at a time only!
If Target.Cells.Count > 1 Then Exit Sub

On Error GoTo ErrHandler:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("a2:f3")) Is Nothing Then
Me.Range("h5").Value = Application.Sum(Me.Range("a2:f3"))
ElseIf Not Intersect(Target, Me.Range("x3:y6")) Is Nothing Then
Me.Range("q5").Value = Application.Sum(Me.Range("x3:y6"))
End If

ErrHandler:
Application.EnableEvents = True

End Sub

Khalil said:
It worked fine. Thanks a lot.
May sheet containes more than one cell that has different formulas and needs
to have the same treatment.
Can this be done?? and How??
maybe 2 formulas and I can follow the same for the rest!

Khalil
Click to expand...
 
K

Khalil Handal

Hi,
It is clear for the sum formula's. in a case like this formula where no
range is there:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
that is in cell B5, what should be instead of the range part in:
ElseIf Not Intersect(Target, Me.Range("x3:y6")) Is Nothing Then
Click to expand...
and the second line (assuming the need of 2 line for each as i concluded )
Me.Range("q5").Value = Application.Sum(Me.Range("x3:y6"))
Click to expand...
will be:
Me.Range("B5").Value= "=Mid(CELL(""filename"", A1), Find(""]"",
CELL(""filename"", A1)) + 1, 255)"

I'am not sure if this is right!!!
I also understand that another elseIF statment should be added for each
value needed.
The "Range" part confuses me in the case were no Sum or Average is there!

Khalil
 
D

Dave Peterson

This formula doesn't depend on what's in A1. It uses A1 as a reference for what
sheet name to return.

And since this formula returns the name of the worksheet, you could use:

me.range("A1").value = me.name

Maybe you can use the worksheet_calculate event to do this -- but I wouldn't.
I'd use that formula.

In fact, I wouldn't replace the other formulas with code either -- but I've said
that already.

Khalil said:
Hi,
It is clear for the sum formula's. in a case like this formula where no
range is there:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
that is in cell B5, what should be instead of the range part in:
ElseIf Not Intersect(Target, Me.Range("x3:y6")) Is Nothing Then
Click to expand...
and the second line (assuming the need of 2 line for each as i concluded )
Me.Range("q5").Value = Application.Sum(Me.Range("x3:y6"))
Click to expand...
will be:
Me.Range("B5").Value= "=Mid(CELL(""filename"", A1), Find(""]"",
CELL(""filename"", A1)) + 1, 255)"

I'am not sure if this is right!!!
I also understand that another elseIF statment should be added for each
value needed.
The "Range" part confuses me in the case were no Sum or Average is there!

Khalil
Click to expand...
 
Top