KlingonCoder
New Member
- Joined
- Dec 20, 2011
- Messages
- 3
Hi there, I’m hoping someone may be able to help me solve a problem I’m having. I am quite familiar with Excel, but am just starting to use VBA to achieve the results that I usually use formulas for. I am currently trying to create a workbook in Excel 2003 for tracking personnel data for numerous employees. I have one worksheet that I use for various dynamic lists and named ranges i.e. Departments, Positions, Office Locations, Employee Status etc. The remaining worksheets are laid out so that they are each dedicated to a separate employee’s information.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
ffice
ffice" /><o
></o
>
<o
> </o
>
The situation that is plaguing me is that I am trying to create two cascading combo boxes. The first one is for the “Department” that an employee works for. This combo box is populated by a dynamic list and the second is for the “Position” within the selected Department. To make things even more of a pain, copies of this workbook will be used in different offices which are going to have some “Departments” and “Positions” that are unique to their location, therefore I can’t just create one list to rule them all, sorry bad pun. <o
></o
>
<o
> </o
>
For example, the following offices might have the following departments:<o
></o
>
Office 1: Management, Engineering, and Payroll <o
></o
>
Office 2: Drafting, <?xml:namespace prefix = st1 ns = "urn:schemas:contacts" /><st1:GivenName w:st="on">I.T.</st1:GivenName> and Security<o
></o
>
Office 3: Operations, Sales and Publications<o
></o
>
Head Office: all of the above plus another half dozen departments<o
></o
>
<o
> </o
>
Of course each department will have various positions within it, and the number of positions will vary from department to department.<o
></o
>
<o
> </o
>
I have no problem creating and naming a dynamic list for the departments, nor do I have a problem having the first combo box display those items. Where I am having difficulty is how to arrange my data and populate the second combo box. I originally had a list for the “Departments” and a second list for all “Positions”, which had two columns, one for the Position and the second for the parent Department. However, after reading numerous posts on dependant drop-down lists I tried rearranging my data so that each Department became a heading above a list of the Positions. This layout posed two problems:<o
></o
>
1. The first is that
gave me an error when I tried to use it as the list source for the second dropdown list;<o
></o
>
2. But more importantly, I had no way of properly laying out the data since the Department list is going to vary from site to site, and I cannot trust that the users at each site will get things right<o
></o
>
<o
> </o
>
So after spending several hours trying to find a solution, I am here to plead for assistance. If anyone can suggest how to populate the list source for the second combo box I would be very grateful.<o
></o
>
<o
> </o
>
<o
> </o
>
Rob




<o


The situation that is plaguing me is that I am trying to create two cascading combo boxes. The first one is for the “Department” that an employee works for. This combo box is populated by a dynamic list and the second is for the “Position” within the selected Department. To make things even more of a pain, copies of this workbook will be used in different offices which are going to have some “Departments” and “Positions” that are unique to their location, therefore I can’t just create one list to rule them all, sorry bad pun. <o


<o


For example, the following offices might have the following departments:<o


Office 1: Management, Engineering, and Payroll <o


Office 2: Drafting, <?xml:namespace prefix = st1 ns = "urn:schemas:contacts" /><st1:GivenName w:st="on">I.T.</st1:GivenName> and Security<o


Office 3: Operations, Sales and Publications<o


Head Office: all of the above plus another half dozen departments<o


<o


Of course each department will have various positions within it, and the number of positions will vary from department to department.<o


<o


I have no problem creating and naming a dynamic list for the departments, nor do I have a problem having the first combo box display those items. Where I am having difficulty is how to arrange my data and populate the second combo box. I originally had a list for the “Departments” and a second list for all “Positions”, which had two columns, one for the Position and the second for the parent Department. However, after reading numerous posts on dependant drop-down lists I tried rearranging my data so that each Department became a heading above a list of the Positions. This layout posed two problems:<o


1. The first is that
Code:
=INDIRECT($B$30)


2. But more importantly, I had no way of properly laying out the data since the Department list is going to vary from site to site, and I cannot trust that the users at each site will get things right<o


<o


So after spending several hours trying to find a solution, I am here to plead for assistance. If anyone can suggest how to populate the list source for the second combo box I would be very grateful.<o


<o


<o


Rob