Excel Enable Content with validated cell bug

Question

I have found what I think is a bug in Excel and I wondered whether anyone else has seen it, or if there is a setting that can avoid the problem.

Problem statement: If I open a xlsm workbook (that contains macros) when (a) the active cell contains a Data Validation message, and (b) there is already another Excel workbook open; then the "Enable Contents" prompt does not appear. However if you minimise the workbook and switch to the previously-opened workbook and then switch back to the macro workbook, the Enable Contents prompt appears as it should.

I am using Excel 2010 on Windows 7.

I can reproduce it minimally like this:

(1) Create new workbook, and for cell A1: Data>Data Validation>Input Message -- enter a single space for the message. When you OK it, cell A1 will be active and will have a small yellow bow next to it. (So there is no data validation for that cell, just the validation message consisting of a single space).

(2) Create a Workbook_Open macro with MsgBox "Macros working" (anything so that the workbook should ask whether to Enable Content).

(3) Save workbook with A1 still the active cell. Let's call this workbook1.

Now open Excel with any other workbook (let's call it workbook2), then open workbook1. For me, the Enable Content prompt does not appear. If you minimise or swap back to workbook2, then come back to workbook1, the prompt suddenly appears.

If in step (3) above you save the workbook1 with a different cell active, then when you open it again the Enable Content prompt always appears and all is well.

Has anyone else ever seen this? Is it well know? I have not found any reference to it anywhere. Should I report it to Microsoft somehow?

Answer 1

Now I understand you, I'm sorry, sometimes I'm a little dumb witted.

IMHO the only solution it to enable the macros by default in the security settings.

Andreas.

Answer 2

Andreas, the whole problem I am having is that the bug means that I cannot enable macros! When I open the macro-enabled workbook as described, the "Enable Contents" prompt does not appear, so I cannot enable macros.

So if I implement your suggestion, the workbook opens but nothing happens. I want to enable macros but Excel does not ask me to!

Answer 3

The MsgBox line is not executed (because macros are not enabled), so how could the "ActiveCell.Select" line ever be executed?

You are right, if the macros are not enabled, the solution did not work.

But if the macros are enabled, it works. .-)

Andreas.

Answer 4

Andreas, thank you for your suggestion; but since I cannot see the Enable Content prompt, the macros are not enabled and so the ActiveCell.Select line does not get executed.  I have tried your suggestion and I am afraid it does not work, and I cannot see how the line could ever be executed if macros are not enabled.

In fact my Workbook_Open routine is as follows:

Private Sub Workbook_Open()

MsgBox "Macros working"

ActiveCell.Select

End Sub

The MsgBox line is not executed (because macros are not enabled), so how could the "ActiveCell.Select" line ever be executed?

Answer 5

I can reproduce this behavior, the issue can be fixed if you add this line to the end of your Workbook_Open macro:

ActiveCell.Select

Andreas.