help with bug in vb code...

[xluser26]

Hi Everyone,

Im using the following code to insert multiple data in a single cell.
For example:

I choose a from a drop-down list (data validation cell) and it inserts the clicked item in the same data validation cell.

'------------------ Data Validation add in same cell ---------------------
 
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 = 16 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

The problem im having is:

Say I have multiple items already inserted in the cell(P1), if for some reason I click the drop down arrow or activate the cell(P1) and then I click a different portion of the sheet, it takes the old value inside the cell(P1) and duplicates it, in the cell(P1) again.

So say I have:

John, Tom, Rick

it ends up with

John, Tom, Rich, John, Tom, Rick

I guess it needs a way to exit the handler (or stop the code) if it finds itself clicking away from the source cell (P1).

Hope it makes sense.
Thank you and

xluser26

 

[xluser26]

Hello,

Question what would be the best place in this code to enter another Exit Handler Statement so the code won't continue running if I activate the 'copy old value' to memory, and then unwantedly re-insert it when I click a different cell?

Much Thanks,

xluser26

 

[shg]

I don't have the problem you describe using the code in your first post (it works fine), and don't understand the question in your second.

 

[xluser26]

thanks for the reply. When I double click a cell it activates it for edit mode. when i do that, then its when it stores the current value in the cell and gets ready to re-insert it in the cell. my problem is if in accident I double click (activate) the cell and then click away from that cell instead of choosing to do nothing if I dont chose an item from the drop-down list, it inserts whats stored in memory (oldvalue) for activating the cell and adds it to the cell.

note: it happens more when the cell has data with 1 or more items.

maybe its a bit more clear. thank, xluser26

well it does it every time i accidently double click the cell then go to another cell, I end up duplicating and making that cell really big.

 

[shg]

Double-clicking is different.

You could try putting code in the double-click event and cancel if it's in one of those cells.

 

[xluser26]

that makes alot of sense. do you have a quick example, if not its okay, im looking as we speak. im not a coder but it should be easy I believe.

hey thank you very much,

xluser26

 

[shg]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim i           As Long
 
    On Error Resume Next
    i = Target.Validation.Type
    Cancel = Err.Number = 0
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i           As Long
    Dim sOld        As String
    Dim sNew        As String
 
    With Target
        If .Count > 1 Then Exit Sub
 
        On Error Resume Next
        i = .Validation.Type
        If Err.Number Then Exit Sub    ' no validation
 
        If .Column = 16 Then
            On Error GoTo Oops
            Application.EnableEvents = False
            sNew = .Value
            Application.Undo
            sOld = .Value
            .Value = sNew
            If Len(sOld) > 0 And Len(sNew) > 0 Then .Value = sOld & ", " & sNew
        End If
    End With
 
Oops:
    Application.EnableEvents = True
End Sub

 

[xluser26]

The code works great, when it comes to double clicking the cell, im definetly going to stick to this code instead. I just over looked that if you activate the formula bar, it does the same thing. Is there a way to disactivate the formula bar for that specific cell or column?

Well it works great, it reduces all the times I double clicked on the sheet.

Have a Great New Years!

xluser26