1 / 66

BSIT 1, BES 1 January Semester 2010 Mr. Ronald Ssejjuuko 0712 029 838

Uganda Christian University Faculty of Science and Technology Information Technology II Module II: Advanced Spreadsheets. BSIT 1, BES 1 January Semester 2010 Mr. Ronald Ssejjuuko 0712 029 838 rssejjuuko@technology.ucu.ac.ug. Advanced Spreadsheets.

hiroko
Download Presentation

BSIT 1, BES 1 January Semester 2010 Mr. Ronald Ssejjuuko 0712 029 838

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Uganda Christian UniversityFaculty of Science and TechnologyInformation Technology IIModule II: Advanced Spreadsheets BSIT 1, BES 1 January Semester 2010 Mr. Ronald Ssejjuuko 0712 029 838 rssejjuuko@technology.ucu.ac.ug BSIT 1: Information Technology II

  2. Advanced Spreadsheets • This module is designed for experienced spreadsheet users and will enable delegates to develop effective solutions to business problems using many of the advanced features within the Microsoft Excel Applications Package BSIT 1: Information Technology II

  3. Creating a New Project s and Websites • The first step is to create a new project to contain our example Visual Basic Application. • Start Visual Studio and select File->New project. • From the new project dialog select Windows Application and name the project myVBapp and click on Ok to create the new project. BSIT 1: Information Technology II

  4. Creating a New Project s and Websites • Once the new project is created, Visual Studio will display a blank form ready for us to design the user interface of the application. BSIT 1: Information Technology II

  5. Adding Controls to the Form • For the purposes of our example Visual Basic application we are going to add two controls to our form; a push button and a label. • To achieve this we first need to access the Visual Studio Toolbox. • Along the left hand side of the Visual Studio main window you should see a tab labeled Toolbox. BSIT 1: Information Technology II

  6. Adding Controls to the Form • Click on this tab to display the Toolbox. It should appear as follows: BSIT 1: Information Technology II

  7. Adding Controls to the Form • This Toolbox contains all the controls that may be used to build a Graphical User Interface for a Windows application. • The toolbox will auto-hide by default, that is it will disappear when the mouse pointer is moved away from it. BSIT 1: Information Technology II

  8. Adding Controls to the Form • To make it permanently visible click on the push pin icon at the top of the toolbox window. Once it is pinned in place it will not auto-hide. It is also possible to detach the toolbox so that it will float and can be positioned anywhere on the desktop. To do so, simply click on the toolbox title area and drag it. BSIT 1: Information Technology II

  9. Adding Controls to the Form • Controls are added to the Form by clicking on the required control in the Toolbox and dragging it to the desired location on the Form. To add a label to the form, click on the Label control in the Toolbox, drag it to the center of the Form and release the mouse button. The new label will then appear in the Form at the point you dropped it. BSIT 1: Information Technology II

  10. Adding Controls to the Form • Next we need to add a button. Grab a Button from the Toolbox and drag and drop it on the Form. Use the mouse to move the controls around the Form until you have a layout you are happy with. BSIT 1: Information Technology II

  11. Setting Control Properties • Now that we have added the controls to our Form we need to change some of the characteristics of these controls. • This is done by changing the Properties of the controls. • Properties are a group of settings that allow the appearance and behavior of controls to be changed. BSIT 1: Information Technology II

  12. Setting Control Properties • For example, there is a property for changing the text displayed on a label, the color of a button, the font of the text on a button and so on. Properties of a control are changed using the Visual Studio Properties panel which is, by default, displayed in the bottom right hand corner of the main dialog: BSIT 1: Information Technology II

  13. Setting Control Properties BSIT 1: Information Technology II

  14. Setting Control Properties • The properties displayed at any one time are related to the currently selected control in the Form. If you click on the Label and then the Button in your Form you will see the properties panel change to reflect the current selection. BSIT 1: Information Technology II

  15. Setting Control Properties • To begin with, we will change the text of the Label control. Select the Label control in the form and then scroll down the list of properties until you find Text. • This is the property which defines the text to be displayed on the currently selected Label control. Change this from the current value ('Label1') to read My First VB Application. BSIT 1: Information Technology II

  16. Setting Control Properties • Notice that as soon as you change this property the Label in the Form changes to reflect the new property setting. • Select the Button control in the Form and change the Text Property for this control to read Close. Re-position the controls in the Form if necessary. You should now have a Form which looks something like the following: BSIT 1: Information Technology II

  17. Setting Control Properties BSIT 1: Information Technology II

  18. Creating an Event Handler • The next step is to make the Close button do something when it is pressed. Before we do that, however, we need to give the button a more meaningful name. Visual Studio has given the button a default name of Button1. BSIT 1: Information Technology II

  19. Creating an Event Handler • While this is fine for a small design, it will quickly become difficult to work with such names in larger applications containing many buttons. With the Button selected in the Form, scroll up to the top of the properties list and change (Name) from Button1 to closeButton. BSIT 1: Information Technology II

  20. Creating an Event Handler • Having changed the name we can now add an event to the button. Double click on the Button in the Form to display the event code for the closeButton control. Visual Studio will display the following code: BSIT 1: Information Technology II

  21. Creating an Event Handler • Private Sub closeButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) • Handles closeButton.Click • End Sub BSIT 1: Information Technology II

  22. Creating an Event Handler • This is a Visual basic Subroutine where code is placed to define what happens when a Click event is detected on the button (i.e. the user clicks on the button in the user interface of our application). In this example we want the application to close when the closeButton is pressed. BSIT 1: Information Technology II

  23. Creating an Event Handler • To achieve this we add a single line of Visual Basic code to the closeButton_Click() sub-routine as follows so that the code calls the Close() method to exit the application: Private Sub closeButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles closeButton.Click Close() End Sub BSIT 1: Information Technology II

  24. Building and Running a Visual Basic Application • Now that we have completed the design and implementation of a simple Visual Basic application we can compile and run it. • To build the application, select Build myVBapp from the Visual Studio Build menu. BSIT 1: Information Technology II

  25. Building and Running a Visual Basic Application • Assuming there are no problems the application should compile without any errors (the message Build succeeded should appear in the status bar at the bottom of the Visual Studio screen). BSIT 1: Information Technology II

  26. Building and Running a Visual Basic Application • Once built, the application can be run by selecting Start Debugging from the Debug menu. An even quicker way of building and running the application is to simply press F5. • This will compile and run the application without having to use any menu options. As you develop Visual Basic applications in Visual Studio you will find yourself using the F5 shortcut more than any other key on your keyboard BSIT 1: Information Technology II

  27. Building and Running a Visual Basic Application • After a few seconds the application should appear just as you designed it in the Visual Studio designer: BSIT 1: Information Technology II

  28. Building and Running a Visual Basic Application • Try out the Click event on the closeButton control by clicking on the Close button to close the application. BSIT 1: Information Technology II

  29. Advanced Spreadsheets This Module is designed for experienced spreadsheet users and will enable delegates to develop effective solutions to business problems using many of the advanced features within the Microsoft Excel Applications Package

  30. Worksheet and Workbook Protection Excel worksheet and workbook protection feature allows you to protect your file being open by unauthorised person. You can set the password so that anyone that intends to open the file requires the password to open it.

  31. To specify a password for opening and modifying a workbook • From the File menu, click Save As. • From the Save As dialog box displayed, click on the Tools icon and from the drop down list displayed, select General Options.

  32. From the Save Options dialog box displayed, enter a password into the Password to open: text box. In future you will be required to enter this password in order to open the file.

  33. If you enter a password into the Password to modify: text box, this gives others the ability to open, view and edit a workbook, but not to save it with the same name. They must save a modified version of the workbook with a different name. • Click on the OK button. You will be asked to re-type the password to ensure that it is consistent.

  34. Note:(1) The check box "Always create backup" forces Excel to create a backup copy of the file every time a worksheet file is saved. The file extension BAK is used, and the backup file is saved in the same folder as the original file. • (2) If the "Read-only recommended" check box is ticked, the following dialog box is displayed when the file is re-opened.

  35. Click Yes. • The file is reopen but with the feature ‘read-only’. • If you needed to make changes on the worksheet, you cannot save the file with the same file name. So, changing the file name allows you to save the file.

  36. To remove a password from a workbook • Open the workbook containing the password you wish to remove (by entering the password when prompted). • From the File menu, click Save As. • From the Save As dialog box displayed, click on the Tools icon, and from the drop down list displayed select General Options. • From the Save Options dialog box displayed, clear either or both passwords text box. • Click on the OK button to close the Save Options dialog box. • Click on the Save button to save the file, which will display a dialog box. • Click on the Yes button.

  37. To protect a workbook • From the Tools menu, point to Protection and click on Protect Workbook. • This will display the Protect Workbook dialog box.

  38. From the Protect Workbook dialog box displayed, you have two options: • StructurePrevents any changes to the structure of the workbook if checked. i.e. you will be unable to edit, insert, delete, rename, copy, move or hide sheets. Windows Prevents changes to the workbooks size. • The windows re-sizing controls (close, maximize, minimize and restore) are hidden. • In the Password column, you can enter a password (case sensitive). Click OK. • You will be asked to re-type the password to ensure that it is consistent. • Click OK.

  39. To unprotect a workbook • From the Tools menu, point to Protection and click on Unprotect Workbook. • Enter the required password and click on OK. To protect a worksheet • From the Tools menu, point to Protection and click on Protect Sheet. • This will displays the Protect Sheet dialog box.

  40. From the Protect Sheet dialog box displayed, you can choose any option from the lists if you ALLOW other users to modify the checked option in the worksheet. • In the Password to unprotect sheet: column, you can enter a password (case sensitive). Click OK. • You will be asked to re-type the password to ensure that it is consistent. • Click OK.

  41. To unprotect a worksheet • From the Tools menu, point to Protection and click on Unprotect Worksheet. • Enter the required password and click on OK.

  42. Using Templates: Apply templates • Turn on your computer and open Excel. • Click on "File" in the tool bar and select "New." This will open a sidebar to the right of your Excel document. • Under "Templates" click on the link that says "On my computer." This will open the "Templates" window. BSIT 1: Information Technology II

  43. Apply templates • Click on the tab labeled "Spreadsheet Solutions." This will display the list of templates available on your computer. Select the template of your choice and then click "OK." • A new window should now be open and the template you selected should be displayed. Now you can fill in the template, and then save and name it accordingly. BSIT 1: Information Technology II

  44. Edit templates • Excel recognizes two templates that it uses automatically, even if you don't specify a template to use. These templates allow you to customize new worksheets that you insert and new workbooks that you create. BSIT 1: Information Technology II

  45. Customize new worksheets • You can create a template named Sheet.xlt to specify the settings for new worksheets. • For example, if you don't want to use gridlines, you must turn them off for each worksheet individually. • If you prefer all new worksheets that you insert to have gridlines already turned off, you can create a Sheet.xlt template that has gridlines turned off. BSIT 1: Information Technology II

  46. Customize new workbooks • To specify settings for all new workbooks that you create by clicking the New button on the Standard toolbar or by clicking the Workbook icon in the New dialog box, you can create a template named Book.xlt. • For example, new workbooks have three worksheets initially, but if you prefer more or fewer worksheets, you can create a Book.xlt template with the number of worksheets you want. BSIT 1: Information Technology II

  47. Where to store templates • Depending on how you want to use templates, you can store them in the default locations, in personal use locations, or in shared locations. • For more information about the template folders and their locations, type location of settings in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search. BSIT 1: Information Technology II

  48. Default locations • For Excel to use the default templates Sheet.xlt and Book.xlt, you must store these templates in the xlstart folder. • If you later want to stop using either of these templates and resume inserting blank worksheets and creating blank workbooks, delete the Sheet.xlt or Book.xlt file. BSIT 1: Information Technology II

  49. Personal use • Put templates you create for your own use in your Templates folder (for the location of this folder, type location of settings in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search). • The Templates folder is where Excel puts templates you create if you don't specify a different location. BSIT 1: Information Technology II

More Related