From Robinson Junction

Ryan: StatmanNotes

Statman Notes


Bug Fixes

Play Entry Sheet - Comment errors

  1. Cell Z6 comment - "V and W" needs changed to say "W and X".
  2. Cell AL6 comment - "AO" needs changed to say "AQ".
  3. Cell AN6 comment - "AO" needs changed to say "AQ".
  4. Cell AQ comment - "AK or AM" needs changed to say "AN or AO".

Customizations

Play Entry Sheet - Display background pattern on odd rows

  1. Highlight cells A8:BY303. The format will apply to this area.
  2. In the "Home" section of the ribbon (top of Excel), click the "Conditional Formatting" button.
  3. In the drop down menu, click on "New Rule".
  4. A small menu screen will appear. Click on the last arrow that says "Use a formula to determine which cells to format".
  5. In the "Format values where this formula is true" section, type in:
    =MOD(ROW(),2)
  1. Click the format button and set the fill pattern style to "12.5% Gray".
  2. Click OK to close the Format Cells window.
  3. Click OK to close the New Formatting Rule window.

Play Entry Sheet - Automatically draw red lines around current entry row

  1. Highlight cells A8:BY303. The format will apply to this area.
  2. In the "Home" section of the ribbon (top of Excel), click the "Conditional Formatting" button.
  3. In the drop down menu, click on "New Rule".
  4. A small menu screen will appear. Click on the last arrow that says "Use a formula to determine which cells to format".
  5. In the "Format values where this formula is true" section, type in:
    =AND(ISBLANK($M9),ISBLANK($N9),ISBLANK($O9),NOT(AND(ISBLANK($M8),ISBLANK($N8),ISBLANK($O8))))
  1. Click the format button and set the top and bottom borders to a solid red line.
  2. Click OK to close the Format Cells window.
  3. Click OK to close the New Formatting Rule window.
And
  1. Highlight cells A8:BY8. The format will apply to this row only.
  2. In the "Home" section of the ribbon (top of Excel), click the "Conditional Formatting" button.
  3. In the drop down menu, click on "New Rule".
  4. A small menu screen will appear. Click on the last arrow that says "Use a formula to determine which cells to format".
  5. In the "Format values where this formula is true" section, type in:
    =AND(ISBLANK($M8),ISBLANK($N8),ISBLANK($O8))
  1. Click the format button and set the top and bottom borders to a solid red line.
  2. Click OK to close the Format Cells window.
  3. Click OK to close the New Formatting Rule window.

Add headings and page numbers to printed reports

The following steps must be done for each printable sheet. To see a list of printable sheets, select the Print tab and click on the "Click here to Print Reports" button. A "Statman Print Macro" window will appear. You must close the window before proceeding.

  1. At the bottom of Excel, click on the tab that corresponds to the printable sheet.
  2. In the "Page Layout" section of the ribbon (top of Excel), click the "Page Setup" arrow in the lower right corner. A "Page Setup" dialog will appear.
  3. Click on the "Margins" tab.
  4. Continually click the down arrow on the right side of the "Header" margin entry box until the setting is 0.
  5. Click on the "Header/Footer" tab.
  6. In the Header drop down menu, select the option that contains the sheet name and page number.
  7. Click OK to close the Page Setup window.

Add Black and White print option

  1. Press ALT-F11. This will open the Visual Basic Editor.
  2. Select UserForm1.
  3. Make the UserForm1 object look as below.
    1. Copy the "Game Stats" object to create the "Black & white" object.
    2. In the checkbox properties windows, rename the new object to "CheckBoxBW" and change the caption to "Black & white".
  1. In the project navigation window, right click on UserForm1 and select View Code.
  2. Edit each If-CheckBox block to look like the following example.
        If CheckBox1.Value Then
            ActiveWorkbook.Application.Sheets(Checkbox1.Caption).PageSetup.BlackAndWhite = CheckBoxBW.Value
            ActiveWorkbook.Application.Sheets(CheckBox1.Caption).PrintOut copies:=1, Collate:=True
        End If
  1. Exit the editor and save the spreadsheet.
Retrieved from https://robinsonjunction.org/index.php?n=Ryan.StatmanNotes
Page last modified on September 09, 2014, at 05:48 AM