[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

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

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
0
Andross9
Asked:
Andross9
  • 2
  • 2
1 Solution
 
jppintoCommented:
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".

jppinto
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

UIexample.xlsm
0
 
jppintoCommented:
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.

jppinto
0
 
Andross9Author Commented:
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.
0
 
Andross9Author Commented:
Got it working for other areas too, excellent solution. Quite easy to follow with a bit of playing. Thanks!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now