Drop down list disapear

[broogle]

I use drop down list in my data validation. The problem is, sometimes,
after I run the VBA code, the drop down list is missing. Any idea why
this could happen ? I have to exit from excel application and open it
again to get drop down list work as normal.
Thanks

 

[Mike Fogleman]

Perhaps if you provide the code we can figure out what is happening.

Mike F

 

[broogle]

Hi Mike,

This is the event change code that I'm using. It didn't happen all the
time, just occasionally.
This code just prevent the user to delete certain cells (but these
cells are not the drop down list cells). Thanks

'Checking C7 - C12
On Error GoTo errHandler:
Application.AutoPercentEntry = True
Dim myData As Range
Set myData = Range("c7, c8, c9, c10, c11, c12, c18, d18")

If Target.Cells <> "" Then GoTo Table_A
If Intersect(myData, Target) Is Nothing Then Exit Sub

If Target.value = "" Then
MsgBox "Please fix: " & Target.Address & vbLf & _
"It can not be blank !"
With Application
.EnableEvents = False
.Undo
End With
End If

Table_A:

If Target.value <= 0 Then
Exit Sub
End If

 

[Mike Fogleman]

This seems to be part of a procedure but I think the problem lies with:

With Application
.EnableEvents = False
.Undo
End With

I don't see where EnableEvents becomes True again but mostly the .Undo will
occur every time an empty target cell is activated including anything that
was done to a non-target cell. Maybe it just Undid your list. If this is
meant to Undo a delete, then you need to trap for the fact that a target
cell was deleted and then do the .Undo, not just because the cell was empty.
Help with that code needs to come from someone better than me.

Mike F