| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Cannot Delete Row with drop down box change
Hi all,
I am trying to use a simple VBA program to delete a row when the value of a cell changed. A drop-down list is provided to the cell. The function was working, but after some changes now it isn't. No error message is returned. However, when i tried to change the value by typing, it works. Also I have tried to create another dummy list for testing, and it also doesn't work. The delete function just gives no response. Could anyone advise what change would do something like this? Thank you. Jensan |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Cannot Delete Row with drop down box change
I assume that you are using a Worksheet Change event. This does not work when
something is pasted to a cell and I assume that changes created by a dropdown is the same as pasting. Regards, OssieMac "Jensan" wrote: Hi all, I am trying to use a simple VBA program to delete a row when the value of a cell changed. A drop-down list is provided to the cell. The function was working, but after some changes now it isn't. No error message is returned. However, when i tried to change the value by typing, it works. Also I have tried to create another dummy list for testing, and it also doesn't work. The delete function just gives no response. Could anyone advise what change would do something like this? Thank you. Jensan |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Cannot Delete Row with drop down box change
Posting code is more likely to elicit help.
There are a hundred and one ways it might be broken - no point in us all wildly guessing... Tim "Jensan" wrote in message ... Hi all, I am trying to use a simple VBA program to delete a row when the value of a cell changed. A drop-down list is provided to the cell. The function was working, but after some changes now it isn't. No error message is returned. However, when i tried to change the value by typing, it works. Also I have tried to create another dummy list for testing, and it also doesn't work. The delete function just gives no response. Could anyone advise what change would do something like this? Thank you. Jensan |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Cannot Delete Row with drop down box change
You sure that the worksheet_change event doesn't fire when you paste into a
cell? OssieMac wrote: I assume that you are using a Worksheet Change event. This does not work when something is pasted to a cell and I assume that changes created by a dropdown is the same as pasting. Regards, OssieMac "Jensan" wrote: Hi all, I am trying to use a simple VBA program to delete a row when the value of a cell changed. A drop-down list is provided to the cell. The function was working, but after some changes now it isn't. No error message is returned. However, when i tried to change the value by typing, it works. Also I have tried to create another dummy list for testing, and it also doesn't work. The delete function just gives no response. Could anyone advise what change would do something like this? Thank you. Jensan -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Cannot Delete Row with drop down box change
What version of excel is causing the trouble. In xl97, the _change event may
not be firing based on a change from a data|validation dropdown. From Debra Dalgleish's site: http://contextures.com/xlDataVal08.html#Change If you turned off events, maybe you didn't re-enable them??? And did you use data|validation to create that dropdown--or a combobox from the control toolbox toolbar or a dropdown from the Forms toolbar. Jensan wrote: Hi all, I am trying to use a simple VBA program to delete a row when the value of a cell changed. A drop-down list is provided to the cell. The function was working, but after some changes now it isn't. No error message is returned. However, when i tried to change the value by typing, it works. Also I have tried to create another dummy list for testing, and it also doesn't work. The delete function just gives no response. Could anyone advise what change would do something like this? Thank you. Jensan -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Cannot Delete Row with drop down box change
Thanks Dave. Now that you have raised it I have realized my mistake. Confused
myself with the OnEntry method of identifying entries to auto run a procedure which doesn't fire if data is pasted. Regards, OssieMac "Dave Peterson" wrote: You sure that the worksheet_change event doesn't fire when you paste into a cell? OssieMac wrote: I assume that you are using a Worksheet Change event. This does not work when something is pasted to a cell and I assume that changes created by a dropdown is the same as pasting. Regards, OssieMac "Jensan" wrote: Hi all, I am trying to use a simple VBA program to delete a row when the value of a cell changed. A drop-down list is provided to the cell. The function was working, but after some changes now it isn't. No error message is returned. However, when i tried to change the value by typing, it works. Also I have tried to create another dummy list for testing, and it also doesn't work. The delete function just gives no response. Could anyone advise what change would do something like this? Thank you. Jensan -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Cannot Delete Row with drop down box change
I'm surprised that you remembered it, much less its problems <vbg.
I haven't used that since we got rid of xl95. OssieMac wrote: Thanks Dave. Now that you have raised it I have realized my mistake. Confused myself with the OnEntry method of identifying entries to auto run a procedure which doesn't fire if data is pasted. Regards, OssieMac "Dave Peterson" wrote: You sure that the worksheet_change event doesn't fire when you paste into a cell? OssieMac wrote: I assume that you are using a Worksheet Change event. This does not work when something is pasted to a cell and I assume that changes created by a dropdown is the same as pasting. Regards, OssieMac "Jensan" wrote: Hi all, I am trying to use a simple VBA program to delete a row when the value of a cell changed. A drop-down list is provided to the cell. The function was working, but after some changes now it isn't. No error message is returned. However, when i tried to change the value by typing, it works. Also I have tried to create another dummy list for testing, and it also doesn't work. The delete function just gives no response. Could anyone advise what change would do something like this? Thank you. Jensan -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Cannot Delete Row with drop down box change
Hi guys,
I had fixed this problem by moving the whole thing to a new workbook. However, I faced the problem again this time, and moving to a new workbook does not work anymore. I have tried this code: Private Sub Worksheet_Change(ByVal Target As Range) If (Target.Address = "$A$1") Then On Error GoTo catchExp1 ThisWorkbook.ActiveSheet.Cells(2, 1) = "test" catchExp1: MsgBox Err.Description End If End Sub That's it.........I have tried to catch the error description and it says "Application-defined or Object-defined Error". Last time I said there is no error returned because I didn't use the "On Error Goto xxx".... Same as last time, if I type, it works. Thank you....... Jensan On Jun 3, 8:26 am, Dave Peterson wrote: I'm surprised that you remembered it, much less its problems <vbg. I haven't used that since we got rid of xl95. OssieMac wrote: Thanks Dave. Now that you have raised it I have realized my mistake. Confused myself with the OnEntry method of identifying entries to auto run a procedure which doesn't fire if data is pasted. Regards, OssieMac "Dave Peterson" wrote: You sure that the worksheet_change event doesn't fire when you paste into a cell? OssieMac wrote: I assume that you are using a Worksheet Change event. This does not work when something is pasted to a cell and I assume that changes created by a dropdown is the same as pasting. Regards, OssieMac "Jensan" wrote: Hi all, I am trying to use a simple VBA program to delete a row when the value of a cell changed. A drop-down list is provided to the cell. The function was working, but after some changes now it isn't. No error message is returned. However, when i tried to change the value by typing, it works. Also I have tried to create another dummy list for testing, and it also doesn't work. The delete function just gives no response. Could anyone advise what change would do something like this? Thank you. Jensan -- Dave Peterson -- Dave Peterson- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Cannot Delete Row with drop down box change
ps.
The Me. in this line: Me.Cells(2, 1).Value = "test" refers to the thing that owns the code. In this case, it's the worksheet that's getting the change. The enableevents stuff tells excel to stop looking for changes--so your code doesn't cause the event to fire again. Dave Peterson wrote: Maybe... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then On Error GoTo catchExp1: Application.EnableEvents = False Me.Cells(2, 1).Value = "test" End If ExitNow: Application.EnableEvents = True Exit Sub catchExp1: MsgBox Err.Description Resume ExitNow: End Sub wrote: Hi guys, I had fixed this problem by moving the whole thing to a new workbook. However, I faced the problem again this time, and moving to a new workbook does not work anymore. I have tried this code: Private Sub Worksheet_Change(ByVal Target As Range) If (Target.Address = "$A$1") Then On Error GoTo catchExp1 ThisWorkbook.ActiveSheet.Cells(2, 1) = "test" catchExp1: MsgBox Err.Description End If End Sub That's it.........I have tried to catch the error description and it says "Application-defined or Object-defined Error". Last time I said there is no error returned because I didn't use the "On Error Goto xxx".... Same as last time, if I type, it works. Thank you....... Jensan On Jun 3, 8:26 am, Dave Peterson wrote: I'm surprised that you remembered it, much less its problems <vbg. I haven't used that since we got rid of xl95. OssieMac wrote: Thanks Dave. Now that you have raised it I have realized my mistake. Confused myself with the OnEntry method of identifying entries to auto run a procedure which doesn't fire if data is pasted. Regards, OssieMac "Dave Peterson" wrote: You sure that the worksheet_change event doesn't fire when you paste into a cell? OssieMac wrote: I assume that you are using a Worksheet Change event. This does not work when something is pasted to a cell and I assume that changes created by a dropdown is the same as pasting. Regards, OssieMac "Jensan" wrote: Hi all, I am trying to use a simple VBA program to delete a row when the value of a cell changed. A drop-down list is provided to the cell. The function was working, but after some changes now it isn't. No error message is returned. However, when i tried to change the value by typing, it works. Also I have tried to create another dummy list for testing, and it also doesn't work. The delete function just gives no response. Could anyone advise what change would do something like this? Thank you. Jensan -- Dave Peterson -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA - Cannot Delete Row with drop down box change
Hi Dave,
Thank you very much. It seems to be a solution. I haven't tried it, because I have tried on every field, and discovered that when I use the function VLOOKUP at the same time with a custom function EOMonth, it would cause the problem. The EOMonth function has nothing special....search on the web and you will get one. Simply return different values in different cases. Now I have replace the formula with a value directly...it works. Therefore, I believe that your explanation should be correct. Thanks again. Jensan On Jul 23, 7:41 pm, Dave Peterson wrote: ps. The Me. in this line: Me.Cells(2, 1).Value = "test" refers to the thing that owns the code. In this case, it's the worksheet that's getting the change. The enableevents stuff tells excel to stop looking for changes--so your code doesn't cause the event to fire again. Dave Peterson wrote: Maybe... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then On Error GoTo catchExp1: Application.EnableEvents = False Me.Cells(2, 1).Value = "test" End If ExitNow: Application.EnableEvents = True Exit Sub catchExp1: MsgBox Err.Description Resume ExitNow: End Sub wrote: Hi guys, I had fixed this problem by moving the whole thing to a new workbook. However, I faced the problem again this time, and moving to a new workbook does not work anymore. I have tried this code: Private Sub Worksheet_Change(ByVal Target As Range) If (Target.Address = "$A$1") Then On Error GoTo catchExp1 ThisWorkbook.ActiveSheet.Cells(2, 1) = "test" catchExp1: MsgBox Err.Description End If End Sub That's it.........I have tried to catch the error description and it says "Application-defined or Object-defined Error". Last time I said there is no error returned because I didn't use the "On Error Goto xxx".... Same as last time, if I type, it works. Thank you....... Jensan On Jun 3, 8:26 am, Dave Peterson wrote: I'm surprised that you remembered it, much less its problems <vbg. I haven't used that since we got rid of xl95. OssieMac wrote: Thanks Dave. Now that you have raised it I have realized my mistake. Confused myself with the OnEntry method of identifying entries to auto run a procedure which doesn't fire if data is pasted. Regards, OssieMac "Dave Peterson" wrote: You sure that the worksheet_change event doesn't fire when you paste into a cell? OssieMac wrote: I assume that you are using a Worksheet Change event. This does not work when something is pasted to a cell and I assume that changes created by a dropdown is the same as pasting. Regards, OssieMac "Jensan" wrote: Hi all, I am trying to use a simple VBA program todeletea row when the value of a cell changed. Adrop-downlist is provided to the cell. The function was working, but after some changes now it isn't. No error message is returned. However, when i tried to change the value by typing, it works. Also I have tried to create another dummy list for testing, and it also doesn't work. Thedeletefunction just gives no response. Could anyone advise what change would do something like this? Thank you. Jensan -- Dave Peterson -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson -- Dave Peterson- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Delete drop down list | Excel Discussion (Misc queries) | |||
| Drop-down Delete Multiples | Excel Discussion (Misc queries) | |||
| Delete a drop down box | Excel Discussion (Misc queries) | |||
| How do i delete drop box | Excel Discussion (Misc queries) | |||
| change info in other cells when i change a number in a drop list? | Excel Discussion (Misc queries) | |||
Linear Mode
