Data Validation dropdown that can choose mutable selection

[ABabeNChrist]

Is it possible to create a Data Validation dropdown within a single cell so that I can add mutable selection together?
Here’s an example, let’s say I have a dropdown with 10 possible comments. But I wish to use 2 or even 3 of them within the same cell. And combinations can vary from time to time. Is this possible to choose mutable selection like that. So when I choose 1 selection and then select another it would add new comment right along side first selection.

[HansV]

No, you cannot do that with validation. The item selected in the dropdown list replaces the current value of the cell. But it wouldn't be a good idea either - the user would be able to add the same item more than once, and wouldn't have a way to remove an item from the cell.

You could use an ActiveX combo box for this purpose. In the attached sample workbook, the combo box is shown to the right of cell F3 when you select that cell. Selecting an item will add it to the list in cell F3, or if it has already been used, remove it. Look at the worksheet code to see how it's done.

Sample.xlsm

[ABabeNChrist]

Thank you Hans
I heard of using ActiveX combo box, but wasn’t sure about Data Validation, was just hoping...
For my uses I prefer using Data validation even without mutable selections.
and thank you for that sample

[ABabeNChrist]

Hi Hans
Using the approach as you described, how would I populated with additional comments. By using let’s say sheets2, column A as the source for selected comments to my ActiveX combo box. That way I could populate the combo box manually. Is this possible

[StuartR]

Hi Hans
Using the approach as you described, how would I populated with additional comments. By using let’s say sheets2, column A as the source for selected comments to my ActiveX combo box. That way I could populate the combo box manually. Is this possible

I'm not sure I understand what you are trying to do.
The combo box in the example workbook has it's ListFillRange property set to A1:A4
If you change this to Sheet2!A:A then it may do what you want.

[ABabeNChrist]

Hi StuartR
Ops I guess there were a couple holes in my description, I apologize. Your suggestion was just what I was after it worked beautiful. Thank you……
Now I’m trying to create an input box that will populate my combo box.
I figured out how to input my comments to Sheet2 column A
But not sure how to have comment entered in next empty cell only
here is what I have so far

Code: Select all

    Dim MyBx As String

    On Error Resume Next

    MyBx = InputBox("Add new comment then Press OK" & "", "Enter Your New Comment Below")
    If MyBx = "" Then Exit Sub

    Sheets("Sheet2").Range("A1:A5") = MyBx
    If MyBx = True Then
        MsgBox ("Your comment has been entered")
    End If

[StuartR]

Assuming that you don't leave any blank cells in column A then the first empty cell in the column will be

Application.Worksheets("Sheet2").Range("A1").End(xlDown).Offset(1,0)

So if MyBx is a variable that holds the value you want in the next empty cell, you can use an expression like

Application.Worksheets("Sheet2").Range("A1").End(xlDown).Offset(1,0).Value = MyBx

Your code uses Inputbox to get a value for MyBox, and Inputbox does return a string, so this should work, but you also use the line
If MyBx = True
which is not correct. You should replace this with
If MyBx = ""

[StuartR]

You can also improve this code a bit by defining the ListFillRange of Combobox1 every time you run the code that displays the Inputbox. Just add the line

Code: Select all

Worksheets("Sheet1").Combobox1.ListFillRange = "Sheet2!A1:A" & _
    Worksheets("Sheet2").Range("A1").End(xlDown).Row

to your code and the Combobox will always have the right range, without including all the blank cells in column A

[ABabeNChrist]

Thank you StuartR
Everything worked great except for

Code: Select all

If MyBx = "" Then

I had to resort back to

Code: Select all

If MyBx = True Then

in order for it to work, maybe i missed something
here is how my code looks now

Code: Select all

    Dim MyBx As String

    On Error Resume Next

    MyBx = InputBox("Add new comment then Press OK" & "", "Enter Your New Comment Below")
    If MyBx = "" Then Exit Sub

    Worksheets("Sheet1").ComboBox1.ListFillRange = "Sheet2!A1:A" & _
                                                   Worksheets("Sheet2").Range("A1").End(xlDown).Row

    Application.Worksheets("Sheet2").Range("A1").End(xlDown).Offset(1, 0).Value = MyBx
    
    If MyBx = True Then
        MsgBox ("Your comment has been entered")
    End If

[StuartR]

Thank you StuartR
Everything worked great except for

Code: Select all

If MyBx = "" Then

That's because I didn't read the code carefully enough. You want to display the message if the user entered text, so it should be

Code: Select all

If MyBx <> "" Then

[StuartR]

I think there is a small error in this code...

Code: Select all

    Dim MyBx As String

    On Error Resume Next

    MyBx = InputBox("Add new comment then Press OK" & "", "Enter Your New Comment Below")
    If MyBx = "" Then Exit Sub

    Worksheets("Sheet1").ComboBox1.ListFillRange = "Sheet2!A1:A" & _
                                                   Worksheets("Sheet2").Range("A1").End(xlDown).Row

    Application.Worksheets("Sheet2").Range("A1").End(xlDown).Offset(1, 0).Value = MyBx
    
    If MyBx = True Then
        MsgBox ("Your comment has been entered")
    End If

You need to add the new value to the worksheet BEFORE you set the ListFillRange, otherwise you will end up with a range that is one too short. Here is my suggested way to organize your code...

Code: Select all

    Dim MyBx As String

    On Error Resume Next
    MyBx = InputBox("Add new comment then Press OK" & "", "Enter Your New Comment Below")
    
    If MyBx <> "" Then
        Application.Worksheets("Sheet2").Range("A1").End(xlDown).Offset(1, 0).Value = MyBx
        Worksheets("Sheet1").ComboBox1.ListFillRange = "Sheet2!A1:A" & _
           Worksheets("Sheet2").Range("A1").End(xlDown).Row
        MsgBox ("Your comment has been entered")
    End If

This only has one test to see if MyBx is blank, and avoids using the Exit Sub, which is legal code but best not used if not necessary.

[ABabeNChrist]

I see what you did and I fully understand
I’m learning as I go
Thank You very much StuartR