Link to home
Start Free TrialLog in
Avatar of Massimo Scola
Massimo ScolaFlag for Switzerland

asked on

Data tables and userforms

Hello everyone

I work with userforms and tables. What code should I write, if I want to display a userform when the user wants to add a new row at the end of the table?  

I've attached an example

Avatar of Norie

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Massimo Scola


So there is no way to call a userform if the user adds a new row to the table?
I have lots of data, and a user might be prompted to enter the data manually, which I don't want.
I have tried it with the userform.
Avatar of Norie

You could possibly use worksheet event code to call a userform when a user does something, say enters a value in the row below the table.

Is that what you are actually looking for rather than a button?

Try this, it goes in the worksheet module of Sheet1.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

    If Target.Row = Me.ListObjects(1).Range.Row + Me.ListObjects(1).Range.Rows.Count - 1 Then

        Set rng = Me.ListObjects(1).Range(Me.ListObjects(1).Range.Rows.Count, 2)

        UserForm1.txtName = rng.Value

        Set rng = Me.ListObjects(1).Range(Me.ListObjects(1).Range.Rows.Count, 3)

        UserForm1.txtStreet = rng.Value

        Set rng = Me.ListObjects(1).Range(Me.ListObjects(1).Range.Rows.Count, 4)

        UserForm1.txtPhone = rng.Value


    End If

End Sub

Open in new window

Note, this is a bit rough - I've not used tables/ListObjects in code for some time.

It can be tidied up if needed.