Controlling Display of Toolbar Buttons

by Allen Wyatt
(last updated November 22, 2014)

1

Jody is in the process of developing custom toolbar buttons and assigning macros to the buttons. She wants to have the buttons be enabled whenever at least one worksheet is visible, but is grasping for the proper code to handle such a situation.

There are many ways that this can be approached, as one might assume with an environment as diverse as Excel. One possible solution is to create a routine that simply checks if there are any visible windows on the screen. If there are, then the toolbar buttons can be enabled; if there aren't, then they can be disabled. The following macro will do just that:

Sub CheckButtons()
    Dim bOneOpen As Boolean
    Dim I As Integer
    Dim J As Integer
    
    bOneOpen = False
    For I = 1 To Workbooks.Count
        For J = 1 To Workbooks(I).Windows.Count
            If Workbooks(I).Windows(J).Visible Then bOneOpen = True
        Next J
        If bOneOpen Then Exit For
    Next I
    If bln Then
        'enable buttons
    Else
        'disable buttons
    End If
End Sub

Notice the two comments near the bottom of the macro. All you need to do is replace those comments with the appropriate code to enable or disable your toolbar buttons. (The code will vary, depending on the number and configuration of your buttons.)

This macro can be called either manually, or it can be called from any of the events that are triggered by window changes, such as those that fire when windows are opened, resized, minimized, maximized, or restored.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2618) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Turning Off Automatic Numbered Lists

Type what Word thinks is a numbered list, and it will helpfully format the text to match what it thinks your numbered list ...

Discover More

Moving Master and Subdocuments

If you need to move master documents or subdocuments from one place to another on your computer, you have to keep in mind the ...

Discover More

Superscripts in Custom Formats

When you create custom formats for your data, Excel provides quite a few ways you can make that data look just as you want it ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

MORE EXCELTIPS (MENU)

Creating Charts in VBA

Most charts you create in Excel are based on information stored in a worksheet. You can also create charts based on ...

Discover More

Using Seek In a Macro

When reading information from a text file, your macro may need to start reading at a place other than the beginning of the ...

Discover More

Adding Leading Zeroes to ZIP Codes

Import a bunch of ZIP Codes into Excel, and you may be surprised that any leading zeroes disappear. Here's a handy little ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is seven minus 6?

2014-11-29 04:59:24

JMJ

Well... I suppose that in the IF instruction, it shouldn't be bln but bOneOpen :-)


Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share