INF 110 In-Chapter Excel Chapter 6

INF 110 In-Chapter Excel Chapter 6
(Workbook named In-ChapterExcel6LastName.xlsm) (Download VLC Video Player)

Your Turn! (page 219) Create UserForm to say Hello in Label
(Create a Worksheet named HelloUserFormp219
Use the following set of directions as a guideline: (See video)
1. Create the form (Click, Insert UserForm on VBA IDE), add a command button to the form, and type in code on page 219
2.  Name the form frmMessage, the command button on the form cmdHello, and the label on the form lblOutput.
3. To display the form on the spreadsheet put the following code under the Worksheet_SelectionChange event of the spreadsheet. (frmMessage.Show)
4.  To test the code click on a cell in the spreadsheet and click on the command button
5.  The message "Hello!" should appear in the label control on the form.
6. Use the Alt-Print Screen keys to capture the Screen-shot for the form
7.  Exit Run-Mode by clicking the Design View Button on the Developer Ribbon
8.  Paste this screen-shot into the spreadsheet  cell A10 to show that the code is working properly
9.Change the Name of sheet1 tab at bottom to HelloUserFormp219
10.  Save the Workbook as an Excel Macro-Enabled Workbook (*.xlsm) in the Save As dialog box using the name In-ChapterExcel6LastName.xlsm 

Your Turn! (page 221) Modal and Modeless Forms
 (Create a Worksheet named ModalModelessFormsp221)

  1. Create a form, and type in instead of the code on page 221 the code listed below
  2. Name the form frmMyUserForm
  3. Add two command buttons to the spreadsheet not the form called cmdModal, and cmdModeless with Modal and Modeless for their Caption property.
  4. To display the form on the spreadsheet put the following code under each of the two command buttons on the spreadsheet:
  5. To test the code click on each of the command buttons in the spreadsheet a new message should appear in the title area at the top of the form.
  6. Use the Alt-Print Screen keys to capture the Screen-shot for each form and message box (modal and modeless)
  7. Exit Run-Mode by clicking the Design View Button on the Developer Ribbon
  8. Paste this screen-shots into the spreadsheet to show that the code is working properly (4 screen-shots total)

Private Sub cmdModal_Click()
    frmMyUserForm.Caption = "Modal Form Example"
    frmMyUserForm.Show vbModal
    MsgBox ("The message box is displayed after the UserForm is closed.")
End Sub

Private Sub cmdModeless_Click()
    frmMyUserForm.Caption = "Modeless Form Example"
    frmMyUserForm.Show vbModeless
    MsgBox ("The message box is displayed immediately after the UserForm")
End Sub

  1. Change the Name of sheet2 tab at bottom to ModalModelessFormsp221
  2. Save the Workbook as an Excel Macro-Enabled Workbook (*.xlsm) in the Save As dialog box using the name In-ChapterExcel6LastName.xlsm
Your Turn! (page 224) Creating Form with Scrollbar to Activate Prior Worksheets
 (Create a Worksheet named ScrollBarsp224)
  1. Create the form listed below for the code on page 224
  2. name the form frmUserForm
  3. add a scroll bar and label control to the form
  4. change the MIN property of the scroll bar from 0 to 1.
  5. name the scroll bar scrWorksheet
  6. name the label control lblSheets
  7. Also enter the values listed in the diagram below into the Caption property of the label, use 25 blank spaces between each item in label.
  8. Type in code on page 224 under the form object.
  9. To display the form on the spreadsheet put the following code under the Worksheet_SelectionChange event of the spreadsheet. (frmUserForm.Show) 
  10. To test the code click on a cell in the spreadsheet and drag the slider control on the scroll bar, as you move it each of your sheets in the workbook should be selected at the bottom of the screen.
  11. Use the Alt-Print Screen keys to capture the Screen-shot for the form
  12. Exit Run-Mode by clicking the Design View Button on the Developer Ribbon
  13. Paste this screen-shot into the spreadsheet at cell A7 to show that the code is working properly (1 screen-shots total)

  1. Change the Name of sheet3 tab at bottom to ScrollBarsp224
  2. Save the Workbook as an Excel Macro-Enabled Workbook (*.xlsm) in the Save As dialog box using the name In-ChapterExcel6LastName.xlsm   

 

Your Turn! (slides 24 and 25 Excel 6) Creating Form with Listbox displaying contents of cells A1:A10
 (Create a Worksheet named LoadingAListBox)
  1. Create the form containing a list box see slide 25
  2. Name the form frmFirstForm
  3. Add a list box control to the form
  4. Name the list box lstMyList
  5. Add a command button named cmdGo to the spreadsheet in cells (J3:K4) (Used to show form with list box)
  6. Enter the values listed on slide 25 into cells (A1:10) (This is the data for the list box control on the form)
  7. Type in code on slide 24 under the form object and then the command button's click event code on the spreadsheet.
  8. Revise the code to position the form using slide 26
  9. To test the code click on the GO command button in the spreadsheet, the items in cells A1-A10 should now appear in the list box on the form
  10. Use the Alt-Print Screen keys to capture the Screen-shot for the form
  11. Exit Run-Mode by clicking the Design View Button on the Developer Ribbon
  12. Paste this screen-shot into the spreadsheet at cell A12 to show that the code is working properly (1 screen-shots total)
  13. Change the Name of sheet4 tab at bottom to LoadingAListBox
  14. Save the Workbook as an Excel Macro-Enabled Workbook (*.xlsm) in the Save As dialog box using the name In-ChapterExcel6LastName.xlsm   

(Students should submit the electronic version of all worksheets in the workbook using the drop box by clicking on on the InChp6 link under the Assignments link, when you submit the assignment challenges at the end of the chapter. In-Chapter work from this chapter (In-ChapterExcel6LastName.xlsm), each spreadsheet in the In-Chapter Excel workbook should use the following names (HelloUserFormp219, ModalModelessFormsp221, ScrollBarsp224, LoadingAListBox), reflecting the page in the text from which the work comes. Substitute your last name for LastName listed in file names above)