Workbook_Open Event

[The_Vulcan]

Is there anyway to force macro's to run upon opening a workbook?
Or force the workbook to close if they don't enable macro's ?


I would like to password protect a workbook so that when opened it prompts for a user name and password.
(hard coded in VBA)

If you don't enter the correct information it closes the workbook.

If you do, it saves user name and time to a hidden sheet (log).


I want to keep track of who opens the workbook, so that it can be traced back to them if they stuff it.

[The_Vulcan]

As far as I can tell the only way around this is the "Hide All Sheets" on close.
So that they get a splash page asking them to close the workbook and re-open it with macro's enabled.

Option Explicit

Const WelcomePage = "Macros"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False

'Evaluate if workbook is saved and emulate default propmts
With ThisWorkbook
If Not .Saved Then
Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
vbYesNoCancel + vbExclamation)
Case Is = vbYes
'Call customized save routine
Call CustomSave
Case Is = vbNo
'Do not save
Case Is = vbCancel
'Set up procedure to cancel close
Cancel = True
End Select
End If

'If Cancel was clicked, turn events back on and cancel close,
'otherwise close the workbook without saving further changes
If Not Cancel = True Then
.Saved = True
Application.EnableEvents = True
.Close savechanges:=False
Else
Application.EnableEvents = True
End If
End With
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False

'Call customized save routine and set workbook's saved property to true
'(To cancel regular saving)
Call CustomSave(SaveAsUI)
Cancel = True

'Turn events back on an set saved property to true
Application.EnableEvents = True
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_Open()
'Unhide all worksheets
Application.ScreenUpdating = False
Call ShowAllSheets
Application.ScreenUpdating = True
End Sub

Private Sub CustomSave(Optional SaveAs As Boolean)
Dim ws As Worksheet, aWs As Worksheet, newFname As String
'Turn off screen flashing
Application.ScreenUpdating = False

'Record active worksheet
Set aWs = ActiveSheet

'Hide all sheets
Call HideAllSheets

'Save workbook directly or prompt for saveas filename
If SaveAs = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
Else
ThisWorkbook.Save
End If

'Restore file to where user was
Call ShowAllSheets
aWs.Activate

'Restore screen updates
Application.ScreenUpdating = True
End Sub

Private Sub HideAllSheets()
'Hide all worksheets except the macro welcome page
Dim ws As Worksheet

Worksheets(WelcomePage).Visible = xlSheetVisible

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
Next ws

Worksheets(WelcomePage).Activate
End Sub

Private Sub ShowAllSheets()
'Show all worksheets except the macro welcome page

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
Next ws

Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub

[Bryan Hessey]

Is there anyway to force macro's to run upon opening a workbook?
Or force the workbook to close if they don't enable macro's ?


I would like to password protect a workbook so that when opened it prompts for a user name and password.
(hard coded in VBA)

If you don't enter the correct information it closes the workbook.

If you do, it saves user name and time to a hidden sheet (log).


I want to keep track of who opens the workbook, so that it can be traced back to them if they stuff it.

Hi,

Before closing veryhidden all sheets except the face.

On the face have the wording displayed that this book cannot be opened without Macros enabled, and to close the book and re-open with macros enabled.

On workbook_open then, after checking the user, veryhidden the face sheet, and un-veryhidden the remaining sheets, if the user is unauthorused unhidden face2 (you are nnot auth, close the book)

Check the required names from
ActiveCell.Value = Application.UserName
and log that plus the 'now' to your hidden sheet.

Let me know how you go.
---

[Bryan Hessey]

As far as I can tell the only way around this is the "Hide All Sheets" on close.
So that they get a splash page asking them to close the workbook and re-open it with macro's enabled.

Pretty much as you have shown, with the 'user' test if required, can be useful for restricting users from some sheets.

This is, of course, Excel security, and will restrict the average office worker, if you want stronger protection keep the file on a laptop not connected to a network.

hth
---

[The_Vulcan]

I had it set so that.

Disable Macros = "Need Macro's On" sheet with everything else hidden.

Enable Macros = "Must enter password" sheet with everything else hidden, in case they closed the password box.

But I have now been told that it is too had for them to enter a user name and password. WTF ?

So I have now resorted to:

Private Sub Workbook_Open()

Module1.Log

End Sub

Sub Log()
Temp1 = Environ("USERNAME")
Temp2 = Date
Temp3 = Now

ActiveWorkbook.Sheets("Log").Activate
Range("B6").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = Temp1
ActiveCell.Offset(0, 1).Select

ActiveCell.Value = Temp2
ActiveCell.Offset(0, 1).Select

ActiveCell.Value = Temp3
ActiveCell.Offset(0, 1).Select

End Sub

That should be enough to point the finger at the person who screws up the sheet or stuffs up the data.
And I kept the Disable Macros = "Need Macro's On" sheet.

Thanks for the Replies guys and Merry Xmas to you....

Though I am more of a Bar Humbug kinda guy.

EDIT:
Now to just add the save command to archive the file on open, so that when they stuff it.....................

[MSP77079]

Ya know, you can log user ID without the user doing anything.

Try this in VBA:

Please Login or Register  to view this content.