Workbook_Open event do not working?

macropod · #1 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 · #2 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

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