active workbook name - new workbook name?

[wmtsub]

So I entered the code below into my personal.xls thisworkbook section in hopes that every time a new workbook opens i would get me the name of the new workbook displayed. But no, the first time it gives me "Personal.xls" subsequent newly opened work book it does not respond at all.

Any ideas how I can do this?

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Private Sub Workbook_Open()
twn=thisworkbook.name
awn=activeworkbook.name
msgbox twn
msgbox awn
end sub
[/FONT]

 

[=ODIN=]

It looks like your code is functioning like it should.

The below event only fires when the workbook it is located in is opened (it is a workbook level event). Since yours is in personal.xls, it only fires once when excel is run and it opens the personal.xls file.

[COLOR=#333333]Private Sub Workbook_Open()[/COLOR]

To do what you're describing, you would need to capture the WorkbookOpen event at the application level. The below 2 links give a nice description of how this works -- it is a lot more complicated than the workbook level events. If you give a little more detail on why you are trying to do what you described, there may be an easier way around it, but to purely capture the name of a workook as it is opened, I think you might be stuck with the application level event.

https://bettersolutions.com/vba/events/excel-application-level-events.htm
https://bettersolutions.com/excel/macros/application-level-events.htm

 

[wmtsub]

Thank for the help. I have one wb that I open many m,any times a day. I am trying to specify the location and size so it will always open in the same unused section of my screens. This works, except then every other workbook opens on top of it at that size. So I was trrying to make it active only for that one workbook.

 

[=ODIN=]

I went ahead and setup an add-in file that will function like your first post described -- msgboxing name of any workbook that is opened.
You can download it from the link below.
To get it to work, you will need to unzip the .xla file, then in Excel enable the add-in by going to file->options->add ins-> excel add ins-> go.
Once enabled the file will run every time excel is run until you disable it.
To modify the code, in the code editor window, you will see a class module folder with one class module in it. Open that up, and you will see the 2 events below. You will need to modify both of those identically. It turns out that workbookOpen only works for existing workbooks, so you need the newWorkbook one to handle any brand new workbooks.

Private Sub excel_events1_NewWorkbook(ByVal Wb As Workbook)    'fires when a brand new workbook is opened
    MsgBox Wb.Name
    
End Sub


Private Sub excel_events1_WorkbookOpen(ByVal Wb As Workbook)
    'fires when an existing workbook is opened
    MsgBox Wb.Name
End Sub

http://s000.tinyupload.com/?file_id=53071202495837314834

 

[wmtsub]

OMG Thank you.