locked
Adding to a dynamic list if not already present RRS feed

  • Question

  • I have code which allows me to add data to my worksheet.

    Certain cells allow me to add data by List by means of Comboboxes, but what I would like is if the data required is not in the dynamic list a warning appears with a warning along the lines of "Data Type not present, do you wish to add this to the list",

    if "OK" is pressed the data can is added to the dynamic list, if "No" is pressed "Please Select another entry" message appears with "OK".

    the Combobox is named "cboIncidet_Type with the RowSource refering to the dynamic list "Incident_Type".

    Thanks
    Steve

    Tuesday, January 2, 2018 12:14 PM

Answers

  • I tested the code by Bernie Deitrick in Excel 2016 and ran into some problems.

    Even though I had set the name range "Incident_Type" as a global name, when I created a formula to make the range dynamic, the VBA code would not accept it unless I specified the worksheet with the named range.

    Next, the RowSource for the ComboBox would not update itself without VBA code to specifically update it after adding the extra element.

    The above occurred with the ComboBox on a Userform and also with a ComboBox on the worksheet.

    I used the following formula to create the Dynamic Named Range from data as per the screen shot below. (The Range for the Named range for the displayed data is A2:A11)

    =OFFSET(Sheet2!A1,1,0,COUNTA(Sheet2!$A:$A)-1,1)

    Following is my edited code for a ComboBox on a Userform

    'Specify the worksheet for the named range even if Named Range is Global
    'Reset the RowSource range to new range for the ComboBox

    Private Sub cboIncidet_Type_AfterUpdate()
        Dim v As Variant
        Dim DataFromComboBox As Variant
       
        DataFromComboBox = Me.cboIncidet_Type.Value
       
        v = Application.Match(DataFromComboBox, Worksheets("Sheet2").Range("Incident_Type"), False)
       
        If IsError(v) Then
            If MsgBox(DataFromComboBox & " does not currently exist. Add it?", vbYesNo) = vbYes Then
                With Worksheets("Sheet2").Range("Incident_Type")
                    .Cells(.Rows.Count + 1, 1).Value = DataFromComboBox
                    Me.cboIncidet_Type.RowSource = "Incident_Type"
                End With
            Else
                Me.cboIncidet_Type.Value = ""
                MsgBox "Please select another entry.", vbOKOnly
            End If
       
        End If
    End Sub

    'Use following code if the ComboBox is on a Worksheet
    'Specify the worksheet for the named range even if Named Range is Global
    'Reset the ListFillRange range to new range for the ComboBox

    Private Sub cboIncidet_Type_LostFocus()
        Dim v As Variant
        Dim DataFromComboBox As Variant
       
        DataFromComboBox = Me.cboIncidet_Type.Value
       
        v = Application.Match(DataFromComboBox, Worksheets("Sheet2").Range("Incident_Type"), False)
       
        If IsError(v) Then
            If MsgBox(DataFromComboBox & " does not currently exist. Add it?", vbYesNo) = vbYes Then
                With Worksheets("Sheet2").Range("Incident_Type")
                    .Cells(.Rows.Count + 1, 1).Value = DataFromComboBox
                    Me.cboIncidet_Type.ListFillRange = "Incident_Type"
                End With
            Else
                Me.cboIncidet_Type.Value = ""
                MsgBox "Please select another entry.", vbOKOnly
            End If
       
        End If

    End Sub

    Addendum:

    The following code can also be used to force updates of the RowSource or ListFillRange.

    Me.cboIncidet_Type.RowSource = Me.cboIncidet_Type.RowSource

    Me.cboIncidet_Type.ListFillRange = Me.cboIncidet_Type.ListFillRange


    Regards, OssieMac


    • Edited by OssieMac Wednesday, January 3, 2018 4:43 AM
    • Marked as answer by Steve MW Wednesday, January 3, 2018 5:39 PM
    Wednesday, January 3, 2018 4:24 AM

