VBA: Get data from different sheets (display of Parent-Child)

[inddon]

Hello there,

I am trying to build the below functionality of displaying 'Parent-Child' data from different worksheets and display it.

Could you please advice on how this can be achieved using VBA? Attached sample workbook and quick print screen for your reference.

Many thanks
Don

Requirement:




There are 3 data worksheets:
a. Main: Display of parent and child records
b. Dept: Data related to Department section
c. Employees: Data related to employees for each department

Buttons:
a. Find: Search the entered department number value and it's related employees
from worksheet 'Dept' and 'Employees' respectively and display and display in
worksheet 'Main'
An empty department number column means get all departments and it's related
employees and display in worksheet 'Main'
b. Clear: Clears all cells related to data display. Below buttons on point (c) are disabled.
c. For Department : << Goto Last record, < Previous, > Next, >> Last record

Single value search worksheet 'Main':

1. In 'Department Number' column when you enter a department number eg.10.
When You press 'Find' button: It should go to worksheet 'Dept',
search the corresponding value of '10' in Table 'TDept' column 'Deptno'
get it's row details and display here in section 'Department's respective cells.
2. Go to worksheet 'Employees' search the corresponding value of '10' in
table 'TEmployee', and get all the rows from that table where deptno=10
and display here in section 'Employees'

All search

1. When the 'Department Number' cell is empty and you press button 'Find'
it should display all the row from worksheet 'Dept' Table 'TDept' one at a time.
2. Step 2 from Single search
3. Working of buttons only for section 'Department' <<, <, >, >> :
When you are on the last record buttons > and >> should be disabled
When you are on the first record buttons << and < should be disabled
When there are records buttons < and > should be enabled

 

[Luke M]

Hi Don,

You can certainly do this with a few VB codes. The department part is a simple lookup type query. The data based on dept # could be an adv. filter data pull.
Example: http://www.contextures.com/exceladvancedfiltervba.html

Before tackling that, I might suggest the "keep it simple" solution of using the built in forms menu. Let's your scroll through entries, and supports entering criteria(s)


The Form command isn't natively on the Ribbon, you can customize the ribbon and add it to the QAT