how to run Worksheet_Change(ByVal Target As Range) with Command button

[hkbhansali]

i have a vb code as under

Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
  If Target = "Shifted" Or Target = "Expired" Then
    Application.EnableEvents = False
      nxtRow = Sheets("Cancel Name").Range("A" & Rows.Count).End(xlUp).Row + 1
       Target.EntireRow.Copy _
        Destination:=Sheets("Cancel Name").Range("A" & nxtRow)
       Target.EntireRow.Delete
  End If
End If
Application.EnableEvents = True
End Sub

I want run this macro when i click on command button
please help

 

[Hui]

Generally if your going to want to re-use the same code stick it in its own sub routine
Then call that as required

eg:

Sub Worksheet_Change(ByVal Target As Range)
  Call My_Macro
End Sub

Sub My_Macro()
  If Target.Column = 1 Then
  If Target = "Shifted" Or Target = "Expired" Then
  Application.EnableEvents = False
  nxtRow = Sheets("Cancel Name").Range("A" & Rows.Count).End(xlUp).Row + 1
  Target.EntireRow.Copy _
  Destination:=Sheets("Cancel Name").Range("A" & nxtRow)
  Target.EntireRow.Delete
  End If
  End If
  Application.EnableEvents = True
End Sub

Then call My_Macro as normal by attaching it to a Button or Shape etc

 

[hkbhansali]

Generally if your going to want to re-use the same code stick it in its own sub routine
Then call that as required

eg:

Sub Worksheet_Change(ByVal Target As Range)
  Call My_Macro
End Sub

Sub My_Macro()
  If Target.Column = 1 Then
  If Target = "Shifted" Or Target = "Expired" Then
  Application.EnableEvents = False
  nxtRow = Sheets("Cancel Name").Range("A" & Rows.Count).End(xlUp).Row + 1
  Target.EntireRow.Copy _
  Destination:=Sheets("Cancel Name").Range("A" & nxtRow)
  Target.EntireRow.Delete
  End If
  End If
  Application.EnableEvents = True
End Sub

Then call My_Macro as normal by attaching it to a Button or Shape etc

Hi, Hui,
Thanks for reply
i try your code,but its not working
when i input Target"Shifted" in Column "A" run time error 424 object required
come and click on debug my code first line highlighted yellow,
"if Target.column=1 then"
Please guide me..(i have upload test file here with)

 

[Hui]

You will need to pass the Target to the subroutine

 

[hkbhansali]

You will need to pass the Target to the subroutine

Sorry Sir, I don't know how to 'pass the target', please guide me, How?

 

[Hui]

In the Sheet 1 Code Module only have

Private Sub Worksheet_Change(ByVal Target As Range)
  Call My_Macro(Target)
End Sub

In a Code Module

Sub My_Macro(Optional ByVal Target As Range)
On Error GoTo 10
  Debug.Print Target.Column
  GoTo 20
10:
Set Target = Selection
20:
If Target.Column = 1 Then
  If Target = "Shifted" Or Target = "Expired" Then
  Application.EnableEvents = False
  nxtRow = Sheets("Cancel Name").Range("A" & Rows.Count).End(xlUp).Row + 1
  Target.EntireRow.Copy _
  Destination:=Sheets("Cancel Name").Range("A" & nxtRow)
  Target.EntireRow.Delete
  End If
 End If
 Application.EnableEvents = True
End Sub

or see attached:

There is a Button in B1 to run the code

 

[hkbhansali]

In the Sheet 1 Code Module only have

Private Sub Worksheet_Change(ByVal Target As Range)
  Call My_Macro(Target)
End Sub

In a Code Module

Sub My_Macro(Optional ByVal Target As Range)
On Error GoTo 10
  Debug.Print Target.Column
  GoTo 20
10:
Set Target = Selection
20:
If Target.Column = 1 Then
  If Target = "Shifted" Or Target = "Expired" Then
  Application.EnableEvents = False
  nxtRow = Sheets("Cancel Name").Range("A" & Rows.Count).End(xlUp).Row + 1
  Target.EntireRow.Copy _
  Destination:=Sheets("Cancel Name").Range("A" & nxtRow)
  Target.EntireRow.Delete
  End If
End If
Application.EnableEvents = True
End Sub

or see attached:

There is a Button in B1 to run the code

I have test your sheet but B1 Button not work..ie : I enter "Expired" in cell "A3" and enter tab then row automatically moves next sheet..i want when I enter "Expired" or "Shifted" in cell "A3" and Click on Button then row have to move..

 

[Hui]

Delete the code in the Sheet1 Code Module (3 lines)
or comment them out

'Private Sub Worksheet_Change(ByVal Target As Range)
'  Call My_Macro(Target)
'End Sub

The file must be saved locally and opened with macro's enambles

 

[hkbhansali]

Delete the code in the Sheet1 Code Module (3 lines)
or comment them out

'Private Sub Worksheet_Change(ByVal Target As Range)
'  Call My_Macro(Target)
'End Sub

The file must be saved locally and opened with macro's enambles

Thanks Hui,
Yes its work like what i want...thanks a lot ..superb.
Thanks again..
Regards