Insurance regulations require legal reserves and surplus bec…

Questions

A mоther hаs а primаry HSV infectiоn during her pregnancy. Her newbоrn infant has a positive IgM and a positive IgG for HSV, which indicates:

Here is the spreаdsheet templаte. Exаm 2 Part 2 Template - F25.xlsm Yоu shоuld dоwnload this file to your computer and do all your work on it. I suggest you rename it with your name. 1)   Use the worksheet named Data for the following a.  (4 pts) Add a button control below the Question1 button control on the Data worksheet. The button should have the following properties:I.    Name = cmdQ2II.    Caption = Question2III.    Font = 10 point Times RomanIV.    Top (distance from the top of the screen) = 71.25 b. (4 pts) Add a scroll bar named scrBar1 below the Question4 button. The values should range from 1 to 60 with the small change of 3 and the large change of 6. Use 2 labels to show the range of values. c. (4 pts) Add VBA code to the Question1 button. When Question1 button is clicked, the code should add the value of the scroll bar to cell b6. d  (4 pts) Add VBA code to the Question2 button. When the Question2 button is clicked, the code will do the following:I.     Put into cell e2 the total of cells b2, c2 and d2.II.     If the value in cell b2 is less than the value in cell c2 and the value in cell c2 is less than the value in cell d2, display an Inputbox that prompts the user for a number and put the number in cell f2.  If not, put a 0 in cell f2. e  (4 pts) Add VBA code to the Question3 button. When the Question3 button is clicked, the code will do the following:I.     Display a message box that says “Valid Input” if the value in b4 is July. Ignore leading / trailing blanks and different cases (upper/lower). So july or JULY or JuLy are all valid.  f.  (4 pts) Add VBA code to the Question4 button. When the Question4 button is clicked, the code will put a random number in the range -15 to 35 inclusive in cell b7. g.  (4 pts) Create a macro named highlight that has a shortcut of Ctl-u. When run, the macro will do the following for cells a1 through h2:I.    Make the characters/numbers Bold and make the background light blue 2)  On the worksheet Numbers, write VBA code that will do the following on the click of the each of the Problem2 buttons. Your code should work for any number of values in column A, not just 22. Please follow the instructions for each question regarding whether you may use Excel Functions or not. However, do NOT record macros to get the code.  a. (4 pts) Problem2a button - In cell h1, put the total of the cells in column A. In cell h2, put the maximum value of cells in column A. Use Excel functions in your VBA code to accomplish this. b. (4 pts) Problem2b button – In cell h3 put the total of every 4th cell (a4, a8, a12, etc.)  Be sure that each time the button is clicked, the value in h3 is just the total of the cells (from one click of the button). Do NOT do this in one statement by just adding 5 cells together. c. (4 pts) Problem2c button - In cell i6 (on the Numbers worksheet), put the total of cells a6 to a15 from the Other worksheet. Do not use the Excel function to do this in one statement but instead use a Loop. Be sure that each time the button is clicked, the value in i6 is just the total of the 10 cells (from one click of the button). d. (4 pts) Problem2d button - For each value in column B, put the word big in column D if the value is less than 10, bigger if greater than or equal to 10 and less than 20, or biggest if it is greater than 19. For example, d1 should be big, and d2 biggest. e. (5 pts) Problem2e button – In cell i2, put the number of times the value of the cell in column A is less than the value in column B, for the same row. In cell i3, put the number of times the value of the cell in column A is greater than the value in column B, for the same row. In cell i4 put the number of times the value of the cell in column A is the same as the value in column B, for the same row. Be sure that each time the button is clicked the values in i2, i3, and i4 are just the total from that one click of the button. Do NOT use the Excel function to do this in one statement but instead use a Loop. f.  (5 pts) Problem2f button – In cells j1 to jJ22, copy cells a1 to a22 in reverse order. That is, cell j22 gets the value from cell a1, j21 gets a2, etc. For this problem only you can assume there are only values in a1 to a22. You must use a Do While to accomplish this. g. (4 pts) Problem2g button – In cell h6, display the number of times any of the buttons on this worksheet has been clicked. Include in this count the clicking of button Problem2g. In cell h7 display the total number of times buttons Problem2a, Problem2e and Problem2f have been clicked.