Program by Levels

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple

VBA Lesson 3 of 33: The Properties Window in the Visual Basic Editor of Excel
(Levels: Beginners)

Note: For a better comprehension of the lessons in section 1 on the Visual Basic Editor and the Macro Recorder www.excel-vba.com has created a workbook (vba-tutorial-editor.xls) one of the 25 that are included with the VBA for Excel Desktop Tutorial.

The Properties window shows you the properties of the object that is selected in the Project Window (single click) or of the the userform or of the control that you have selected on a userform (double click). Click once on any object in the Project window and its properties appear in the Properties window. You can modify any of the properties from the properties window. The list of properties changes as  you select different types of components (ThisWorkbook, sheets, modules, userforms or controls).

You can set the beginning properties in the Properties  window and change them later with VBA with lines of code like:
Sheets("Test 1").Visible = True
Sheets("Test 1").Visible = False
Sheets("Test 1").Visible = xlVeryHidden

For example, click on "Sheet1" in   the Project window and you will see the following properties for the sheet:

There are 3 properties that you will work with: (Name) property which is the VBA name of the sheet, Name property which is the caption (the name on the tab in Excel) and the Visible property where you make a sheet either -1-xlSheetVisible, -0- xlSheetHidden or....yes yes yes...-2- xlSheetVeryHidden. Click on Sheet3 of "vba-tutorial-editor.xls" and see that it is very hidden see also that  Sheet2 is hidden and that the other sheets are visible.

The "VeryHidden" property is very useful. You make a sheet very hidden if it contains information that you want to hide to the user. He can only access this sheet through the VBE and if the procedure is protected by a password he can't get to it.

To change any  property click in  the text box of the property. If you click on the text box of the Visible property of Sheet1 you will see this:

The small arrow reveals a drop-down list with the three possible values for this property. Select any and go to Excel to see that the property of Sheet1 has changed.

Click on Module1 of "vba-tutorial-editor.xls" in the Project window and your property window will look like this one.

There is only one property, the name. Please, please, please name your modules. There is nothing less informative than Module1, Module2, Module3.... It is so much friendlier to see modImport, modDatabase, modExportData, modWhatever. Give a significant name to your module starting with the prefix "mod "and a first capital letter for any significant word comprised in the name. You will see later how important this prefix and capital letters habit becomes.

To see the properties of userforms and their controls see lessons 3-1 to 3-10.

If you click on ThisWorkbook in the Project window you will see this:

Unless you are a "real" programmer who is trying to reinvent the wheel you will not work with these properties. The ThisWorkbook object (as you will see in the lesson on events) is where you store the procedures that you want executed  when the workbook is opened. You could have there a line of code like:
Sheets("Introduction") .Select
Range("A1") .Select
if you want you workbook to always open showing the sheet "Introduction" with the cell "A1" selected. You can decide to show a message box or a userform when the workbook is opened and you can even develop very complex procedures like importing a new set of data from the central database or the WEB to make sure that any analyses or reports that is created by the workbook is using the most recent data.

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple