VBA code working in one workbook but not another

[Makr1]

Hi

I apologise if this has already been solved somewhere on this forum, however I am unable to find it.

I have two workbooks in which I am trying to use the same code. In one it is working but in the other it is not. I am using Excel 2007.

Here is the code:

Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
Dim lOld As Long
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 = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
lOld = Len(oldVal)
If Left(newVal, lOld) = oldVal Then
Target.Value = newVal
Else
lUsed = InStr(1, oldVal, newVal)
If lUsed > 0 Then
Target.Value = oldVal
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub


Thanks

 

[RCBricker]

In the first workbook (the one that works) where is the code found?

What about the second workbook?

This is a worksheet change event so it needs to be inserted into the worksheet VBE that will have the change that triggers the event.

Also please use code tags when posting code. Saves space on the page and is easier to read.

open code = [ code ] (no spaces)
close code = [ /code ] (no spaces).

 

[Makr1]

Thank you for your reply.

In both cases the code is in the object for the sheet required.

 

[RCBricker]

ok what is it that the code is supposed to do? I am looking at it and it is a bit confusing.

 

[Makr1]

The code is supposed to allow multiple options to be selected in a dropdown menu. If an option is selected that has already been selected then it will not appear again in the cell. The options that have been selected will appear in the cell separated by commas.

 

[RCBricker]

this might be a quick error fix. It is possible that the enable events has been set to false in the new workbook. open VBE and under the Thisworkbook insert the following:

Private Sub Workbook_Open()
Application.EnableEvents = True
End Sub

save close and reopen.

See if that fixes the issue.

 

[Makr1]

Thank you very much for the help. I have managed to get it working by using a slightly different code and your code.

The new code

Option Explicit
Private Sub DTPicker1_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
End Sub
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lOld As Long
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 = 3 Then
    If oldVal = "" Then
      'do nothing
    Else
      If newVal = "" Then
        'do nothing
      Else
        lOld = Len(oldVal)
        If Left(newVal, lOld) = oldVal Then
          Target.Value = newVal
        Else
           Target.Value = oldVal _
                & ", " & newVal
        End If
      End If
    End If
  End If
End If
 
If newVal = "" Then
  'do nothing
Else
  lOld = Len(oldVal)
  If Left(newVal, lOld) = oldVal Then
    Target.Value = newVal
  Else
     Target.Value = oldVal _
          & ", " & newVal
  End If
End If

exitHandler:
  Application.EnableEvents = True
End Sub

 

[RCBricker]

Glad it works