New workbook event, or new workbook name????

[cresbydotcom]

VBA help suggests this event should work

Sub App_NewWorkbook(ByVal Wb As Workbook)
newbuk = Wb
End Sub

I have tried putting it in "This Workbook" and Module1 but the event is not trapped. How do I get it to trap the event or find the name of the new workbook. Private or not private makes no difference

Other than list all workbooks before and after then see what is different.

 

[xlbo]

Hmmm - this would have to be in a CLASS module and I believe that there is already an application level New Workbook event...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[cresbydotcom]

I have just tried

Workbooks.Item(Workbooks.Count).Name

which does for the purpose.
thanks

 

[firefytr]

As Geoff said, this would need to be in a Class Module, not in ThisWorkbook module. Insert a class module (Insert | Class Module) and type the following...

Public WithEvents App As Application

Then, directly above where you typed that you'll see two drop down combo boxes. Choose the left one and select App. Now you'll see the shell of what you posted.

Now, here comes the definitive part you need to pay attention to. So we have a unique identifiable name which makes sense to us, we'll need to rename the class module. By default it will start as Class1, Class2, etc. So select it in the Project Explorer (Ctrl + R if you do not see it) and go to the Properties window (F4) and change the Name property from 'Class1' to 'clsApp' - or whatever name you want, but we'll be using it here in a bit.

Now double click on your ThisWorkbook module. Again, fine the General dropdown (aka the Object combobox, housing all unique objects in the current module) and select Workbook, you should get a workbook open procedure (unless you already have one, just navigate to it). Put in code that looks like this...

Private Sub Workbook_Open()
    Dim XlApp As New clsApp
    Set XlApp.App = Application
End Sub

Click your cursor in that and press F5 to run and initialize the variables/class.

Now go back into your Class module and add your code. I.e. ...

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
    MsgBox Wb.Name
End Sub

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[firefytr]

LOL! Okay, the car-drivey guy should have said "[ auto ]" (w/o spaces). It is an auto-initialized variable.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[xlbo]

pmsl !!!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

pmsl?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[WalkerEvans]

firefytr,

PMSL = "P*ssing myself laughing"

Mainly used in the UK and pretty much the same of ROFL in the US.


----------------------------------------------------------------------------------
[small]How did the newspaper describe the swindler who fell off the prison roof during an escape attempt?[/small]
They used a condescending remark.

 

[firefytr]

LOL! Happy to oblige (and now join in the laughing...).

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[cresbydotcom]

I will look at firefytr's method - though I have to say in the UK it would have been a case of:
TMHOWF trying to carinitialise!

(tearing my hair out with frustration)

The workbook.count wheeze is good enough provided the user doesn't have another workbook opening at the same time. Which I would guess to be the case 99.999% of the time.