I am using code (see attached snippet) that I wish would give me the workbook connected to the active code module. However, there is a "hidden" code module that automatically comes attached with any VB program I write in Excel called "Insert Visio Button.XLS", which is obviously an addon from Visio. I say "hidden" because the Excel file is not visible from the Excel application, but it shows up in the Project Explorer window. What I'm finding is that the code below most often returns the workbook connected to code, but it sometimes returns the workbook connected to "Insert Visio Button.XLS", which, in my view, should never truly be the active code module.
If someone has a better solution to achieve the desired result, I'm listening.
Thank you, Peter Ferber
Function WorkbookConnectedToCode(UseApp As Excel.Application) As Workbook
Set WorkbookConnectedToCode = UseApp.Workbooks(GetFullFileName(UseApp.VBE.ActiveVBProject.Filename))
Function GetFullFileName(strFullPath As String) As String
Dim iLastSlash As Integer
iLastSlash = InStrRev(strFullPath, "\")
GetFullFileName = Mid(strFullPath, iLastSlash + 1)