How to create command button to populate multiple selections in another table

Posted on 2011-05-12
Last Modified: 2012-06-27
I have a section of a worksheet which is to function as a "user interface", allowing the user to make selections of details about an employee.

the user would enter then enter dates stipulating the period of time that this combination applied for.

E.g. If an employee changed from a casual to a full time or went from a level 2 to level 3 employment classification we need to keep a running sheet on these changes.

If looking at the example attatched, basically need a Macro that is capable of copying data from the cells and validation lists above the red line into the cells below the line (marked in yellow).

Once an user has made one selection and copied it down to the yellow cells, they need to have the ability to add further combinations and have them added to the table below. I.E the first combinations copied into row 19, then row 20 etc.

And help would be greatly appreciated UIexample.xlsx UIexample.xlsx
Question by:Andross9
    LVL 33

    Accepted Solution

    You can create a macro that runs when the user clicks on a button, that adds the selections to the table.

    Please take a look at the attached working example. Click on the button "Add Combination".

    Sub AddCombination()
    Dim lstRow As Long
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    lstRow = ws.Cells(ws.Rows.Count, "J").End(xlUp).Row
    ws.Cells(lstRow + 1, 10).Value = ws.Range("H4").Value
    ws.Cells(lstRow + 1, 12).Value = ws.Range("H7").Value
    ws.Cells(lstRow + 1, 14).Value = ws.Range("H10").Value
    End Sub

    Open in new window

    LVL 33

    Expert Comment

    You need to use a button to run a macro because it's the only way you can know when the user finished selecting all 3 values of the combinations and that he wants to add that combination to the list. That's why I've used a button to run it instead of using some event to trigger this.


    Author Comment

    Excellent solution. Exactly the result I was looking for.

    However also need it to be able to capture the values entered in Row Q and the dates entered in V4 and X4.

    Tried to vary the Macro to achieve this but was not successful.

    Author Closing Comment

    Got it working for other areas too, excellent solution. Quite easy to follow with a bit of playing. Thanks!

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now