Written by Allen Wyatt (last updated June 18, 2019)
This tip applies to Excel 97, 2000, 2002, and 2003
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.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2618) applies to Microsoft Excel 97, 2000, 2002, and 2003.
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!
Need to select a cell using a macro? Need that selection to be relative to the cell you currently have selected? Here's ...
Discover MoreGot a macro that you need to run on each of a number of workbooks? Excel provides a number of ways to go about this task, ...
Discover MoreGot a bunch of cells that have different colored text in them? Here's a great way to count the occurrences of certain ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2014-11-29 04:59:24
JMJ
Well... I suppose that in the IF instruction, it shouldn't be bln but bOneOpen :-)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments