CCE 170 - Final Exam KEY

Fall Semester 2021

32. The following spreadsheet is designed to plot a curve defined by the two columns labeled x and y.

The objective of this exercise is to write VB code to generate a new plot using a subset of the Excel charting options. The new plotting options will be in a custom user form that looks like this:

Click here to download a copy of this spreadsheet (your copy will not have a plot). Then complete the following exercise. Follow all instructions carefully.

Click here to download a copy of the solution.

(a). (forms) (4 pts) Create a custom user form using the pattern shown above. Name your form frmQuickPlot. Give each of the controls in the form an appropriate name.

(b). (forms) (1.5 pts) Change the code for the Quick Plot button on the spreadsheet so that it launches your form.

(c). (forms) (1.5 pts) Add code to the Cancel button on your form so that it closes the form.

(d). (macros) (1.5 pts) Record a macro called make_plot that creates an XY scatter plot. As you record your macro, make sure you do the following steps, in order. While recording, make sure you do NOT change the position of the chart.

1. Start recording.
2. Select the Y column (C4:C24). Include the "Y" cell at the top.
3. Select the Insert tab, click on the Scatter option in the Charts section, and select one of the chart types that includes lines
4. Click the "+" symbol just to the upper-right side of the new chart and turn on the Legend option.
5. Right click on the curve (y series) in the new plot and click on the Select Data command.
6. Click on the Edit button, put the cursor in the Series x values: field, and drag to select the x column on the worksheet (B5:B24). Click OK twice to exit the dialogs.
7. Click on the title and change it to something else ("My Chart" for example).
8. Stop recording.

(e). (subs) (4 pts) Add VB code for the OK button on your form.

First of all, your code should first close the form.

Next, your code should created a new chart using your macro code. Cut and paste the code from the make_plot macro to your cmdOK_Click sub and modify the code so that it uses the status of the controls in the user form to generate a new plot with the selected settings. Modify the code as follows:

(i) Find each line that looks like this:

ActiveSheet.ChartObjects("Chart 1").Activate

and delete each one. They aren't necessary and they will cause an error after you create your second plot.

(ii) Change the

ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select

part of your code so that it is embedded within an If statement. Your code should change the xlXYScatter... constant (second argument) to generate one of the following four types of plots, depending on the options selected by the user:

ChartType	Line Option	Markers?
xlXYScatterLines	Straight Lines	Yes
xlXYScatterLinesNoMarkers	Straight Lines	No
xlXYScatterSmooth	Smooth Lines	Yes
xlXYScatterSmoothNoMarkers	Smooth Lines	No

(iii) Find the line of code that turns on the legend. Embed this line in an If statement and add the legend if the Legend toggle is selected.

(iv) Change the code that sets the chart title so that it uses the title input by the user from the form. This might involve two lines of code.

(v) You should have two (or three) blocks of code associated with a With statement that looks something like this (should be right after the code that changes the title):

With Selection.Format.TextFrame2.TextRange.Characters(1, 8).ParagraphFormat
  (some statements here)
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 8).Font
  (some statements here)
End With

Delete both blocks of code.

Test your code to make sure it works.

(Upload instructions and links went here)