PDA

View Full Version : Solved: Befuddled



Opv
04-08-2012, 02:27 PM
I'm experiencing an odd occurrence. At times, although not all the time, when I change a cell in a worksheet in Workbook A, Excel begins to open Workbook B and I get prompted concerning whether I want to allow macros in Workbook B. If this happens, once it happens I can't do anything in Workbook A without unless and until such time as I close all workbooks, exit Excel, restart Excel and open Workbook A again.

The only script I have in Workbook A is a short and simple Private Sub in one of the Worksheet modules to auto-populate two cells in that worksheet when a certain condition is met. (This is NOT, however, the worksheet that I'm working in when the above phenomenon occurs.)

I've gone through all of my relevant scripts in Workbook B and assured that they only run if Workbook B is the active workbook. That did not resolve the problem. Last time it occurred, I disabled all script in every module in Workbook B, and I still couldn't modify Workbook A. With each attempt, a message popped up and indicated that the macros in Workbook B were disabled. I had to totally exit Excel and start over before I could edit Workbook A.

Does this behavior ring a bell with anyone?

Paul_Hossler
04-09-2012, 05:46 AM
Are there defined names in WB A that reference WB B?

If not, can you post A?

Paul

Opv
04-09-2012, 07:14 AM
Are there defined names in WB A that reference WB B?

If not, can you post A?

Paul
Thanks for the tip. I checked. Both workbooks have worksheets named "SUMMARY" and workbook A has a defined name that references a range in its SUMMARY worksheet; however, the reference in the defined range within workbook A does not include a path to the sheet within workbook B. It is properly referencing the worksheet in workbook A.

I was thinking after my original post. The problem described seems to occur only after I have first opened and worked a while in Workbook B, either alone or in combination with another workbook. As long as workbook B hasn't yet been opened and manipulated, I don't recall noticing a problem.

That said, in reviewing my scripts I noticed a potential problem. In a couple of placed in a Private Sub Workbook_Change or Workbook_SelectionChange block of code in Workbook B, for some reason I have in some instances used "ActiveCell" instead of Target. My guess is that this might be causing the problem.

That would make sense to me if that was the problem; however, I don't understand why the problem would continue to occur even after Workbook B has been closed.

Opv
04-09-2012, 07:59 PM
I think I can safely say that the problem likely doesn't lie with Workbook A, as I just experienced the problem again and, on a hunch, created a brand new workbook, with absolutely no data and no VBA script, just an empty workbook, and when I tried to edit one of the cells in the new workbook, I received the same message about whether I wanted to run the macros in Workbook B. So, I'm thinking the issue has to be in Workbook B.

I'm still running some tests, blocking out blocks of code at a time to see if I can hone in on which script might be causing the problem.

Opv
04-10-2012, 10:14 AM
I think I've found my problem. I have several scripts in Workbook B which use "Application.onKey" to temporarily change the behavior of the Enter key. When certain conditions are met and Enter is pressed, one of several scripts are called, depending on which condition is met.

It finally dawned on me that the phenomenon was only occurring when I press Enter in a workbook other than Workbook B. I don't have a problem, for example, if I attempt to modify Workbook A by merely pasting new data into the workbook. I think I have have resolved the problem by assuring that the Application.onKey statement is reset back to its default action, both in the scripts that employ that statement as well as whenever the Workbook is deactivated or closed.