I am replicating a risk assessment form in excel. I would like to have a userform for data entry and calculation of values and then print a completed form for signature. I am learning VBA and am making good progress but have a ways to go still. I am not trying to get the form designed and completed by anyone just looking for a way to get started with some of the sections. Of particular concern are the sections where there are three columns and only the highest column total is used. I'm also still trying to work out how, on designated entries, they automatically make the overall risk a medium or high regardless of total risk value. I can not decide if I should utilize VBA to calculate everything or maybe create a userform that enters data onto a hidden sheet where it is calculated and then sent to the actual form that gets printed. I am looking to utilize good practices so that when I move to a different position the form can be modified and updated as needed. Hopefully this isn't too cryptic and with the attached pdf original someone will have an idea of what I am attempting to do.
Userform to calculate Risk Assesment
- Thread starter Wid2001
- Start date
NARAYANK991
Excel Ninja
Hi ,
It is good that you have uploaded a document , since it gives everyone an idea of what is involved.
However , it is generally not advisable to transfer a paper document directly into a software data entry form , whether userform-based or Excel worksheet-based.
Let us start with a few basic questions :
1. Will all of the data entry be done by one person ?
2. Will all of the data entry be done at one go ?
3. Are all of the sections totally independent , or is there information which when entered in one section needs to be transferred to other sections or at least be available to the user who is doing the data entry ? This can mean that either all of the data entry needs to be within one screen , or certain fields might need to be copied and presented to the user when other sections of the form are being filled in.
4. Is there any data validation that you would want done ? Let us start with the basic ones viz. do all fields need to be filled in , or can some fields be left blank ? What are the available choices when the user is filling in , or what are the lower and upper limits for numeric entries ? The more validation there is , the better.
5. How much of the entered information needs to be stored , and if so , in what format ? Rather than store the data in the format it is entered , it would be better to organize the storage so that it can be easily manipulated / processed when the time comes for it to be presented in the desired formats.
I think others can come up with more points , but let us start with these and go forward.
Narayan
It is good that you have uploaded a document , since it gives everyone an idea of what is involved.
However , it is generally not advisable to transfer a paper document directly into a software data entry form , whether userform-based or Excel worksheet-based.
Let us start with a few basic questions :
1. Will all of the data entry be done by one person ?
2. Will all of the data entry be done at one go ?
3. Are all of the sections totally independent , or is there information which when entered in one section needs to be transferred to other sections or at least be available to the user who is doing the data entry ? This can mean that either all of the data entry needs to be within one screen , or certain fields might need to be copied and presented to the user when other sections of the form are being filled in.
4. Is there any data validation that you would want done ? Let us start with the basic ones viz. do all fields need to be filled in , or can some fields be left blank ? What are the available choices when the user is filling in , or what are the lower and upper limits for numeric entries ? The more validation there is , the better.
5. How much of the entered information needs to be stored , and if so , in what format ? Rather than store the data in the format it is entered , it would be better to organize the storage so that it can be easily manipulated / processed when the time comes for it to be presented in the desired formats.
I think others can come up with more points , but let us start with these and go forward.
Narayan
Thank you very much for the reply. Your help to this point has already been invaluable and has helped me better my ability working with excel. Hopefully this will better clarify.
The attachment is of the pdf form filled out for signature
1. Each form will be done by 1 individual but that individual will vary.
2. All data entry will be done at one time.
3. The sections are independent but some sections (moon illumination for example) Can be calculated based on moonrise, moonset, and takeoff time generated elsewhere in the workbook) The values in the boxes on the attached sample are fixed. For example terrain flight always has a risk value of "2". When checked it adds 2 to the risk total for that section. I was envisioning, maybe a multi-page control with different sections of this form on it. If multiple sections can fit they can occupy the same page. On each page would be a label for the mode of flight and the risk value " Terrain Flight--2" with an associated checkbox. When checked it would populate the total for that section, much like the attached example, as well as the final page being the "Risk Value Totals" section. This section would total all the sections almost identical to the attached and then maybe if possible circle the risk, low, moderate, high etc.
4. The information once completed will be printed and then signed. No need to store electronically or in a database although would be good for possible future growth.
I know this is a lot to explain and I am trying to work on it one piece at a time but this forum has been a wealth of information to me and I thought I might be more efficient posting my own questions for mentorship and help. I am however attempting to conduct my own research and educate myself rather that just rely on everyone to do it for me.
Thanks again for your time and effort.
Spence
The attachment is of the pdf form filled out for signature
1. Each form will be done by 1 individual but that individual will vary.
2. All data entry will be done at one time.
3. The sections are independent but some sections (moon illumination for example) Can be calculated based on moonrise, moonset, and takeoff time generated elsewhere in the workbook) The values in the boxes on the attached sample are fixed. For example terrain flight always has a risk value of "2". When checked it adds 2 to the risk total for that section. I was envisioning, maybe a multi-page control with different sections of this form on it. If multiple sections can fit they can occupy the same page. On each page would be a label for the mode of flight and the risk value " Terrain Flight--2" with an associated checkbox. When checked it would populate the total for that section, much like the attached example, as well as the final page being the "Risk Value Totals" section. This section would total all the sections almost identical to the attached and then maybe if possible circle the risk, low, moderate, high etc.
4. The information once completed will be printed and then signed. No need to store electronically or in a database although would be good for possible future growth.
I know this is a lot to explain and I am trying to work on it one piece at a time but this forum has been a wealth of information to me and I thought I might be more efficient posting my own questions for mentorship and help. I am however attempting to conduct my own research and educate myself rather that just rely on everyone to do it for me.
Thanks again for your time and effort.
Spence
Attachments
NARAYANK991
Excel Ninja
Hi Spence ,
Good that you have uploaded a filled in document.
Let me ask questions of this , without having gone through what you have posted , since otherwise I may think I know when actually I don't.
If we consider the sections 1 through 8 , each of them has a first line which is divided into 6 sections ; what are these for ?
Along with data entry in each individual section , the first line also has a number in it ; this number is based on some numbers which are circled in each section ; what is the significance of this circling , who does it , and how will this be done in the Excel-based system ?
Section 9 appears to be an entirely textual section ; what kind of text and how much of it will be entered in this section ?
Sections 10 and 11 appear to be a summary of the first 8 sections ; does this mean that no data entry will be done in these two sections ?
If you have any additional information which I might not have thought of , please put that down in your post.
Narayan
Good that you have uploaded a filled in document.
Let me ask questions of this , without having gone through what you have posted , since otherwise I may think I know when actually I don't.
If we consider the sections 1 through 8 , each of them has a first line which is divided into 6 sections ; what are these for ?
Along with data entry in each individual section , the first line also has a number in it ; this number is based on some numbers which are circled in each section ; what is the significance of this circling , who does it , and how will this be done in the Excel-based system ?
Section 9 appears to be an entirely textual section ; what kind of text and how much of it will be entered in this section ?
Sections 10 and 11 appear to be a summary of the first 8 sections ; does this mean that no data entry will be done in these two sections ?
If you have any additional information which I might not have thought of , please put that down in your post.
Narayan
This assessment is for up to six crews. The first line would represent the total for that section each for up to six crews. If there is only one crew the others will be left blank as shown in he example. Each section has the individual mode or task. Each of those has a fixed value representing a level of risk (points). The circled numbers represent the modes or tasks that will be done for his particular example and the total on the first line represents the sum of all for that section. Section 9 would indeed just be a text box for the Approver to write in additional comments. Section 10 just represents the totals from each section and then totals them together and section 11 represents the risk level based on the total. Eg ghost form totaled to 38 which is identified as a "medium" in section 11.
NARAYANK991
Excel Ninja
Hi Spence ,
If each section can be filled up for up to 6 crews , where would these numbers appear ? I can see that all the 6 sections in the first line would be filled up , but how would the individual crew numbers be distinguished one from the other ? For instance in section 3 , the numbers 2 , 6 and 3 have been circled and the total of 11 has been entered against crew #1 ; suppose two more crews were to have their numbers for this section filled up , I can understand that the first line would have the totals , but how would the individual choices for the two crews be distinguished from the choices of 2 , 6 and 3 for crew #1 ?
Narayan
If each section can be filled up for up to 6 crews , where would these numbers appear ? I can see that all the 6 sections in the first line would be filled up , but how would the individual crew numbers be distinguished one from the other ? For instance in section 3 , the numbers 2 , 6 and 3 have been circled and the total of 11 has been entered against crew #1 ; suppose two more crews were to have their numbers for this section filled up , I can understand that the first line would have the totals , but how would the individual choices for the two crews be distinguished from the choices of 2 , 6 and 3 for crew #1 ?
Narayan