Pop-up form to add to data list

[Claymationator]

I have not been able to find a thread that is directed at what I am trying to do. I have a workbook that is going to be used to keep track of volunteer information for a local non-profit. The workbook will contain the following information:

Name
Home Phone
Cell
Mailing Address
Email
Email 2
Date of Birth
Current Department
Team Leader
Departments Worked In
Years of Service
Misc Notes

I have all of that information on one sheet, but I want to be able to have another sheet where I can click a button to add a new person to the list of volunteers and have a form come up with those fields in it. Then, once it is entered click a button that will add it to the bottom of the list on the other sheet.

Any help is appreciated, even if another link is given that will give me some added direction for what i want to do. Thanks for looking.

Clay

I am also now running Vista Ultimate and Excel 2003 & 2007

 

[vba_newby]

sounds like you are wanting a a list - to do this go to Data>Validation...>

in the allow box, choose List. You can type in the list in the Source box or if you have the source names in the file simply select them

you can even use an input message for special instructions if you need it

hope this helps

 

[Claymationator]

No, I am not looking for data validation, nor would that work for the results that I am looking for. I need a userform that I can populate the data and then once entered have those values copied into another sheet at the bottom of the list.

So, for instance, I enter in these values into the fields on the form:

First Name: John
Last Name: Smith
Phone: 555-555-5555

etc.

Then, I press a button on the form and John is copied to Sheet 2 cell A2, Smith is copied to Sheet 2 Cell B2, 555-555-5555 is copied to Sheet 2 cell C2.

So, every time that I pull up the form it will append the list with the newest inputs.

I hope that makes more sense.

 

[ZVI]

Hi,

There are two propositions for you:

1. The simplest way is in usage of menu Data – Form for editing data as well as adding the new one.
Let's assume that your data list is in Sheet1.
The code of macro in VBA module would be:

Sub MyMacro1()
    Worksheets("Sheet1").ShowDataForm
End Sub

Create your user panel (Excel 2000-2003) or place the button anywhere in sheets and make referencing to MyMacro: select button / right click / assign macro / MyMacro1

2. As alternative, you can surely design your own sheet-form using of:
- unprotected form-cells
- protection of the sheet with unchecked “Select locked cells”
- button “Add” (textbox can be used as well).

In this case the following VBA code is required for transferring data from the form-sheet to the bottom (empty) row of your data list:

Sub MyMacro1()
    Dim RngFrom, RngTo
    ' Set form cells range (here are taken randomly for example)
    Set RngFrom = Worksheets("Sheet2").Range("B1:B10,C11,D12")
    ' Set data list used region assuming the title starts from A1
    Set RngTo = Worksheets("Sheet1").Range("A1").CurrentRegion
    ' Set the bottom empty row in data list
    Set RngTo = RngTo.Rows(1).Offset(RngTo.Rows.Count)
    ' Add form data to the list
    RngTo.Value = RngFrom.Value
End Sub

Place code in VBA module and provide for the "Add" button the referencing to MyMacro as it was described in my 1st proposition.

Enjoy,
Vladimir

 

[ZVI]

Clay, having re-read your task more closely I suggest to create VBA Userform with appropriative labels & textboxes and the "Add" button.

The code in UserForm for CommandButton1 as the "Add" button:

Private Sub CommandButton1_Click()

  Dim oRng As Range, oLastRow As Range

  ' Find used region of active sheet, assuming the title starts from A1
  Set oRng = ActiveSheet.[A1].CurrentRegion

  ' Find the bottom empty row in active sheet
  Set oLastRow = oRng.Rows(1).Offset(oRng.Rows.Count)

  ' Add form data to the bottom (empty) row of the active sheet list
  oLastRow.Cells(1, 1).Value = TextBox1
  oLastRow.Cells(1, 2).Value = TextBox2
  oLastRow.Cells(1, 3).Value = TextBox3
  ' ... and so on

  ' Unload Me  ' If required

End Sub

Regards,
Vladimir

 

[Claymationator]

Vladmir,

Thanks for the post. I have gone the route of putting the form on the first sheet of my workbook and protected the sheet. I have values in the following cells:

First Name - B4
Last Name - E4
Street Address - B7
Street Addrses 2 - B10
City - B13
State - F13
Zip - H13
Home Phone - B16
Cell Phone - F16
Email - B19
Email 2 - F19
DOB - H4
Current Dept - J7
Team Leader - J10
Notes - B22

What I need to do is copy each of those values into the "Database" sheet in cells A1:Q1, and those columns are listed in the same order as they are shown above. I am sure that I probably missed something in your post, but when I run the macro it takes the value in B4, and copies that in all of the cells in the Database sheet.

Any help is appreciated.

Clay

 

[Claymationator]

Vladmir,

Thanks for the other post that you recommend the userform. I would like to get to that point ideally, but I am a little unsure of how to do it with the layout of my sheet, and I have not created a userform before. Can you give me a little insight in how to do that and I think that I can probably get the rest worked out.

Clay

 

[ZVI]

Ok Clay,

For method 2 and your already designed form the code of the macro is:

Sub MyMacro1()
    Dim RngFrom, LeftCellTo, i
    ' Set form cells range
    Set RngFrom = ActiveSheet.Range("B4,E4,B7,B10,B13,F13,H13,B16,F16,B19,F19,H4,J7,J10,B22")
    ' Find the leftmost NOT empty cell in DataBase sheet
    Set LeftCellTo = Worksheets("Database").Range("A" & Rows.Count).End(xlUp)
    ' Find the leftmost empty cell in DataBese sheet
    If Len(LeftCellTo) > 0 Then Set LeftCellTo = LeftCellTo.Offset(1, 0)
    ' Add form data to the list
    For Each x In RngFrom
      LeftCellTo.Offset(0, i).Value = x
      i = i + 1
    Next
End Sub

Regards,
Vladimir

 

[ZVI]

Vladmir,

Thanks for the other post that you recommend the userform. I would like to get to that point ideally, but I am a little unsure of how to do it with the layout of my sheet, and I have not created a userform before. Can you give me a little insight in how to do that and I think that I can probably get the rest worked out.

Clay

Clay, here is the short instruction:

1. Press Alt-F11 to go to the Visual Basic Editor

2. The left window is the project explorer tree where your worksheet VBA project container is viewed.

3. Choose menu: Insert / Module to add the new module where usually the macro code is placed.
The Module1 (2,3 etc) would be created.

4. Double click on Module1 in project explorer window, the blank code area of the Module1 is appeared at the top right side.

5. Copy to this area the code of Macro1 for proposed variants 1 and 2.
For variant 3 with UserForm add the user form calling macro in Module1:

Sub UserFormCalling ()
UserForm1.Show
End Sub

6. Take menu: Insert / UserForm. UserForm1 is appeared in project explorer window and where the Module1 code has been previously.

7. Using ToolBox add to the UserForm1 the labels and the textboxes elements. Press F4 for viewing of its properties. i.e. the Name property: Label1, Label2, …,TextBox1, TextBox2 and so on.

8. Add CommandButton1, Press F4 and customize its Caption property as “Add”.

9. Right click on a form with choosing of “View code” opens the code area of the user form where you should place the code from the 3d proposition, customized for your details.

Press F1 for context help, use internet resources and VBA books for improving your skills.

Wish you success,
Vladimir