VBA - Cannot Delete Row with drop down box change

[Jensan]

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

 

[OssieMac]

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

 

[Tim]

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

 

[Dave Peterson]

You sure that the worksheet_change event doesn't fire when you paste into a
cell?

 

[Dave Peterson]

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.

 

[OssieMac]

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]

I'm surprised that you remembered it, much less its problems <vbg>.

I haven't used that since we got rid of xl95.

 

[jensanthegreat]

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

 

[Dave Peterson]

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

 

[Dave Peterson]

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.

 

[jensanthegreat]

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

 

[jensanthegreat]

I have tried to disable event but it seems doesn't help....

Now I have reformed the formula, to be less complicated, and it
works......
It could be the limitation of Excel......in my opinion.

 

[Dave Peterson]

Difficult to guess since you never shared the actual formula--or the code you
used.