Excel VBATutorial                      

             

Excel VBA Macros

Email to excel-vba.com

Excel Tutorial on Macros

 Excel Consulting

Here is a sample of what you will find in lesson 15 of the downloadable Tutorial on Excel macros

Lesson 15 on Excel Macros (VBA):

VBA Code for Worksheets

To develop a VBA procedure that is triggered by an event relating to the worksheet (when you select it, when you leave it...) see the VBA lesson  on events.

Sheets

You access a worksheet named " Balance" with:
Sheets("Balance").Select
Note that the word "Sheets" is plural and always use the quotes within the parenthesis

You cannot select a sheet that is hidden so you will need to write:
Sheets("Balance").Visible= True
Sheets("Balance").Select
and then if you want to hide the sheet again:
Sheets("Balance").Visible= False

The name  of a sheet must not have more than 31 characters and should not include certain special characters like  " ? : \   /   [  ]" . If you do not respect these rules your procedure will crash.

The following lines of code will generate an error message:
Sheets("Sheet1").Name= "Balance and Introduction to Numbers" because there are more than 31 characters including the spaces
Sheets("Sheet1").Name= " Balance: Introduction" because of the special character :
Sheets("Sheet1" ).Name= " " because the name  cannot be blank

You can not go directly from a sheet to a cell on another sheet. For example if the active sheet is "Balance" and you want tot go to cell A1 of a sheet named " Results" you cannot write:
Sheets("Results").Range("A1").Select

You must take two steps:
Sheets("Results").Select
Range("A1").Select


We hope you have enjoyed this introduction to lesson 15
for more on this topic and a complete course on Excel macros download the
Tutorial on Excel Macros


Next Lesson: VBA Code for Cells and Ranges