Anyone ever hear of this?

[hatman]

So I had a colleague ask me to come help him with some code that was firing when he closed a workbook that he inheritted. The first thing that struck me as odd was that is was a .xlsx, not a .xlsm. Ipso fatso: no VBA code in the book anywhere. First I had him check ThisWorkbook.Excel4MacroSheets.count, which returned zero. This stumped me, so just on a hunch, I had him check the Name Manager Dialog. Guess what: there were two defined names called Autpen and Auto_Close, each linked to a cell on a worksheet, and in each cell was a filepath pointing to an external file (.dbq???? isn't that an AutCad query file?). I had him delete these two Names from the dialog, and it fixed the problem. But outside of the Get.Cell workaround, I didn;t know this usage existed... I played with duplicating it in a new workbook, just for kix, and I'm not getting anything. Can anybody shed some light on this?

 

[AngelJ]

The defined names thing is new to me, so I'm subscribing to hear more about that!

On a related note, though, I am aware of another way that event code can fire for non macro-enabled workbooks:

I created a class called EventHandler with the following code:

Private WithEvents wb As Workbook


Private Sub Class_Initialize()
    Set wb = Workbooks("Book1")
End Sub


Private Sub wb_BeforeClose(Cancel As Boolean)
    Cancel = MsgBox("Are you sure you want to close?", vbYesNo) = vbNo
End Sub

Then I added the following code to my Personal Macro Workbook:

Dim eh As EventHandler
Private Sub Workbook_Open()
    Set eh = New EventHandler
End Sub

 

[hatman]

Excellent point! Assume that this type of construct is not in play in this case (I know this to be fact). But thank you for mentioning it.

I'm not sure how far this discussion is going to go... it;s more curiosity than anything else, though I suppose it could yield some interesting possibilities once I fully understand it.

 

[AngelJ]

I have been able to find some stuff on it like this: Both Autpen and Auto_Activate Macros Run When Sheet Opened

It looks like a deprecated method to me.

Edit: I just realized you addressed the macro sheet thing in your first post. I wasn't aware of macro sheets before looking into this, so I missed that.

 

[RoryA]

I've certainly seen the old XLM auto_open name before but, like you, thought it would have to be a macro-enabled format. I wonder if that's a conversion issue.