Excel VBA : Changing button click event into cell value change event

Question

I created a simple value sum example driven by button click event as below.

Sub buttonEvent()

  Dim a As Double
  a = Application.WorksheetFunction.Sum(Range("A1:A10"))
  Cells(11, 1).Value2 = a

End Sub

GUI is like below.

When I click sum button, this macro sums values in defined range.

I would like to change the click event as range value change event.

It means, as soon as I change a value in the range, value sum function will be performed.

How can I do that?

Harun24hr · Accepted Answer · 2016-02-13 19:19:01Z

1

Use these codes.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim a As Double
    If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
         a = Application.WorksheetFunction.Sum(Range("A1:A10"))
             Cells(11, 1).Value2 = a
    End If
End Sub

Edited after comment
For your clarification:

edited Feb 13, 2016 at 19:19

answered Feb 13, 2016 at 19:02

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

KKS Over a year ago

I have a question. Must I change the macro name as Worksheet_Change?

Harun24hr Over a year ago

No. You have to use code in Worksheet_Change event. It is not a macro name. It is built in sheet event.

KKS Over a year ago

I am afraid I am a beginner of VBA. How could I make the Worksheet_Change event?

Harun24hr Over a year ago

See the screenshot. I hope it will clear answer now to you. Let me know if you still need clarification.

Harun24hr Over a year ago

Double click on Sheet1 in left side pane. Select Worksheet from above dropdown. Then select Change from right dropdown. Now copy paste above codes.

|

Collectives™ on Stack Overflow

Excel VBA : Changing button click event into cell value change event

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related