Excel Training VBA Lesson 4

This will return ALL built in CommandBars and ALL custom CommandBars that are within the open Workbook.  There are 85 built in CommandBars in Excel.  and 845 associated level 1 Controls.  How do I know this, I counted them! Not really :o)  Included with this lesson is some code I have written that will list all CommandBars and their associated level 1 Controls.  As you learn more about VBA for Excel and you become comfortable with it, you will no doubt find yourself wanting to do more than just automate common tasks within Excel.  You will possibly end up wanting to create your own custom CommandBar and/or modifying Excels built in CommandBars.  To do this you will need to gain access to them and their Controls and to do this you will need to know their names or captions.  There are two ways to access CommandBars and their Controls,  one is to use the Index number and the other is to use the name or caption.  I strongly recommend using the latter (name or caption) method as you will be able to tell at a glance which CommandBar or Control you are dealing with.

As CommandBars represent a Collection of the CommandBar Object and CommandBars are a member of the Application we must go through the correct order to gain access.  So if we want to hide a particular CommandBar we would use the code:

Sub HideACommandBar()
    Application.CommandBars("Formatting").Visible = True
End Sub

As you can see this will simply show the Formatting CommandBar.  We have told Excel we want a Object member of the CommandBars Collection and that member is called "Formatting".  We have then set it's Visible Property to True.  the Visible property returns a Boolean ie, either True or False.  So having gained access to the "Formatting" CommandBar we can now also gain access to any one of it's Controls.  To do this we would use:

Sub DisableACommandBarsControl()
    Application.CommandBars("Formatting").Controls("&Fill Color").Enabled =False
End Sub

This would grey out (disable) the "Fill Color" icon on the "Formatting" Commandbar.  The use of the "&" before the Control names simply represents the underline that is used on Excel Toolbars, eg File would be expressed as ("&File").

Now if we are dealing with the "Worksheet Menu Bar" Excels main CommandBar we not only need to gain access to the first level Control, but also it's second level and third level.  To do this we would use:

Sub AccessMenuBarLevel2()
    Application.CommandBars("Worksheet Menu Bar").Controls _
                       ("&Insert").Controls("&Name").Enabled = False
End Sub

From here we can also go to the last level, to do this we would use:

Sub AccessMenuBarLevel3()
    Application.CommandBars("Worksheet Menu Bar").Controls _
                       ("&Insert").Controls("&Name").  _
                       Controls("&Define...").Enabled = False
End Sub

So as you can see, once we know the names of the CommandBar and It's Controls caption we are able to manipulate it in many ways.

Workbook

As with the CommandBar Object, to gain access to a Workbook Object we go through the Workbooks Collection.  some of the most common uses of the Workbook Object are Opening, Saving, Activating and Closing.  Lets look at each of these.

Opening

To open a Workbook we must tell Excel it's name and if we are dealing with more than one directory, it's file path.  So to open a Workbook that is on the same drive in the same folder we would use:

Sub OpenAWorkbook()
    Workbooks.Open ("Book1.xls")
End Sub

The "Open" Method also takes some arguments all of which are optional.  (An argument is a constant, variable, or expression passed to a procedure).  The most common one is the "UpdateLinks"

UpdateLinks tells Excel how to deal with a file we are opening that contains links.  If the argument is omitted, the user is asked (via a message box)  how links should be updated.

The table below is from the VBE help in Excel

Value     Meaning
0           Doesn't update any references

1           Updates external references but not remote references

2           Updates remote references but not external references

3           Updates both remote and external references

So to open a file and NOT update links we would use:

Sub OpenAWorkbookWithLinksWithoutUpdating()
    Workbooks.Open "Book1.xls", UpDatelinks:=0
End Sub