Add items to a Drop Down List via a form?

[ablumny]

Hello members. Second time coming to the well; appreciate the help of course!

• I have a list on a (sheet 3) with three columns
  • A = NAME
  • B = PHONE
  • C = EMAIL
• On (sheet 2) I have a cell B2 with data validation connected to Column A (NAME)
• On (sheet 1), the result of whats in (Sheet2, B2) appears in a cell and thru vlookup, the phone and email are pulled right under the name

What I want to do is add new entries to the list on the fly if they are not present. I have found methods to add one data point but I want to add all three.


Thanks in advance for any advice!
Andrew

 

[mrmmickle1]

Are you using a UserForm? or is this a "Form" you have created in excel.

If your using a UserForm you should look into using a combobox and then connect the rowsource of the combobox to a dynamic range by using a formula like this:

=OFFSET(YourWorksheet!$A$2, 0, 0, COUNTA(YourWorkSheet!$A:$A)-1,1) [COLOR=#008000]'This assumes list has a header and exists in column A[/COLOR]

If your using a "Form" in Excel yomay want to consider making a simple UserForm with a 3 textboxes (One for each field).

1. You can launch the form from the worksheet
2. The form will then input your data into the right location in your workbook (Most likely the last row of x columns.....)

Maybe something like this:

    [COLOR=#0000ff]Dim[/COLOR] ws [COLOR=#0000ff]As Worksheet[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] LR [COLOR=#0000ff]As Long[/COLOR]
    
    [COLOR=#0000ff]Set[/COLOR] ws = Sheets("YourWorksheet") 
    
[COLOR=#0000ff]    With [/COLOR]ws
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row [COLOR=#008000]'1 Specifies Column A[/COLOR]
        .Cells(LR + 1, 1) = TextBox1
        .Cells(LR + 1, 2) = TextBox2
        .Cells(LR + 1, 3) = TextBox3
[COLOR=#0000ff]    End With[/COLOR]

 

[ablumny]

Are you using a UserForm? or is this a "Form" you have created in excel.

If your using a UserForm you should look into using a combobox and then connect the rowsource of the combobox to a dynamic range by using a formula like this:

=OFFSET(YourWorksheet!$A$2, 0, 0, COUNTA(YourWorkSheet!$A:$A)-1,1) [COLOR=#008000]'This assumes list has a header and exists in column A[/COLOR]

End With[/CODE]

Thank you

• It happens that I just created a user form with the three fields I need. ADD PART VBA is working and adding rows to my list.
• Im able to create a working dynamic range which is linked to my cell via data validation

What I cant seem to do is trigger the input user form when something other then whats already in the list is chosen

 

[mrmmickle1]

Maybe something like this:

[COLOR=#0000ff]If [/COLOR]ComboBox1.ListIndex < 0 [COLOR=#0000ff]Then[/COLOR]
    MsgBox "Your entry must be part of the list", vbCritical
[COLOR=#0000ff]    Exit Sub[/COLOR]
[COLOR=#0000ff]End If[/COLOR]

WEBSITE FOR REFERENCE

 

[ablumny]

Maybe something like this:

[COLOR=#0000ff]If [/COLOR]ComboBox1.ListIndex < 0 [COLOR=#0000ff]Then[/COLOR]
    MsgBox "Your entry must be part of the list", vbCritical
[COLOR=#0000ff]    Exit Sub[/COLOR]
[COLOR=#0000ff]End If[/COLOR]

WEBSITE FOR REFERENCE

Either I dont know where to use or how to use your script... or it doesnt do what I need. but thanks for trying!

 

[mrmmickle1]

Are you using ComboBoxes? If your dynamic list is linked to the Rowsource Property in ComboBox1 for example then you can put something like this in your UserForm module....

[COLOR=#0000ff]Private Sub[/COLOR] ComboBox1_Exit([COLOR=#0000ff]ByVal [/COLOR]Cancel[COLOR=#0000ff] As [/COLOR]MSForms.ReturnBoolean)
[COLOR=#0000ff]    If [/COLOR]ComboBox1.ListIndex < 0 [COLOR=#0000ff]Then[/COLOR]
         MsgBox "Your entry must be part of the list", vbCritical
[COLOR=#0000ff]        Exit Sub[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

So for example if your end user were to enter something manually that was not in the dynamic list you have linked to the combobox1 then when you click on another textbox or combobox or tab over then this Sub Procedure will fire and tell them via a msgbox that their entry must be part of your predefined list.....

 

[ablumny]

Are you using ComboBoxes? If your dynamic list is linked to the Rowsource Property in ComboBox1 for example then you can put something like this in your UserForm module....

[COLOR=#0000ff]Private Sub[/COLOR] ComboBox1_Exit([COLOR=#0000ff]ByVal [/COLOR]Cancel[COLOR=#0000ff] As [/COLOR]MSForms.ReturnBoolean)
[COLOR=#0000ff]    If [/COLOR]ComboBox1.ListIndex < 0 [COLOR=#0000ff]Then[/COLOR]
         MsgBox "Your entry must be part of the list", vbCritical
[COLOR=#0000ff]        Exit Sub[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

So for example if your end user were to enter something manually that was not in the dynamic list you have linked to the combobox1 then when you click on another textbox or combobox or tab over then this Sub Procedure will fire and tell them via a msgbox that their entry must be part of your predefined list.....

UGH! Actually for other reasons I now realize this wont work for me. We have a template with a lookup sheet. When we pull the template and add new data we are of course only adding it to the version we opened, not the main template. No good. I suppose I could call out to an external lookup sheet buts its on a google drive. Ill adjust my plan but thank you VERY MUCH for helping me out!

Andrew

 

[mrmmickle1]

Hopefully you will be able to solve your issues. If the file wasn't on google drive and was instead on a shared drive you might consider a setup where you have multiple user files. When the User File is opened a WorkBook_Open Event fires that:

1. Opens the Master File
2. Updates the list in the User File
3. Closes the Master File

You could also add the functionality to the User Files where when a user fills out the User Form you have constructed and clicks Update:

1. Opens Master File
2. Updates Master File
3. Saves Master File
4. Closes Master File

I would suggest a shared workbook, but I have had far too many issues with these files to recommend them as a solution to anyone....