XL: Cannot Unhide Module Sheet If Visible Property Is xlVeryHidden (153587)

SYMPTOMS

You may not be able to unhide a module sheet if the following two conditions are true:

The module sheet was hidden by setting its Visible property to xlVeryHidden. -and-
The hidden module sheet contains a macro that is called from a macro in a different module sheet, and that module also contains the macro to unhide the hidden module.

NOTE: This does not apply to Microsoft Excel 97 for Windows and Microsoft Excel 98 Macintosh Edition; modules in these versions of Microsoft Excel cannot be hidden, since they are displayed in the Visual Basic Editor.

CAUSE

Usually a module sheet that was hidden by setting the Visible property to xlVeryHidden, can be unhidden by setting the Visible property to TRUE. But if the module sheet contains a macro, that is called from a macro in a different module sheet, and that module sheet also contains the macro to unhide the module, the hidden module sheet will not be unhidden.

For example, if a macro named UnhideModule is in a module sheet named, Module1 and the macro below is added to that module sheet (Module1), after the module sheet Module2 is hidden, running the UnhideModule macro will not unhide the Module2 module sheet.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. Type the following code in module sheet, Module1:

   Sub HideModule()
      Sheets("Module2").Visible = xlVeryHidden
   End Sub

   Sub UnhideModule()
      Sheets("Module2").Visible = True
   End Sub

   Sub CallMacroInModule2()
      ' This is a macro in Module1 that calls a macro that is in Module2.
      Call Message ' Message is a macro in Module2.
   End Sub

You will still be able to run all the macros that are contained in the hidden module.

NOTE: If you add the above macro to Module1 before hiding Module2, and then try to hide Module2, you receive an error message.

For additional information, please see the following article in the Microsoft Knowledge Base:

131551 Macro Cannot Set Module to xlVeryHidden

WORKAROUND

To unhide the module sheet, in this example Module2, make sure that the macro that unhides the module sheet (Module2), is in a different module sheet from the macro that calls the macro contained in the hidden module (Module2). Using the example above, you can put the calling macro in a new module sheet, Module3, and leave the unhide macro in Module1. You can also insert an apostrophe before the line that calls the macro, making that line a comment, or delete the calling line. After doing any of the above, you will be able to unhide the module by setting its Visible property to TRUE.

REFERENCES

For more information about the Visible property in Microsoft Excel version 7.0, click Answer Wizard on the Help menu and type:

Tell me about the visible property

For more information about the Visible property in Microsoft Excel version 5.0, choose the Search button in Help and type:

visible property