Welcome toExcel.Tips.Net
Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment
ExcelTips FAQ
ExcelTips Premium
Learn Access Now
Free Printable Forms
Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Legal Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips
Advertise on the
ExcelTips Site
Filtering Columns for Unique Values
Printing Multiple Worksheets on a Single Page
Jody Perrell 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 versions: 97 2000 2002 2003
Tame Your Data! ExcelTips: Filters and Filtering provides all the details necessary to let you manage large sets of data with confidence and ease. Its information-packed pages demonstrate how to use the two types of filters provided by Excel: AutoFilters and advanced filters.