CP212 Lab 7 : Userforms

Practice

• Create a form that allows the user to play a simple number guessing game such as the one created from last week. If you completed this game last week you can use it as a starting point.
• Create a login form like the one used on websites which contains a username field, a password field, a login button, and a cancel button, as well as a button that can be used if the password is forgotten.
  • A textbox has a property named PasswordChar, which is a character that can be used to block out the password field so it can't be read. A typical password character is the asterisk (*) but you can choose another if you like.
  • If the user enters the correct password, display a message.
  • If the user enters the wrong password, also display a message.
  • Extra Mile: Develop a way to have multiple users and passwords recognized by your login form.
  • From the VBE, click File / Export to export your login userform for future use.

The image shown below has had a few other changes made to it, but you can create your form however you want.

---

Instructions

• Save the spreadsheet file as a Macro Enabled Workbook (.xlsm) file before you write more code. Name your spreadsheet with your network login followed by _l07.xlsm. For example, if your network login was barn4520, then your lab file should be named barn4520_l07.xlsm. Save often.

• All tasks are to be completed on your own.

• Show the Developer Tab in Excel. Click File / Options Customize Ribbon and check the option for Developer Tab.

• Put all code into a Module. Use a different Module for each task and name them appropriately as Task1 or Task2 etc.

• Copy and paste this header into your code modules and fill it out:

  Option Explicit
  
  ' ==== CP212 Windows Application Programming ===============+
  ' Name: Your Name
  ' Student ID:
  ' Date:
  ' Program title:
  ' Description:
  '===========================================================+
  

• Make sure you test your tasks before submitting it to the Dropbox be marked.

• When finished upload your file to the Lab 7 Task Dropbox in MyLearingSpace.

---

Lab Tasks

1. Create the userform shown below:
   



• The form should contain a the usual OK and Cancel buttons.
• The form also contains a list box whic allows the user to select only one item from the list.
• Write a sub Populate that populates the list with the provinces Alberta, British Columbia, Manitoba, New Brunswick, Newfoundland and Labrador, Nova Scotia, Ontario, Prince Edward Island, Quebec, and Saskatchewan in thie same order. Select Ontario as default.
• Write a code in a module ShowForm so that the user sees the form. The user either select an item from the list or clicks Cancel. When a province is selected from the list as shown below:



, and then clicks OK, the user should see a message as,"You live in Ontario"



• While when the Cancel or X button is clicked, the message "You must not live in a Big Ten Provinces" should appear:

  

• Your excel worksheet should have a button to run a module and access the user form. Based on the user form, user either selects a province then a message showing a province , or selects, Cancel or X the response will show a message of "You must not live in a Big Ten Provinces"

When the user tries to close the form by clicking the upper right X, the following sub can be used

Private Sub UserForm_QueryClose(cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then cmdCancel_Click
End Sub

2. Create the userform shown below:

   

This form contains a frame, labels, a RefEdit control, two buttons, and a scrollbar. When you click the Sum button, it should display a message box indicating the Sum of the selected cells.

Note: The RefEdit control has a Value property which returns a string containing the address of the selected range. To create a range object from this address use a line like:

Dim range1 As Range

set range1 = Range(refEdit.Value)

When you click the Cancel button, the form should close/unload. Use the Accelerator property for each button to include the HotKey (displays the underscore and allows that key to execute the button when pressing Alt.)

To activate the scrollbar, you need to access and modify its properties such as ScrollTop and ScrollHeight. Please note that scrollbar can be activated by assigning appropriate value to the ScrollBars property of the form.

Note

You have to use Button(s) on worksheet to run the tasks in this lab and all next labs and assignments. Marks will be deducted if you do not use Buttons

You can use only the covered material in the course till now. You can't use uncovered topics. You can't use topics out of the context of this course.