Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. VBA code "behind" a worksheet or a workbook may not work in Excel

VBA code "behind" a worksheet or a workbook may not work in Excel

For a Microsoft Excel 97 version of this article, see 157346 .

For a Microsoft Excel 98 version of this article, see 182482 .

↑ Back to the top

Symptoms

In Microsoft Office Excel, Microsoft Visual Basic for Application (VBA) macro code that is contained "behind" a worksheet or workbook may not work correctly.

↑ Back to the top

Cause

This will occur if both of the following conditions are true:
  • The code in question is contained inside an automatically-running subroutine, such as an Auto_Open or Auto_Close subroutine.
  • The code is not contained in a Visual Basic module, but "behind" a worksheet or the workbook itself.

↑ Back to the top

Workaround

In order for automatically-running subroutines to work correctly in Microsoft Excel, they must be contained within a Visual Basic module.

You must insert a Visual Basic module into the workbook and then place the code in the new module.

To insert a Visual Basic module:
  1. In the Project Explorer in the Visual Basic Editor, activate the workbook that contains the code in question.
  2. On the Insert menu, click Module.
After the new module is inserted, cut the code from its original location and paste it into the Visual Basic module. Then, save the workbook.

↑ Back to the top

More Information

In Excel, VBA code can be stored in three different locations: in a Visual Basic module, in a Visual Basic class module, and "behind" worksheets and workbooks.

To edit code "behind" a worksheet or a workbook:
  1. Activate the Visual Basic Editor (press ALT+F11).
  2. In the Project Explorer window, you will see entries similar to the following:

         VBAProject (Book1)
         Microsoft Excel Objects
            Sheet1 (Sheet1)
            Sheet2 (Sheet2)
            Sheet3 (Sheet3)
            ThisWorkbook         
         Modules
             Module1
If you double-click one of the worksheet names or ThisWorkbook, you can type code in the code window. Such code not contained within a Visual Basic module is considered to be "behind" the worksheet or
ThisWorkbook .

If an Auto_Open, Auto_Close, or other automatically-running subroutine is stored "behind" a worksheet or ThisWorkbook, it may not function correctly when you open or close your workbook, or when you perform an action that should cause the subroutine to run.

↑ Back to the top

Keywords: kboffice12yes, kbfreshness2006, kbmacro, kbautomation, kbvba, kbexpertisebeginner, epucon, kbfaq, kbpending, kbprb, kbprogramming, offcon, kb, kbbillprodsweep

↑ Back to the top

Article Info
Article ID : 213581
Revision : 11
Created on : 8/20/2020
Published on : 8/21/2020
Exists online : False
Views : 156