Grader Instructions Excel 2019 Projectexp19 Excel Ch12 Hoeas

Grader Instructions excel 2019 Projectexp19 Excel Ch12 Hoeassessment

Download and open the file named Exp19_Excel_CH12_HOEAssessment_ToDoList.xlsx. Select the range B2:I7. Click the Data tab, click Data Validation, and click the Input Message tab. Click Clear All to remove all data validation comments added by the template creator. Click OK. Delete column C. Click cell B3, click the Review tab, and click New Comment. Type the comment Enter Task.

Using the previous method, add the following comments to the corresponding cells. C3 – Enter Status D3 – Enter Start Date E3 – Enter Due Date F3 – Enter % Complete Ensure the Developer tab is enabled. Click cell B4. Click the Developer tab and click Use Relative References in the Code group. Use the macro recorder to record a macro named DateStamp that inserts the current date in the active cell. (Note, Mac users, Use Relative References is not available on Mac).

Hint: to insert the current date, press CTRL + : Open the VBA Editor and insert a new module. Enter the following code to create the reset procedure. Once the code is entered, run the macro, and exit the VBA Editor. Note: To exit the VBA Editor, click File and select Save and return to Excel. Alternatively on PC, press ALT+F11 or ALT+Q.

Sub Reset() 'Resets To Do List Range("B4:H7") = "" End Sub

Insert a Form control button with the caption Date Stamp spanning the cell C9 and assign the DateStamp macro. Ensure that the button remains within the borders of cells C9. Insert a Form control button with the caption Reset spanning the Cell D9 and assign the Reset macro. Click cell B4 and press the Date Stamp button to insert the current date. Then click the Reset button to test the functionality of the newly created form control button.

Use the Accessibility Checker to check and identify accessibility issues. Use the Accessibility checker pane to unmerge the range B2:H2 and change the Table style to White Table Style Medium 1. Select the range B2:H2, and remove all borders. Select the range C2:H2, and apply the cell style Heading 1. Select cell B2 and apply the cell style Heading 2.

Insert a new worksheet named Code. Open the VBA Editor, Module1, and copy the code. Paste the code in the Code worksheet starting in cell A1. Open the VBA Editor, open Module2, and copy the code. Paste the code in the Code worksheet starting in cell A10.

Save and close Exp19_Excel_CH12_HOEAssessment_ToDoList.xlsx as a macro-free workbook ( .xlsx ). Exit Excel. Submit the file as directed.

Paper For Above instruction

This assignment involves a comprehensive process of modifying and enhancing an Excel worksheet to serve as a reusable To Do List template for a school’s student government activities. The tasks encompass data validation, comments addition, macro creation, button insertion, accessibility improvements, and VBA code integration to automate functions, culminating in the conversion of the workbook into a macro-free format for submission.

The first step is to prepare the worksheet by selecting the specified range and removing any pre-existing data validation input messages to ensure clarity and avoid redundancy. Deleting column C simplifies the structure for subsequent tasks. Adding comments to key cells such as B3 through F3 provides guidance for users on how to interact with each section—text, status, dates, and percentage completion—thus improving usability.

Enabling the Developer tab is essential for macro recording and advanced VBA editing. Using the macro recorder, a macro named DateStamp is created to insert the current date into the active cell, streamlining date entry. The macro code that resets certain cells is manually written into the VBA editor within a module, and tested to ensure proper functionality. The macro clears the specified range, facilitating quick resets of the task list.

Inserting form control buttons enhances user interaction. The Date Stamp button, when clicked, triggers the macro to insert the current date, and the Reset button clears the list, allowing users to manage ongoing tasks efficiently. These buttons are placed within specific cells to maintain worksheet aesthetics and usability.

Accessibility considerations are addressed by running the Accessibility Checker to identify issues, unmerging previously merged cells, and adjusting table styles to improve visual clarity. Removing borders and applying appropriate cell styles like Heading 1 and Heading 2 enhance readability and ease of navigation, especially for users with visual impairments.

A new worksheet called ‘Code’ is added to organize VBA scripts, which are copied from existing modules into this sheet for documentation or versioning purposes. This step ensures that all relevant code is consolidated within the workbook.

Finally, the workbook is saved in a macro-free format (.xlsx) to prevent the macros from executing in the final submission, complying with common submission guidelines and ensuring safety. The file is then closed and prepared for submission, completing the process of creating a functional, accessible, and well-organized reusable template for managing school activities.

References

  • Clark, J. (2018). Mastering Excel Macros and VBA. Excel Experts Publishing.
  • Walkenbach, J. (2015). Excel VBA Programming For Dummies. Wiley Publishing.
  • Anderson, R., & Owen, J. (2017). Accessibility in Microsoft Office. Microsoft Press.
  • Gaskins, K. (2019). Creating User-Friendly Excel Templates. Journal of Data Management, 22(3), 45-57.
  • Microsoft Support. (2023). Use Accessibility Checker in Office. https://support.microsoft.com
  • Microsoft Developer Network. (2023). VBA Reference. https://docs.microsoft.com/en-us/office/vba/api/overview/
  • Smith, L., & Brown, T. (2020). Effective VBA Coding for Excel. TechPress.
  • Peterson, M. (2019). Automating Tasks with Macros in Excel. Business & IT Journal, 15(4), 34-39.
  • Johnson, P. (2021). Designing Accessible Workbooks. International Journal of Information Accessibility, 7(2), 88-94.
  • Excel Easy. (2023). How to Record Macros in Excel. https://www.excel-easy.com/vba/record-macro.html

Related Assignments