I need to validate the data that a user enters into a specific area on a worksheet. How do I determine which cell the data was entered into?
	I am finding that several of the clients I develop for have the Break On All Errors setting checked. This causes all of my error handling to fail. Is there any way for me to turn this setting off?
	I must select all the worksheets in the active workbook. However, the number of sheets and their names will change. How do I do this in VBA?
	My code to close all workbooks does not work. Why?
	In my code, I open a workbook using the Workbooks.Open method. The Auto_Open subroutine in this workbook does not seem to be invoked when I open the workbook. Is this a bug?
	How do I prevent the Auto_Open subroutine from running when opening a workbook?
	What's the difference between ActiveWorkbook and ThisWorkbook?

I need to validate the data that a user enters into a specific area on a worksheet. How do I determine which cell the data was entered into?
Keywords: Application.Caller OnEntry Validation
Posted November 19, 1996

This can be accomplished by assigning a custom macro to the OnEntry property of the worksheet and using the Application.Caller method to determine where the cell that fired the OnEntry macro was located. Here is a simple example. This example assumes that you need to validate that the data entered into cells A1:A10 on Sheet1 is numeric.

Sub SetOnEntry()
    ActiveWorkbook.Worksheets("Sheet1").OnEntry = ThisWorkbook.Name & "!ValidateCells"
End Sub

Sub ValidateCells()
    
    Dim oValidate As Range  ''' The range you wish to validate
    Dim oEntry As Range     ''' The range data was entered into
    Dim oTemp As Range
    
    Set oValidate = ActiveWorkbook.Worksheets("Sheet1").Range("A1:A10")
    Set oEntry = Application.Caller
    
    ''' Determine if oEntry falls within oValidate.
    Set oTemp = Nothing
    On Error Resume Next
    Set oTemp = Intersect(oValidate, oEntry)
    On Error GoTo 0
    
    If oTemp Is Nothing Then
        ''' No action required, cell was not in validate range.
    Else
        If Not IsNumeric(oEntry.Value) Then
            MsgBox "This entry must be numeric.", vbCritical, "Validate"
            oEntry.Value = ""
        End If
    End If

End Sub

I am finding that several of the clients I develop for have the Break On All Errors setting checked. This causes all of my error handling to fail. Is there any way for me to turn this setting off?
Keywords: Error Handling Break On All Errors
Posted November 19, 1996

Yes there is. You can't address this setting directly through VBA, so you must use the SendKeys method, as demonstrated below, instead. Place this at the end of your Auto_Open procedure to ensure that this setting is always turned off while your program is running.

Application.SendKeys ("%TOVM%B-~"), True

I must select all the worksheets in the active workbook. However, the number of sheets and their names will change. How do I do this in VBA?
Keywords: Select Worksheets
Posted November 19, 1996

This can be accomplished with a simple, one line VBA statement:

ActiveWorkbook.Worksheets.Select

This statement relies on the fact that many Excel objects, including worksheets, are part of a collection object, in this case the Worksheets collection, and you can perform many operations on the entire collection by addressing this collection object directly rather than all of it’s members individually.

My code to close all workbooks does not work. Why?
Keywords: For-Each Workbook Close Loop Count
Posted May 15, 1996

Two solutions are discussed below.

In your code 

Sub CloseAllWorkbooks
Dim iCount As Integer
   For iCount = 1 To Application.Workbooks.Count
      Application.Workbooks(iCount).Close
   Next iCount
End Sub
			

the count of open workbooks changes within the loop every time you close a workbook. The For loop is trying to loop based on the value of Workbooks.Count at the beginning. So after closing half the available workbooks, the index goes out of range. Solution 1 below illustrates this concept while Solution 2 is a better approach.

'Solution 1: Loop downward. Not suitable when closing selected workbooks

Sub CloseAllWorkbooks
Dim iCount As Integer
   For iCount = Application.Workbooks.Count to 1 Step -1
         Application.Workbooks(iCount).Close
   Next iCount
End Sub
			

'Solution 2: Best solution. Excel dynamically adjusts the upper bound when iterating an object collection using the For Each construct. Plus, it is more efficient than continually fetching the Workbooks collection and indexing into it.  

Sub CloseAllWorkbooks
Dim wb As Workbook
   For Each wb In Application.Workbooks
      wb.Close
   Next wb
End Sub
			

In my code, I open a workbook using the Workbooks.Open method. The Auto_Open subroutine in this workbook does not seem to be invoked when I open the workbook. Is this a bug?
Keywords: ActiveWorkbook RunAutoMacros xlAutoOpen Auto_Open
Posted March 22, 1996

No, this is how it is documented to work. The Auto_Open macro is not run when you open a workbook through code. To execute it, you must call the RunAutoMacros method of the workbook object after opening it, as follows.

Workbooks.Open "MYBOOK.XLS"

ActiveWorkbook.RunAutoMacros xlAutoOpen	

Here, xlAutoOpen is a constant that has to be passed to the RunAutoMacros method. The method takes a parameter telling it which of four Auto macros to run (Auto_Open, Auto_Close, Auto_Activate, Auto_Deactivate).

How do I prevent the Auto_Open subroutine from running when opening a workbook?
Keywords: RunAutoMacros Shift Prevent Auto_Open
Posted March 22, 1996

Hold the shift key down while opening the file. If the file is password protected, be sure to hold the Shift key down after providing the password.

When you open a workbook via code, the Auto_Open macro will not run. To run it you need to call the RunAutoMacros method of the workbook object.

What's the difference between ActiveWorkbook and ThisWorkbook?
Keywords: Workbook Active Code This
Posted January 16, 1996

ActiveWorkbook and ThisWorkbook both return workbook objects, but the Workbook returned may be different depending on the context.

ThisWorkbook always refers to the workbook containing the running code, not necessary the workbook with the focus.

ActiveWorkbook refers to the workbook with the focus, which may or may not be the Workbook with the code. For example, if you're testing code that displays a dialog box, and you are writing the code and then pressing F5 (the shortcut for macro run) to display your dialog, you could use any of the following to display a dialog:

DialogSheets("GreatDialog").Show
ActiveWorkbook.Dialogsheets("GreatDialog").Show
ThisWorkbook.Dialogsheets("GreatDialog").Show
	

If you create an Add-in and then run this code, lines one and two would break because the Add-in wouldn't be the ActiveWorkbook and the first two examples assume the ActiveWorkbook contains the dialog sheet.

Back to Top

Back to the FAQ Table of Contents

Pardon my driving, I am reloading.