Error?

%3CLINGO-SUB%20id%3D%22lingo-sub-744962%22%20slang%3D%22en-US%22%3EError%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-744962%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20anyone%20find%20the%20error%20in%20this%20code%3F%20I've%20inputted%20it%20to%20the%20developer%2C%20but%20it%20doesn't%20seem%20to%20be%20applying%20to%20the%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EDim%20Oldvalue%20As%20String%3CBR%20%2F%3EDim%20Newvalue%20As%20String%3CBR%20%2F%3EMe.Protect%20UserInterfaceOnly%3A%3DTrue%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3CBR%20%2F%3EOn%20Error%20GoTo%20Exitsub%3CBR%20%2F%3EIf%20Target.Column%20%3D%2015%20Or%20Target.Column%20%3D%2025%20Then%3CBR%20%2F%3E%26nbsp%3B%20If%20Target.SpecialCells(xlCellTypeAllValidation)%20Is%20Nothing%20Then%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20GoTo%20Exitsub%3CBR%20%2F%3E%26nbsp%3B%20Else%3A%20If%20Target.Value%20%3D%20%22%22%20Then%20GoTo%20Exitsub%20Else%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Application.EnableEvents%20%3D%20False%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Newvalue%20%3D%20Target.Value%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Application.Undo%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Oldvalue%20%3D%20Target.Value%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20Oldvalue%20%3D%20%22%22%20Then%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Target.Value%20%3D%20Newvalue%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Else%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20InStr(1%2C%20Oldvalue%2C%20Newvalue)%20%3D%200%20Then%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Target.Value%20%3D%20Oldvalue%20%26amp%3B%20%22%2C%20%22%20%26amp%3B%20Newvalue%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Else%3A%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Target.Value%20%3D%20Oldvalue%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20If%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20If%3CBR%20%2F%3E%26nbsp%3B%20End%20If%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3CBR%20%2F%3EExitsub%3A%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-744962%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-746338%22%20slang%3D%22en-US%22%3ERe%3A%20Error%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-746338%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F370493%22%20target%3D%22_blank%22%3E%40adavis726%3C%2FA%3E%26nbsp%3BWhat%20are%20you%20trying%20to%20achieve%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDid%20you%20step%20through%20the%20code%20with%20F8%20and%20see%20what%20gets%20evaluated%3F%26nbsp%3B%20As%20far%20as%20I%20see%20it%2C%20the%20code%20does%20the%20following%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20a%20cell%20in%20the%20range%20contains%20%3CSTRONG%3Edata%20validation%3C%2FSTRONG%3Eand%20the%20previous%20value%20of%20the%20cell%20was%20not%20a%20%3CSTRONG%3Eblank%20%3C%2FSTRONG%3Eor%20the%20%3CSTRONG%3Ecurrent%26nbsp%3B%3C%2FSTRONG%3E%3CSTRONG%3Evalue%2C%26nbsp%3B%3C%2FSTRONG%3Ethen%20the%20current%20value%20of%20the%20cell%20gets%20appended%20to%20the%20old%20value.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20works%20fine%20in%20my%20tests%20with%20the%20code%20as%20you%20posted%20it.%20If%20it%20does%20not%20work%20for%20you%2C%20step%20through%20the%20code%20to%20identify%20where%20your%20expectations%20and%20the%20code%20don't%20match.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-746517%22%20slang%3D%22en-US%22%3ERe%3A%20Error%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-746517%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F370493%22%20target%3D%22_blank%22%3E%40adavis726%3C%2FA%3E%3C%2FP%3E%3CP%3EDid%20you%20install%20the%20code%20in%20the%20code%20pane%20of%20a%20worksheet%3F%20It%20won't%20work%20if%20you%20put%20it%20in%20ThisWorkbook%20or%20in%20a%20regular%20module%20sheet.%20I%20doubleclicked%20Sheet1%20in%20the%20screenshot%20below%20and%20put%20the%20code%20there--where%20it%20is%20working%20for%20me.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122829iE2B82521BC1DA1A0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22CodeInWorksheetCodePane.PNG%22%20title%3D%22CodeInWorksheetCodePane.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-747372%22%20slang%3D%22en-US%22%3ERe%3A%20Error%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-747372%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3BI%20have%20a%20column%20which%20indicates%20what%20services%20a%20client%20accepts.%20The%20column%20links%20to%20a%20drop%20down%20list%20on%20sheet%202.%20I%20am%20wanting%20to%20be%20able%20to%20select%20multiple%20items%20from%20the%20drop%20down%20list.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-748870%22%20slang%3D%22en-US%22%3ERe%3A%20Error%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-748870%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F370493%22%20target%3D%22_blank%22%3E%40adavis726%3C%2FA%3E%26nbsp%3B%20That%20does%20not%20really%20help%20troubleshoot%20the%20problem.%20If%20you%20expect%20the%20code%20to%20do%20something%2C%20but%20it%20doesn't%20do%20what%20you%20expect%2C%20you%20can%20step%20through%20the%20code%20with%20F8%20to%20see%20which%20IF%20branch%20is%20executed.%20Then%20you%20know%20where%20things%20go%20%22wrong%22%20and%20you%20can%20inspect%20the%20value%20that%20is%20the%20condition%20of%20the%20IF%20branch.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMaybe%20someone%20inserted%20a%20column.%20That%20would%20cause%20the%20macro%20to%20fail%2C%20because%20it%20is%20now%20watching%20the%20wrong%20columns.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20sort%20of%20thing.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20can't%20do%20these%20checks%20yourself%2C%20you%20will%20need%20to%20upload%20a%20sample%20file%20that%20shows%20the%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E
adavis726
New Contributor

Can anyone find the error in this code? I've inputted it to the developer, but it doesn't seem to be applying to the cells.

 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Me.Protect UserInterfaceOnly:=True
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 15 Or Target.Column = 25 Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

4 Replies

@adavis726 What are you trying to achieve?

 

Did you step through the code with F8 and see what gets evaluated?  As far as I see it, the code does the following:

 

if a cell in the range contains data validation and the previous value of the cell was not a blank or the current value, then the current value of the cell gets appended to the old value. 

 

This works fine in my tests with the code as you posted it. If it does not work for you, step through the code to identify where your expectations and the code don't match.

@adavis726

Did you install the code in the code pane of a worksheet? It won't work if you put it in ThisWorkbook or in a regular module sheet. I doubleclicked Sheet1 in the screenshot below and put the code there--where it is working for me.

CodeInWorksheetCodePane.PNG

@Ingeborg Hawighorst I have a column which indicates what services a client accepts. The column links to a drop down list on sheet 2. I am wanting to be able to select multiple items from the drop down list.

@adavis726  That does not really help troubleshoot the problem. If you expect the code to do something, but it doesn't do what you expect, you can step through the code with F8 to see which IF branch is executed. Then you know where things go "wrong" and you can inspect the value that is the condition of the IF branch. 

 

Maybe someone inserted a column. That would cause the macro to fail, because it is now watching the wrong columns. 

 

That sort of thing. 

 

If you can't do these checks yourself, you will need to upload a sample file that shows the problem.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies