sanketgroup
New Member
- Joined
- Feb 4, 2010
- Messages
- 13
Hello
i am using following code on Worksheet_Change event.
In this Worksheet_Change code, i have used Application.OnKey and there are other procedures in this Worksheet_Change.
when Application.OnKey occurs, it run the macro as said, but after completion of all those procedures in macro, it does not come back to original Worksheet_Change functions.
pls help me to solve this.
Following is code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
ActiveSheet.Unprotect Password:="password"
'on delete key, run macro4
Application.OnKey "{DELETE}", "Macro4"
' from above line, if user press delete, it successfully goes to "Macro4, but then it never comes back to following procedures in this sub
With Target
If .Column <> 1 Or .Cells.Count > 1 Then Exit Sub
If WorksheetFunction.CountIf(Columns(.Column), .Value) > 1 Then
'Application.DisplayAlerts = False
.ClearContents
Application.DisplayAlerts = True
MsgBox "Varient already exists!"
End If
End With
End If
End Sub
============macro4 code============
Sub Macro4()
If ActiveCell.Column <> 1 Then Exit Sub
If Selection.Count = 1 Then
ActiveSheet.Unprotect Password:="password"
Range("b" & ActiveCell.Row & ":M" & ActiveCell.Row & "").Select
Selection.ClearContents
Range("a" & ActiveCell.Row & "").Select
Selection.ClearContents
ActiveSheet.Protect Password:="password"
Else
MsgBox ("Please select only 1 Varient")
End If
End Sub
'after completion of this subject process should go back to Private Sub Worksheet_Change to where it came to this process
==============================================
Also, is it possible to write entire procedure of macro4 directly into "Private Sub Worksheet_Change" on Application.OnKey "{DELETE}"
I read that it accepts only another function macro.
i am using following code on Worksheet_Change event.
In this Worksheet_Change code, i have used Application.OnKey and there are other procedures in this Worksheet_Change.
when Application.OnKey occurs, it run the macro as said, but after completion of all those procedures in macro, it does not come back to original Worksheet_Change functions.
pls help me to solve this.
Following is code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
ActiveSheet.Unprotect Password:="password"
'on delete key, run macro4
Application.OnKey "{DELETE}", "Macro4"
' from above line, if user press delete, it successfully goes to "Macro4, but then it never comes back to following procedures in this sub
With Target
If .Column <> 1 Or .Cells.Count > 1 Then Exit Sub
If WorksheetFunction.CountIf(Columns(.Column), .Value) > 1 Then
'Application.DisplayAlerts = False
.ClearContents
Application.DisplayAlerts = True
MsgBox "Varient already exists!"
End If
End With
End If
End Sub
============macro4 code============
Sub Macro4()
If ActiveCell.Column <> 1 Then Exit Sub
If Selection.Count = 1 Then
ActiveSheet.Unprotect Password:="password"
Range("b" & ActiveCell.Row & ":M" & ActiveCell.Row & "").Select
Selection.ClearContents
Range("a" & ActiveCell.Row & "").Select
Selection.ClearContents
ActiveSheet.Protect Password:="password"
Else
MsgBox ("Please select only 1 Varient")
End If
End Sub
'after completion of this subject process should go back to Private Sub Worksheet_Change to where it came to this process
==============================================
Also, is it possible to write entire procedure of macro4 directly into "Private Sub Worksheet_Change" on Application.OnKey "{DELETE}"
I read that it accepts only another function macro.