Application.OnKey- does not go back to Private Sub Worksheet_Change

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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top