Solved: On open excel

[jov_damo86]

Hello..

I want to ask something..

Is there any way or code that will determine the filename or worksheet name whenever i open a excel files?

Example: If i open a worksheet or workbook named "Hello". a msgbox pop up indicating "You open HELLO worksheet".

using either a personal.xls or a add-in menu.

Thank you for your help..

[geekgirlau]

I don't know whether you could create an add-in or class module for this, but the first question is why?

I'm assuming that you have some purpose other than telling the user what workbook they've opened (I would like to think that if they opened it, they already know what the filename is). Why do you need to check every single workbook opened? Is this related to a requirement that affects a specific workbook? If so, why not use the Open event for that workbook only?

[Bob Phillips]

[vba]

Public WithEvents App As Application

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

Private Sub Workbook_Open()
Set App = Application
End Sub
[/vba]

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[jov_damo86]

i created a workbook named "HELLO" that we currently using in our company. I want this workbook to be unusable after 3 months. unfortunately, forgot to create a code that this workbook("HELLO") expired after 3 months...

my remedy now is to detect if this workbook is opened i want to prompt the user using msgbox and immediately close this workbook.

since this workbook is saved in a CD, any time they could open this workbook, so i tried to create a personal.xls or add-in that will detect this workbook if ever it will be opened. I need a code ralated to this..any one could help please...

I need your help...

[Bob Phillips]

So check my code.

[jov_damo86]

thank you XLD... but your code works for specific workbook only(where you place that code).

what I'am asking is a code that detect the other workbook("HELLO" for specific) inside add-ins or personal.xls

if HELLO workbook is open for example,I am thinking of using a trigger like a mouse click,copy or paste to detect that it is HELLO is open but I don't know how.

[mikerickson]

You could write an application level OpenWorkbook event.

Create a class module called Class1 and put this code in it.[VBA]Public WithEvents thisInstance As Application

Private Sub thisInstance_WorkbookOpen(ByVal Wb As Excel.Workbook)
MsgBox "You have opened " & Wb.Name
End Sub[/VBA]
and this in a normal module[VBA]Public myAppLevelEvents As New Class1

Sub makeALE()
Set myAppLevelEvents.thisInstance = Application
End Sub[/VBA]After running makeALE, every time a workbook is opened, a message will appear (until Excel is closed. makeALE will have to be called every time Excel is opened.)

[Bob Phillips]

thank you XLD... but your code works for specific workbook only(where you place that code).

what I'am asking is a code that detect the other workbook("HELLO" for specific) inside add-ins or personal.xls

if HELLO workbook is open for example,I am thinking of using a trigger like a mouse click,copy or paste to detect that it is HELLO is open but I don't know how.

You obviously haven't tried it, it works for all workbooks.

[Bob Phillips]

You could write an application level OpenWorkbook event.

As I showed him yesterday!

[jov_damo86]

Im very sorry XLD, Your work also work... I didn't realize it, but now its working. thank you!

mikerickson give a really defined code.. Thank you also...

In conclusion, both codes works.... Thank you!

this thread considered SOLVE.