Previous Topic Next Topic

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



Related:

3 replies

Answer 1 / 3
hawa
Aug 22, 2010 at 07:23 PM
hi people i am on myspace
0
Answer 2 / 3
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
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
Answer 3 / 3
dzrdw0
Aug 23, 2010 at 11:32 PM
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 767
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