Losing Sheet Tabs when suppressing Headings

Hello John,

These 2 macros should help. These will hide and display the Taskbar. Currently, the Taskbar is hiding the sheet tabs. Copy this code into a separate VBA module. You can then add calls in your code where needed to control the Taskbar.

Please Login or Register  to view this content.

Hello John,

The headings belong to the Window class. Try this...

Please Login or Register  to view this content.

Hello John,

Add this to the Workbook_Open event. This will remove the headings from all the sheets.

Please Login or Register  to view this content.

Hello John,

You're correct. The last line chooses which sheet you want to be active. The following line selects all the sheets in the workbook simultaneously.

Please Login or Register  to view this content.

Hello John,

The ReDim statement is used to change the size of the last dimension of an array. If you want to resize an array that already contains data, you should use the statements ReDim Preserve <array>. If you do not then the array will be cleared.

In this case, a single dimension array needs to be created that holds the names of all the sheets in the workbook. An array must be used to select more than one sheet at a time.

The Sheets collection has a count of all the workbook sheets. However, the count can not be used directly when declaring the array. Using a Variant, an array can be created without using the Dim statement. ReDim will create and attach the new array to the variant.

Hello John,

Unless you explicitly state the beginning element of the array, the first element defaults to zero. Some functions require zero based arrays and some are 1-based (starting at one). Here is an example of declaring a ten element array as 1-based.

Please Login or Register  to view this content.

Here is another way to do it using the Option Base statement. This is will define the first element of all arrays declared inside the module to begin with the number specified. This must be added to the top of the module's code.

Please Login or Register  to view this content.

Hello John,

I made 2 changes to the code and ran it. It runs fine on my machine. I removed the "ActiveSheet.DisplayHeadings = False" from the With statement and changed which sheet become the ActiveSheet.

Please Login or Register  to view this content.

Hello John,

This macro will force the headings to be hidden whenever the sheet is activated. Add this to the ThisWorkbook module.

Please Login or Register  to view this content.

Change the code in your macro to what is below. This probably won't fix the Chart Sheet problem, but should fix the other.

Please Login or Register  to view this content.

Hello John,

This should fixz the problem.

Please Login or Register  to view this content.

Hello John,

I am out of ideas. Can you post the workbook? I can then test it out on my machine.

Okay, here it is. I'm about to leave work for the day, so if you find anything I won't be able to check it until tomorrow morning. Thanks a lot for sticking with me through this.

Hello John,

Thanks for posting the workbook. It would have taken forever to figure it out. You had hidden sheets and some with protection. This is what was causing the problem with the macro. I rewrote it and tested it. Everything looks good on my end. There were minor changes made elsewhere. Try out the attached workbook and let me know the results.

Please Login or Register  to view this content.

Follow-up: I tried the last macro you posted in my full sheet, hit the same error on the same line.

As a workaround, I've gone back to the original macro you posted, and put

Please Login or Register  to view this content.

in every worksheet activate event. It's all working, except I still have that one solid light blue line (looks like the Command Bar minus all the selections ("File", "Edit", etc.). The blue line doesn't show up in the initial view, but when navigating via any of the macros it appears.

Hello John,

The blue line is indeed where the menu bar would be. For some reason, your system is failing to render the application window correctly. My first suspicion is lack of memory.

Check your memory while the workbook is open. In Windows 2003 you can monitor your memory using the Windows Task Manager. To bring it up, use CTRL+ALT+Del and then select the performance tab.

Hello John,

Check if you are approaching your limit with physical memory. The sum of available memory and the cache memory should be well below the total shown on the Performance tab.

Hello John,

Well, it looks like you have plenty of memory. If you can, try running the program with the anti-virus turned off and see if that changes anything.

Hello John,

Can you run the file on another machine with the same anti-virus software? If you get the same result, it is probably due to the anti-virus hogging the resources.

Hello John,

Just to be sure it is work related, run the workbook on your computer at home. If it is work related then I don't know where to go next.