Add items to a Drop Down List via a form?

ablumny

New Member
Joined
Sep 28, 2014
Messages
7
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,456
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:

Code:
=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:
Code:
    [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

New Member
Joined
Sep 28, 2014
Messages
7
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:

Code:
=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

Well-known Member
Joined
May 11, 2012
Messages
2,456
Maybe something like this:

Code:
[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

New Member
Joined
Sep 28, 2014
Messages
7

ADVERTISEMENT

Maybe something like this:

Code:
[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

Well-known Member
Joined
May 11, 2012
Messages
2,456
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....

Code:
[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

New Member
Joined
Sep 28, 2014
Messages
7
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....

Code:
[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

Well-known Member
Joined
May 11, 2012
Messages
2,456
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....
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,774
Members
413,934
Latest member
austinb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top