Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
drop down deletion errors
I am currently running a macro to allow me to select more than one item from
a drop down list, however after all the selections are made, if I try to go in to the cell and erase one, it displays an error and will not let me do so. I suspect it has something to do with the fact that the cell is formatted as a list in the data validation tab, but I am not sure how to work around that. Here is the code I am currently running, in case it helps. Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String If Target.Count 1 Then GoTo exitHandler On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then GoTo exitHandler If Intersect(Target, rngDV) Is Nothing Then 'do nothing Else Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If Target.Column = 5 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ", " & newVal End If End If End If End If exitHandler: Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
drop down deletion errors
Sound like the behavior of an array. Maybe you should consider an
alternative. Take a look at this: http://www.dailydoseofexcel.com/arch...ect-listboxes/ HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Tech_Wolf" wrote: I am currently running a macro to allow me to select more than one item from a drop down list, however after all the selections are made, if I try to go in to the cell and erase one, it displays an error and will not let me do so. I suspect it has something to do with the fact that the cell is formatted as a list in the data validation tab, but I am not sure how to work around that. Here is the code I am currently running, in case it helps. Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String If Target.Count 1 Then GoTo exitHandler On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then GoTo exitHandler If Intersect(Target, rngDV) Is Nothing Then 'do nothing Else Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If Target.Column = 5 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ", " & newVal End If End If End If End If exitHandler: Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent function errors caused by cutting & paste or drag & drop | Excel Worksheet Functions | |||
Drop down errors | Excel Programming | |||
Excel Throwing Circular Errors When No Errors Exist | Excel Worksheet Functions | |||
How do I generate Errors for Drop-Down Lists? | Excel Discussion (Misc queries) | |||
Unresolved Errors in IF Statements - Errors do not show in results | Excel Worksheet Functions |