Having diffuculty getting my target.value appear in the middle of my MsgBox message

[sspatriots]

Hi,

I'm trying to do exactly what the Post title says with the "Contextures" code below. However, the target.value doesn't show up in the MsgBox. I'm thinking this is something simple, but nothing I have tried has worked. Any ideas would be greatly appreciated. Thanks, SPS

Option Explicit
' Developed by Contextures Inc.
' www.contextures.com

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim str As String
Dim i As Integer
Dim rngDV As Range
Dim rng As Range
Dim lCol As Long
Dim myRsp As Long
Dim My_Value As Variant
'Added, SPS, 09/26/22
Dim strList As String
If Target.Count > 1 Or Target.Value = "" Then Exit Sub
Set ws = Worksheets("Drops")        'Changed "Lists" to "Drops", SPS, 09/26/22
  
If Target.Row > 1 Then
    On Error Resume Next
    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo 0
    If rngDV Is Nothing Then Exit Sub
    
    If Intersect(Target, rngDV) Is Nothing Then Exit Sub
      
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    On Error Resume Next
    Set rng = ws.Range(str)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub
    
    If Application.WorksheetFunction _
        .CountIf(rng, Target.Value) Then
        Exit Sub
  
    Set My_Value = Target.Value 'Added, SPS, 09/26/22
  
    Else
    
        myRsp = MsgBox("Add this " & My_Value & " item to the drop down list?", _
            vbQuestion + vbYesNo + vbDefaultButton1, _
            "New Item -- not in drop down")
    If myRsp = vbYes Then
        lCol = rng.Column
        i = ws.Cells(Rows.Count, lCol).End(xlUp).Row + 1
        ws.Cells(i, lCol).Value = Target.Value
      
      strList = ws.Cells(1, lCol).ListObject.Name
   
      With ws.ListObjects(strList).Sort
         .SortFields.Clear
         .SortFields.Add _
             Key:=Cells(2, lCol), _
             SortOn:=xlSortOnValues, _
             Order:=xlAscending
         .Header = xlYes
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
      End With
      
      With ws.ListObjects(strList)
        .Resize .DataBodyRange.CurrentRegion
      End With
      
   End If
  End If

End If

End Sub

 

[rlv01]

Your Set My_Value = Target.Value 'Added, SPS, 09/26/2 statement. is in the wrong place. It should be after the Else statement.

       If Application.WorksheetFunction.CountIf(rng, Target.Value) Then
            Exit Sub
        Else
            Set My_Value = Target.Value               'Added, SPS, 09/26/22

            myRsp = MsgBox("Add this " & My_Value & " item to the drop down list?", _
                           vbQuestion + vbYesNo + vbDefaultButton1, _
                           "New Item -- not in drop down")

 

[sspatriots]

I tried making that change, but now I get a "Run-time error '13': Type mismatch. Any idea what would cause that?

It happens at the line below:

Set My_Value = Target.Value 'Added, SPS, 09/26/22

 

[sspatriots]

I think I have it now. I set the Dim as a "String" in lieu of a "Variant" and removed the word "Set" from the line below.

My_Value = Target.Value 'Added, SPS, 09/26/22

Thanks for helping.

 

[rlv01]

Good catch. I should have seen that myself. Doh!