Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel
Previous Topic Next Topic

Convert Worksheet_change event to macro

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 -
Hello,

How can I convert WorkSheet_change event becomes a macro. Please see code below. thanks.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("K1:K150")) Is Nothing Then
With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False
With .Offset(0, 1)
.Value = .Value + .Offset(0, -1).Value
.Offset(0, -1).Clear
End With
Application.EnableEvents = True
End If
End With
End If
End Sub



3 replies

Answer 1 / 3

hi people i am on myspace
Answer 2 / 3
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
depends on how you forsee "Target" is handled

This one way where, target is still handled in same manner 

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Call MyMacro(Target)
    
End Sub

Sub MyMacro(ByVal Target As Range)

    If Not Intersect(Target, Range("K1:K150")) Is Nothing Then
        With Target
        If IsNumeric(.Value) Then
            Application.EnableEvents = False
            
            With .Offset(0, 1)
                .Value = .Value + .Offset(0, -1).Value
                .Offset(0, -1).Clear
            End With
            
            Application.EnableEvents = True
            
            End If
        End With
    End If
    
End Sub
Answer 3 / 3

rizvisa1
Thanks for the quick response. how can i call the macro manually (Alt +F8), select MyMacro and hit Run.
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
Correct, unless that macro that you are trying to run needs some parameters. In that case you cannot directly run it. Like

Sub MyMacro(ByVal Target As Range)
you cannot run this macro directly as it needs parameters

Sub MyMacro()
you can run this one as it does not need any thing
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!