delete row base the value of certain column

[Guest]

Hi How are you?
My customer report this to me, he delete some row by mistake using hot key
Ctrl-D. he want me to put code to prevent it happening. What I shold do it,
check a certain column (e.g. column ZZ), if there are value, DO NOT delete
this row. cancel the deletion. if it is empty, it is fine to delete.
after some research, here is my plan,
1. assign Ctrl-D to my DoNothing routine,
2. in Worksheet_SelectionChange function, I will check if a value in
column ZZ, yes, I am not change hot key assignment by
Application.OnKey "^{d}", ""
no, I will assign Ctrl-D back to Delete row by
Application.OnKey "^{d}", "InsertProc"
Now my problem is, I do not how to do that. which command string should I
use in the place of "InsertProc". Your help will be deeply appreciated.
aboud my solution, if you have any idea, suggestion, advice, anything, I
would love to hear.



Thank you and have nice day.
Wes






code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target(1, 1).Value <> "" Then
Application.OnKey "^{d}", ""
MsgBox "disable"
Else
Application.OnKey "^{d}", "InsertProc" ' <- here is my question
MsgBox "enable"
End If
End Sub

Public Sub Donothing()
MsgBox "Here is my DoNothing"
End Sub

 

[Anne Troy]

Try this, wesbird: http://www.vbaexpress.com/kb/getarticle.php?kb_id=260
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

 

[Guest]

Thank you, Anne.
But this is not what I am looking for. This is my fault, I should put
"Disable/Enable delete row base the value of certain column" in the subect.
The trick part is, I have to deal with hot key, Ctrl+D instead of menu
command.
Any thought? Anyway, thank you very much


Wes

 

[Dave Peterson]

Do you mean ctrl-D or ctrl-d (uppercase D or lowercase d).

If you mean lowercase d, is this the excel shortcut that's used to duplicate the
top row of a multi-row selection (or copy the previous row of a single row
selection)?

Or is it a macro that has a shortcut key?

I'm gonna guess that it's excel's builtin shortcut.

And since I don't have a column ZZ in my version of excel, I'm gonna use column
I.

I tried to check the ranges to see if the code should continue. If yes, then do
excel's shortcut key. If not, then give a message.

Option Explicit
Sub auto_open()
Application.OnKey "^d", "myctrl_d"
End Sub
Sub auto_close()
Application.OnKey "^d"
End Sub
Sub myCtrl_d()

Dim myRng As Range
Dim myCell As Range
Dim myCol As String
Dim okToContinue As Boolean

myCol = "i"
With ActiveSheet
Set myRng = Intersect(Selection.EntireRow, .Columns(1))
okToContinue = True
If myRng.Cells.Count = 1 Then
'single row selected
If IsEmpty(.Cells(myRng.Row, myCol)) Then
'it's ok, do nothing special
Else
okToContinue = False
End If
Else
For Each myCell In myRng.Cells
If IsEmpty(.Cells(myCell.Row, myCol)) Then
'ok so far
Else
okToContinue = False
Exit For
End If
Next myCell
End If

If okToContinue = True Then
Application.OnKey "^d"
SendKeys "^d"
Application.OnKey "^d", "myctrl_d"
Else
Beep
MsgBox myCol & " Isn't empty for at least on cell!"
End If

End With

End Sub

 

[Guest]

Thank you very much. You just make me figure out where my problem is.