Personal.xls

  • Thread starter Thread starter Zurn
  • Start date Start date
Z

Zurn

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
 
So I want my workbooks to open first, and directly afterwards make my
menu (= run the code)...
 
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?
 
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
 
I used this example from Bob Phillips to realise it...thx Bob! Code
explanation in help does not explain it this clearly.

Bob Phillips
Re: Using Events with the Application Object & XL 2003
------------------------------------------------------------------------
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
Click to expand...
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?
Click to expand...

If you don't Dim X, this statement will error.
This goes in the normal (non-class) module, correct? What calls "Sub
InitializeApp()"?
Click to expand...

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.
 
Back
Top