Master Child in VBA userform

[shawnw]

I have two worksheets:

- "People" (shows unique salesperson ID, name, and basic info about our sales team members, with one row per salesperson)
- "Sales" (shows details about each sale, with a foreign key back to the salesperson ID, with multiple rows per salesperson).

I have a VBA userform that lets users view and modify the salesperson data in the "People" worksheet. I would like to show zero to N rows at the bottom of the same userform and also display the data from the related "Sales" worksheet.

Basically, i want to show the salesperson at the top and their sales at the bottom of the same userform. I want to keep them synchronized. And I want the user to be able to make edits.

Is this possible in Excel?

Thanks,

Shawn

 

[Jeffrey Mahoney]

Shawn,

Let me try to understand. You want the sales folks to scroll through a list of people, while the bottom list shows the summary sales for each person?

Jeff

 

[shawnw]

Yes. But to be clear, it would not be a "summary," but the detailed records. Think of an Access form/sub-form. Thanks!

 

[My Aswer Is This]

Tell me how do you do this?
Your Quote:
have a VBA userform that lets users view and modify the salesperson data in the "People" worksheet.

How is this data modified in the userForm.

As far as I know only textboxes can be viewed and edited in a UserForm.

Multi column list boxes are good for viewing data but are not editable.

 

[Norie]

How is the data stored?

Do you have a table/sheet for salespeople and a separate table/sheet for sales?

If so, are those tables linked in any way?

For example is there some sort of primary key/foreign key setup where the primary key in the salespeople table is a unique identifier?

 

[shawnw]

Yes, as per original post. Thanks!

 

[shawnw]

I could display, for example, one row on top and one stack of rows below. When the row on top is selected on top (salesperson id), the rows below refresh to show sales made by that id. I use pushbuttonsome to navigate the master record and then repopulate the child records each time. Since subforms don't seem to be an option, I could live with a fixed list of 10 detail rows, for example.

 

[Peter h]

I have a code similar to what I think you're wanting to do. Let me see if I'm understanding what you're requesting. You want to be able to select an employee from a list of employee, and then upon selection, you want the userform to show sales data for that person, that you can also edit. Is that correct?

What I do is analyze metallurgical samples, and record the data of multiple sample types. So what my userform does is it has a combobox populated with the sample date and time recorded for the sample (This would be your list of employee). When I select the sample I want to view the data for it populates textboxes with the data that was entered (This would be your sales data for the employee selected). The userform has a "Modify" button and a "Remove" button. I can change the data in the textboxes and click "Modify", and the userform updates the data in the correct row, or I can just click remove, and it will delete the appropriate row. Does this sound like something you're looking to do? I can try to modify the code for you to get it to work for you.

The only thing I'm wondering is it sounds like you want to see all sales data for the selected employee at the same time, and I'm not sure how you'd do that. What I'd maybe do is have the combobox with the list of employees, and when you select an employee, it populates another combobox with another sales ID (e.g. Dates, Times, or Order Numbers) associated with that employee. So then you can pull up the data for the selected sale or order or whatever. Let me know if this is on the right track, and I'll try to help you out.

 

[Norie]

You could use a listbox to display the sales data and another smaller, seperate form (or a set of controls on the main form) to allow the user to add/edit data.

To edit data the user would double click a row in the listbox, the small form would then pop up and be populated with the data from the row that was double clicked.

The user would then edit the data in the smaller form and then click a button to update the data in the listbox on the main form and close the smaller form.

To add data a button would be clicked and the small form would pop up with all fields empty.

They would then fill out the fields in the small form, then click a button to add the data to the listbox and close the small form.

To delete data from the listbox the user would select a row and click a delete button.

 

[Peter h]

You could use a listbox to display the sales data and another smaller, seperate form (or a set of controls on the main form) to allow the user to add/edit data.

To edit data the user would double click a row in the listbox, the small form would then pop up and be populated with the data from the row that was double clicked.

The user would then edit the data in the smaller form and then click a button to update the data in the listbox on the main form and close the smaller form.

To add data a button would be clicked and the small form would pop up with all fields empty.

They would then fill out the fields in the small form, then click a button to add the data to the listbox and close the small form.

To delete data from the listbox the user would select a row and click a delete button.

I had just thought about this also. Having a listbox he could display all the data he wants, and then just select the one he wants to modify.