ComboBox1_NotInList Question

[callasabra]

I have created a userform with a combobox linked to a list in an xlsx file. when the user enters a value in the combobox that is not present in the list, I want it to 1) prompt the user "Do you want to add "value" to the list?" and, if yes, add the value to the list in the xlsx file.

Currently, the form does everything except the NotInList portion. It is like that entire ComboBox1_NotInList is ignored.

Below is the code for the entire userform. It is adapted from many sources so I may have unintentionally left something out.

Option Explicit
Private oVars As Variables

Private Sub ComboBox1_NotInList(NewData As String, Response As Integer)
On Error GoTo Error_ComboBox1_NotInList

    Dim strMsg As String
    Dim ctl As Control
    ' Return Control object that points to combo box.
    Set ctl = Me.ComboBox1
    ' Prompt user to verify they wish to add new value.
    strMsg = "'" & NewData & "' is not in the list. "
    strMsg = strMsg & "Would you like to add it?"
   
    If strMsg = vbOK Then
        ' Set Response argument to indicate that data
        ' is being added.
        Response = acDataErrAdded
        ' Add string in NewData argument to row source.
        ctl.RowSource = ctl.RowSource & ";" & NewData
        Else
        ' If user chooses Cancel, suppress error message
        ' and undo changes.
        Response = acDataErrContinue
        ctl.Undo
    End If

Error_ComboBox1_NotInList:
MsgBox Err.Number & " " & Err.Description
Resume Exit_ComboBox1_NotInList
End Sub

Private Sub Userform_Initialize()
'Late binding.  No reference to Excel Object required.
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim cRows As Long
Dim i As Long
  Set xlApp = CreateObject("Excel.Application")
  'Open the spreadsheet to get data
  Set xlWB = xlApp.Workbooks.Open("C:\VBA\ReportData.xlsx")
  Set xlWS = xlWB.Worksheets(1)
  'Range is defined in ReportData
  cRows = xlWS.Range("PlaintiffRange").Rows.Count
  'Populate the list in the combobox.
  With Me.ComboBox1
    For i = 2 To cRows
       'Use .AddItem property to add a new row for each record and populate column 0
      .AddItem xlWS.Range("PlaintiffRange").Cells(i, 1)
      'Use .List method to populate the remaining columns
      .List(.ListCount - 1, 1) = xlWS.Range("PlaintiffRange").Cells(i, 2)
    Next i
  End With

  'Clean up
  Set xlWS = Nothing
  Set xlWB = Nothing
  xlApp.Quit
  Set xlApp = Nothing
lbl_Exit:
  Exit Sub
End Sub

Private Sub cmdOK_Click()
'Define the variable oVars
Set oVars = ActiveDocument.Variables
'Hide the userform
Me.Hide
'Assign the value of the combobox to the variable
oVars("Plaintiff").Value = Me.ComboBox1.Value
'Update the fields in the body of the document
ActiveDocument.Fields.Update
'Clear the variable
Set oVars = Nothing
'Unload the form
Unload Me
End Sub
Private Sub cmdCancel_Click()
'User has cancelled so unload the form
Unload Me
End Sub

[macropod]

The basic problem is that you're misusing StrMsg. You define it as being a particular string, with no way to change it, then try to test whether it's something else! Try something based on:

Private Sub ComboBox1_NotInList(NewData As String, Response As Integer)
    Dim varMsg As Variant
    ' Prompt user to verify they wish to add new value.
    varMsg = MsgBox("'" & NewData & "' is not in the list. " & _
      "Would you like to add it?", vbOKCancel)
    If varMsg = vbOK Then
        MsgBox "OK"
    Else
        MsgBox "Cancel"
    End If
End Sub

[callasabra]

Thanks for the help. I have modified the code according to your post. when I enter a value in the combobox it puts it in the document, but it is not prompting me to add to list, nor is it adding it to the list.
Does it matter that the combobox1_notinlist section is before the userform_initialize section?

[macropod]

The code I posted only dealt with the correct handling of your prompt, not with any associated updates of the list, etc. That's why the code I posted only outputs the 'OK' & 'Cancel' message boxes to show which button was clicked.