Sherlock, Can You Find Cause of Macro Error on FileOpen

[mandora]

SHERLOCK WORTHY PROBLEM: Is there a Sherlock that can solve this problem. When workbook is opened an error box pops up noting that it is "unable to get the Hidden property of the range class" pertaining to a line in the code. The mystery is rooted in an unexplained diversion of code in the Workbook_Open module which is tracked through the code below. Why is code diverted? How to remedy? File is attached. Also, see background information.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

BACGROUND: This is a budget summary workbook with three sheets. Sheet 1 shows Budget income & expenses. Sheet 2 shows Actual income & expenses. Sheet 3 shows the NetDiff of sheets 1 & 2. Each sheet is identical in programs represented and accounts structure. (Programs may change but accounts do not.) Filtering criteria of accounts with data is applied via formulas in Col B. Data can be filtered with the AutoFilter arrow in Col B. (To facilitate debugging, a filter toggle button on each sheet has been hidden and associated code disabled until the problem discussed in this query is resolved.)

VBA code facilitates data entry. To enter data, select Budget or Actual tab. (Data can be reviewed but not entered into the NetDiff sheet.) Using the comboboxes on the sheet, select a program. Then select an accounts catagory. There is a Show All and Print button on each sheet. The sheets are protected.

A notable feature of this workbook is that actions on one sheet are reflected on the other two sheets. For example, if program X is selected on any sheet (thereby hiding other programs), that action is replicated on the other two sheets. Same with selection of account catagories, filtering data and restoring data to default (fully revealed) state.

Note: Tasks remaining include reinstating hidden filter toggle buttons and associated code, fixing incorrect formulas in NetDiff sheet and final debugging and testing including print macros.

[patel45]

don't use Sheet1, Sheet2 ... , use always Sheets("name") to avoid confusion

[Izandol]

In normal module add:

Please Login or Register  to view this content.

In module for NetDiff sheet change code to:

Please Login or Register  to view this content.

In ThisWorkbook module:

Please Login or Register  to view this content.

[mandora]

Izandol, you receive the SHERLOCK AWARD!! Your code solved the mystery that has puzzled me for too many hours. Thank you!!

[mandora]

Izandol, I would like to know why your code works. How did you know to define & use variable "Public bNoEvents As Boolean" in the code?

[Izandol]

Application.Enableevents does not affect controls - on worksheets or userforms - so we must create our own version. If you do not link the controls to cells you may also avoid the problem.