Jilla,

my situation:
All code runs from my Personal.xls.
I create a menu were I need to check wether a certain sheet is
available in the opened Excel workbook. The menu is created in
ThisWorkbook of the Personal.xls, but the Personal.xls does not see the
sheet in the opening workbook.

What should I do?

Thanks


--
Zurn
------------------------------------------------------------------------
Zurn's Profile: http://www.excelforum.com/member.php...o&userid=14645
View this thread: http://www.excelforum.com/showthread...hreadid=532168

So I want my workbooks to open first, and directly afterwards make my
menu (= run the code)...


--
Zurn
------------------------------------------------------------------------
Zurn's Profile: http://www.excelforum.com/member.php...o&userid=14645
View this thread: http://www.excelforum.com/showthread...hreadid=532168

What about using ActiveWorkbook, ActiveSheet, etc?

"Zurn" wrote:


So I want my workbooks to open first, and directly afterwards make my
menu (= run the code)...


--
Zurn
------------------------------------------------------------------------
Zurn's Profile: http://www.excelforum.com/member.php...o&userid=14645
View this thread: http://www.excelforum.com/showthread...hreadid=532168

This is my code: typed in ThisWorkbook of the VBAProject (Personal.xls)
____________________________________
Private Sub Workbook_Open()
Application.WindowState = xlMaximized
Application.ScreenUpdating = False
Create_Menu
Application.ScreenUpdating = True
End Sub
____________________________________

The Sub Create_Menu goes to the opening workbook and looks into a sheet
of this workbook. But the sub is executed before the book opens, and the
sub does not see the sheet he needs.

Sollution would be a code executed directly after opening, does this
exist?


--
Zurn
------------------------------------------------------------------------
Zurn's Profile: http://www.excelforum.com/member.php...o&userid=14645
View this thread: http://www.excelforum.com/showthread...hreadid=532168

Ok, finally found it!

I should have used: App_WorkbookOpen

because I am working in the application, not the workbook. Stupid me!


Now you know also it


--
Zurn
------------------------------------------------------------------------
Zurn's Profile: http://www.excelforum.com/member.php...o&userid=14645
View this thread: http://www.excelforum.com/showthread...hreadid=532168

Thanks for letting us know

"Zurn" wrote:


Ok, finally found it!

I should have used: App_WorkbookOpen

because I am working in the application, not the workbook. Stupid me!


Now you know also it


--
Zurn
------------------------------------------------------------------------
Zurn's Profile: http://www.excelforum.com/member.php...o&userid=14645
View this thread: http://www.excelforum.com/showthread...hreadid=532168

I used this example from Bob Phillips to realise it...thx Bob! Code
explanation in help does not explain it this clearly.

Bob Phillips
Using Events with the Application Object & XL 2003
------------------------------------------------------------------------
"Kevin H. Stecyk" wrote in message
...

From your response, I get the impression that I need to ""Dim X as a
New
EvenClassModule" and the Sub InitializeApp()". I am testing that
statement
to see whether you agree or not. If I don't do that, then what
happens?

Without this, nothing happens, because as Chip says, all you have is
an
object template, you will not have created an instance of that oject.

For example, let's say I don't "instantiate" the class module, will
Create
a Private Sub App_WorkbookOpen(ByVal Wb As Workbook)" still
function?
CanI still set my global variable?

Sub InitializeApp()
Set X.App = Application
End Sub

If you don't Dim X, this statement will error.

This goes in the normal (non-class) module, correct? What calls "Sub
InitializeApp()"?

I tend to do it in a normal workbook_Open event for that workbook. This
is
my example post on App Events.

Firstly, all of this code goes in the designated workbook.

'========================================
Insert a class module, rename it to
'clsAppEvents', with this codeOption Explicit
Public WithEvents App As Application
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
msgbox Wb.Name
End Sub
'========================================In ThisWorkbook code module,
add this event code
Dim AppClass As New clsAppEvents
Private Sub Workbook_Open()
Set AppClass.App = Application
End Sub

Either save the workbook, close it and re-open it to initaite
application
events, or just run the Workbook_Open code manually. From then on,
each
workbook opened will display the name.


--
Zurn
------------------------------------------------------------------------
Zurn's Profile: http://www.excelforum.com/member.php...o&userid=14645
View this thread: http://www.excelforum.com/showthread...hreadid=532168