active workbook name - new workbook name?

wmtsub

Active Member
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?

Code:
[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=

Active Member
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.
Code:
[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

Active Member
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=

Active Member
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.



Code:
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
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top