Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row base the value of certain column
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row base the value of certain column
Try this, wesbird: http://www.vbaexpress.com/kb/getarticle.php?kb_id=260
******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "wesbird" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable/Enable delete row base the value of certain column
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 "Anne Troy" wrote: Try this, wesbird: http://www.vbaexpress.com/kb/getarticle.php?kb_id=260 ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "wesbird" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row base the value of certain column
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 wesbird wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row base the value of certain column
Thank you very much. You just make me figure out where my problem is.
"Dave Peterson" wrote: 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 wesbird wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy column header to next column, delete & delete every nth colum | New Users to Excel | |||
Merging rows base on value in other column | Excel Discussion (Misc queries) | |||
Sort Data Base By Column | New Users to Excel | |||
select a range base on Column A | Excel Programming | |||
Row to column data move base on row | Excel Programming |