Avatar of Massimo Scola
Massimo Scola
Flag 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

Thanks
datatable.xlsm
Microsoft Excel

Avatar of undefined
Last Comment
Norie

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Norie

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Massimo Scola

ASKER
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.
Massimo Scola

ASKER
I have tried it with the userform.
Thanks
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

        UserForm1.Show

    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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck