Convert Worksheet_change event to macro

Closed
dzrdw0 - Aug 22, 2010 at 07:15 PM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Aug 24, 2010 at 02:58 AM
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

hi people i am on myspace
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Aug 23, 2010 at 09:22 AM
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
0
rizvisa1
Thanks for the quick response. how can i call the macro manually (Alt +F8), select MyMacro and hit Run.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Aug 24, 2010 at 02:58 AM
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
0