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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
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,461
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,461
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,461
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....
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,983
Messages
5,834,709
Members
430,311
Latest member
JAC0617

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