Dear fellow members,
Currently I'm developing a toolkit concerning future Dutch fire safety legislation that will be in effect as of January 1 2012. It's a school project to finish my study building engineering.
In this toolkit I'll have to select different user functions, as they call it over here. These user functions can be categorized in main user functions and sub user functions. The tricky part is that the sub user functions can vary in each chapter. Some chapters only use main user functions, other chapters divide the same main user function in sub user functions a and b while other chapters divide the same user function in sub user function x and y. The way I want to solve this is to use different validation lists for each chapter. First I want to make a rough analysis of a random building in tab "Report (1)". In the example used in the attached file I want to determine how many people are allowed in a building. This information is placed in table "Occupation" in tab "Report (2)". After I defined which main user functions are applied in "Report (1)" I want Excel to put all these main user functions in a list in table "Occupation" directly underneath each other.
Another problem is that I included a macro in "Report (1)" that can add more user functions. Adding more user functions changes the range in which Excel has to search for the main user functions. Also the range is determined by how many spaces/rooms each userfunction has. I do know a thing or two about Excel but this is the first time I'm using VBA. Initially I wanted to avoid this but using standard Excel formulas and functions such as a standard validation lists brings to much limitations to the things I want.
The process can be displayed as follows:
Tab: "Report (1)"
Input: Main user functions.
↓
Tab: "Report (2)"
Action: Number of validation lists is equal to number of main user functions in "Report (1)".
Initial values of validation lists are set to main user functions chosen in "Report (1)"
↓
Afterwards values can be set to required sub user functions which can vary in each chapter.
I read something about a technique called dependent validation. I've tried it but that's not really what I'm looking for. I want to make this toolkit as efficient as possible so I want to keep the whole thing as compact as possible. I'm also aware that there's a possibility to make a complete list of all user functions including all sub user functions used throughout the whole legislation document. It would save me some time because I wouldn't have to make different lists for each chapter. But I'm afraid that the whole usage of this toolkit will become a bit confusing if people don't know which specific sub user functions are applied in each chapter. So I want to figure out if there is a way to filter out all these sub user functions.
I practically don’t know anything about the usage of VBA but I thought over some possible solutions:
- Using different validations lists for each chapter. Initial value is set to main user functions chosen in “Report (1)”.
- Using one type of validation list. All the main user functions defined in “Report (1)” are copied with the selected value and inserted in “Report (2)”. The values can than be set to the required sub user function. (As described earlier I want to avoid this option.)
- Using different validations lists for each chapter. All the main user functions defined in “Report (1)” are copied with the selected value and inserted in “Report (2)”. By adding an extra column the main user function can be refined to the wanted sub user function using dependent validation lists. (This is also an option I don’t prefer.)
- Using ActiveX-elements, for example choice boxes. I’m aware that this option gives a lot of possibilities. However, due to my limited knowledge of VBA it wouldn’t be wise to make this even more complicated than it already is. Nevertheless, if this is the only way I'm very willing to give it a try.
I have to admit that I may be a bit out of my league here but if I have one useful example I can apply it throughout my whole Excel file adjusting it to every chapter. Or if someone could redirect me to some useful information elswhere, I'd be very thankful as well. Maybe I'm making this more difficult than it is. If someone has a whole other approach in mind please enlighten me. I'm also aware that this forum isn't meant to ask for complete solutions to ones problems but if anyone has a bit of useful information, any information at all, I'd be very grateful.
If someone wants to take a look at the workbook in progress, contact me and I'll send you the file. I've already translated a view words in the workbook to make the relevant information as understandable as possible.
Many thanks in advance!
Greetings from the “Lowlands”
Currently I'm developing a toolkit concerning future Dutch fire safety legislation that will be in effect as of January 1 2012. It's a school project to finish my study building engineering.
In this toolkit I'll have to select different user functions, as they call it over here. These user functions can be categorized in main user functions and sub user functions. The tricky part is that the sub user functions can vary in each chapter. Some chapters only use main user functions, other chapters divide the same main user function in sub user functions a and b while other chapters divide the same user function in sub user function x and y. The way I want to solve this is to use different validation lists for each chapter. First I want to make a rough analysis of a random building in tab "Report (1)". In the example used in the attached file I want to determine how many people are allowed in a building. This information is placed in table "Occupation" in tab "Report (2)". After I defined which main user functions are applied in "Report (1)" I want Excel to put all these main user functions in a list in table "Occupation" directly underneath each other.
Another problem is that I included a macro in "Report (1)" that can add more user functions. Adding more user functions changes the range in which Excel has to search for the main user functions. Also the range is determined by how many spaces/rooms each userfunction has. I do know a thing or two about Excel but this is the first time I'm using VBA. Initially I wanted to avoid this but using standard Excel formulas and functions such as a standard validation lists brings to much limitations to the things I want.
The process can be displayed as follows:
Tab: "Report (1)"
Input: Main user functions.
↓
Tab: "Report (2)"
Action: Number of validation lists is equal to number of main user functions in "Report (1)".
Initial values of validation lists are set to main user functions chosen in "Report (1)"
↓
Afterwards values can be set to required sub user functions which can vary in each chapter.
I read something about a technique called dependent validation. I've tried it but that's not really what I'm looking for. I want to make this toolkit as efficient as possible so I want to keep the whole thing as compact as possible. I'm also aware that there's a possibility to make a complete list of all user functions including all sub user functions used throughout the whole legislation document. It would save me some time because I wouldn't have to make different lists for each chapter. But I'm afraid that the whole usage of this toolkit will become a bit confusing if people don't know which specific sub user functions are applied in each chapter. So I want to figure out if there is a way to filter out all these sub user functions.
I practically don’t know anything about the usage of VBA but I thought over some possible solutions:
- Using different validations lists for each chapter. Initial value is set to main user functions chosen in “Report (1)”.
- Using one type of validation list. All the main user functions defined in “Report (1)” are copied with the selected value and inserted in “Report (2)”. The values can than be set to the required sub user function. (As described earlier I want to avoid this option.)
- Using different validations lists for each chapter. All the main user functions defined in “Report (1)” are copied with the selected value and inserted in “Report (2)”. By adding an extra column the main user function can be refined to the wanted sub user function using dependent validation lists. (This is also an option I don’t prefer.)
- Using ActiveX-elements, for example choice boxes. I’m aware that this option gives a lot of possibilities. However, due to my limited knowledge of VBA it wouldn’t be wise to make this even more complicated than it already is. Nevertheless, if this is the only way I'm very willing to give it a try.
I have to admit that I may be a bit out of my league here but if I have one useful example I can apply it throughout my whole Excel file adjusting it to every chapter. Or if someone could redirect me to some useful information elswhere, I'd be very thankful as well. Maybe I'm making this more difficult than it is. If someone has a whole other approach in mind please enlighten me. I'm also aware that this forum isn't meant to ask for complete solutions to ones problems but if anyone has a bit of useful information, any information at all, I'd be very grateful.
If someone wants to take a look at the workbook in progress, contact me and I'll send you the file. I've already translated a view words in the workbook to make the relevant information as understandable as possible.
Many thanks in advance!
Greetings from the “Lowlands”