All replies

  • I would use code like this:

    Dim v As Variant

    Dim DataFromComboBox As String '?

    DataFromComboBox = ....... however you get your 'data to add' value

    v = Application.Match(DataFromComboBox, Range("Incident_Type"), False)

    If IsError(v) Then

        If Msgbox(DataFromComboBox & " does not currently exist. Add it?", vbYesNo) = vbYes Then

            Range("Incident_Type").Cells(1,1).Offset(Range("Incident_Type").Cells.Count,0).Value = DataFromComboBox

        Else

            MsgBox "Please select another entry.", vbOKOnly

        End If

    End If

     
    Tuesday, January 2, 2018 9:13 PM
  • I tested the code by Bernie Deitrick in Excel 2016 and ran into some problems.

    Even though I had set the name range "Incident_Type" as a global name, when I created a formula to make the range dynamic, the VBA code would not accept it unless I specified the worksheet with the named range.

    Next, the RowSource for the ComboBox would not update itself without VBA code to specifically update it after adding the extra element.

    The above occurred with the ComboBox on a Userform and also with a ComboBox on the worksheet.

    I used the following formula to create the Dynamic Named Range from data as per the screen shot below. (The Range for the Named range for the displayed data is A2:A11)

    =OFFSET(Sheet2!A1,1,0,COUNTA(Sheet2!$A:$A)-1,1)

    Following is my edited code for a ComboBox on a Userform

    'Specify the worksheet for the named range even if Named Range is Global
    'Reset the RowSource range to new range for the ComboBox

    Private Sub cboIncidet_Type_AfterUpdate()
        Dim v As Variant
        Dim DataFromComboBox As Variant
       
        DataFromComboBox = Me.cboIncidet_Type.Value
       
        v = Application.Match(DataFromComboBox, Worksheets("Sheet2").Range("Incident_Type"), False)
       
        If IsError(v) Then
            If MsgBox(DataFromComboBox & " does not currently exist. Add it?", vbYesNo) = vbYes Then
                With Worksheets("Sheet2").Range("Incident_Type")
                    .Cells(.Rows.Count + 1, 1).Value = DataFromComboBox
                    Me.cboIncidet_Type.RowSource = "Incident_Type"
                End With
            Else
                Me.cboIncidet_Type.Value = ""
                MsgBox "Please select another entry.", vbOKOnly
            End If
       
        End If
    End Sub

    'Use following code if the ComboBox is on a Worksheet
    'Specify the worksheet for the named range even if Named Range is Global
    'Reset the ListFillRange range to new range for the ComboBox

    Private Sub cboIncidet_Type_LostFocus()
        Dim v As Variant
        Dim DataFromComboBox As Variant
       
        DataFromComboBox = Me.cboIncidet_Type.Value
       
        v = Application.Match(DataFromComboBox, Worksheets("Sheet2").Range("Incident_Type"), False)
       
        If IsError(v) Then
            If MsgBox(DataFromComboBox & " does not currently exist. Add it?", vbYesNo) = vbYes Then
                With Worksheets("Sheet2").Range("Incident_Type")
                    .Cells(.Rows.Count + 1, 1).Value = DataFromComboBox
                    Me.cboIncidet_Type.ListFillRange = "Incident_Type"
                End With
            Else
                Me.cboIncidet_Type.Value = ""
                MsgBox "Please select another entry.", vbOKOnly
            End If
       
        End If

    End Sub

    Addendum:

    The following code can also be used to force updates of the RowSource or ListFillRange.

    Me.cboIncidet_Type.RowSource = Me.cboIncidet_Type.RowSource

    Me.cboIncidet_Type.ListFillRange = Me.cboIncidet_Type.ListFillRange


    Regards, OssieMac


    • Edited by OssieMac Wednesday, January 3, 2018 4:43 AM
    • Marked as answer by Steve MW Wednesday, January 3, 2018 5:39 PM
    Wednesday, January 3, 2018 4:24 AM
  • Thank you OssieMac

    Works a treat

    Steve

    Wednesday, January 3, 2018 5:39 PM