Workbook_Open event do not working?

beginner · #1 04-10-2013, 01:23 PM

On this link I wrote about my problem and I attach my file *. Xlsm
When I save my Workbook as *. Xlsm in this case Workbook_Open event is functioning correctly.
If I save in the file format *. Xls, in this case on re-opening the file Workbook_Open event that does not work, nothing happens?

I tried to run the file in *. Xls format and after that nothing happens I've tried to run the following VBA code to check the event

Code:

Sub EnableEventsNow()
    Dim sMsg As String
    With Application
        sMsg = IIf(.EnableEvents, _
            "Events were already enabled.", _
            "Events are now enabled.")
        .EnableEvents = True
    End With
    MsgBox sMsg
End Sub

After activating this code (I put the cursor inside the VBA code) by pressing F5 on my keyboard, then I get the message "Events were allready enabled" means that the event has started?

Can someone help me and explain why it is not working Workbook_Open event in *. XLS file format

Improved thanks for any help.

macropod · #2 04-10-2013, 02:41 PM

The issue may be that your Workbook_Open code has content that isn't supported in the xls format. Without seeing the code, it's impossible to say for sure what the issue is.

beginner · #3 04-11-2013, 01:22 AM

Quote:

Originally Posted by macropod

The issue may be that your Workbook_Open code has content that isn't supported in the xls format. Without seeing the code, it's impossible to say for sure what the issue is.

Thanks for answering. I attach my file in XLS format vith VBA code

I tried also a simple VBA in my XLS file (See second attach)

Code:

Private Sub Workbook_Open()
Sheets("Sheet1").Activate
End Sub

but nothing happens

Thank you, that you have a desire to help me

macropod · #4 04-11-2013, 02:38 AM

Your 'Sheet1 Active' workbook works fine for me - immediately it is opened it switches from Sheet3 to Sheet1. Same behaviour for both Excel 2010 and Excel 2003.

Your limitdate workbook's macro falls over because you haven't declared the 'limitdate' variable. Furthermore, you don't need the 'Application.EnableEvents = True' code as it does nothing in the context in which you have it.

You have numerous comments in your files about what you're trying to achieve. I don't propose to do your development work for you. However, you might find the following code, which I've posted elsewhere, useful.

The following code works by automatically hiding all except a 'main' worksheet upon opening and closing. It then asks the user for their worksheet name and password. If the correct password for the named sheet is given, it is displayed and unprotected; otherwise the prompt is repeated. If the prompts are left empty, the code does nothing more and exits.

The code is also written around the principle that the workbook itself should be protected - see the relevant comments in the code. This is to protect its structure etc, so that you won't run into problems with sheets being renamed etc.

You may want to add more code to trap 'Save' events, in case the user saves the workbook with their worksheet visible and a subsequent user opens the workbook with macros disabled.

Code:

Option Explicit
Dim User As String, UPwd As String, WPwd As String
Dim Err As Boolean, wsSheet As Worksheet, wsActvSht As Worksheet
Private Sub Workbook_Open()
WPwd = "" 'Inset the Workbook Password between the double quotes
Set wsActvSht = ThisWorkbook.Sheets("Sheet1") ' A worksheet that must remain visible
If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd
wsActvSht.Activate
For Each wsSheet In ActiveWorkbook.Worksheets
  With wsSheet
    If .Name = wsActvSht.Name Then
      .Visible = xlSheetVisible
    Else
      .Visible = xlSheetVeryHidden
    End If
  End With
Next wsSheet
Restart:
User = InputBox("Please Input your Worksheet Username")
UPwd = InputBox("Please Input your Worksheet Password")
For Each wsSheet In ActiveWorkbook.Worksheets
  With wsSheet
    If .Name = User Then
      On Error GoTo Restart
      If .ProtectContents = True Then .Unprotect UPwd
      .Visible = xlSheetVisible
      .Activate
      Exit Sub
    End If
  End With
Next wsSheet
ThisWorkbook.Protect Password:=WPwd, Structure:=True, Windows:=True
End Sub

Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd
For Each wsSheet In ActiveWorkbook.Worksheets
  With wsSheet
    If .Name = User Then .Protect UPwd
    If .Name <> wsActvSht.Name Then .Visible = xlSheetVeryHidden
  End With
Next wsSheet
wsActvSht.Activate
ThisWorkbook.Protect Password:=WPwd, Structure:=True, Windows:=True
End Sub

beginner · #5 04-11-2013, 01:09 PM

Quote:

Originally Posted by macropod

Your 'Sheet1 Active' workbook works fine for me .......................
You have numerous comments in your files about what you're trying to achieve. I don't propose to do your development work for you.

Thank you for answering and responding
I will not ask you that you developed for me and your wasting your time.

Thank you for this macro code that you gave me. I will examine this VBA code.
For all other problems, I will help look for internet surfing

Forget the comments in my VBE, go to solve this problem based on topic
My Excel or Office 2007 does not correct?
I using Excel 2007 SP3 (12.0.6661.5000)-(12.0.6607.1000)

When I start XLS file format Workbook_Open () does not work.
How do I solve this problem?
What should I do, that I can using XLS file format

macropod · #6 04-11-2013, 01:14 PM

Have you checked your macro security settings (Developer>Macro Security) to confirm that you can run macros? It should be set to the second option: 'Disable all macros with notification'.

beginner · #7 04-11-2013, 01:25 PM

Quote:

Originally Posted by macropod

Have you checked your macro security settings (Developer>Macro Security) to confirm that you can run macros? It should be set to the second option: 'Disable all macros with notification'.

I allow all macros in Excel Options
I have set my location "C :/ User / Downloads /" as a Trusted Location

BTW: If I using XLSM format file, then all is OK, Workbook_Open working without problems

macropod · #8 04-11-2013, 01:54 PM

Your security settings are most unwise. You should not use 'Enable all macros' and you should not check the 'Trust access ..' option. With those settings, a macro virus could do a lot of damage to your system.

As for your problems with xls files only, that suggests there may be a problem with your Office installation. Try repairing the Office installation (via Excel Options|Resources|Diagnose).

beginner · #9 04-11-2013, 02:37 PM

Quote:

Originally Posted by macropod

As for your problems with xls files only, that suggests there may be a problem with your Office installation. Try repairing the Office installation (via Excel Options|Resources|Diagnose).

Thank you for the idea
MS Office Diagnostics has solved my problem.
Workbook_Open() event is now working

Thanks for Your answers

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Duplicate event reminders	rgarneau	Outlook	0	01-23-2012 08:58 AM
Create calendar event	groegee	Outlook	1	12-05-2011 09:56 PM
Catch event before next slide	PetLahev	PowerPoint	0	10-21-2011 03:29 AM
Event Log - Outlook Crashed	Davva	Outlook	0	11-10-2009 01:35 PM
Problem with Workbook_Open with CustomUI	birdseye42	Excel	0	08-26-2009 11:59 AM