Visual Basic in Excel 2007

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ChuckN - 05 Apr 2008 17:47 GMT

I have used a visual basic routine to save the date and time when I save an
Excel file.

Private Sub Workbook_BeforeSave(By Val SaveAsUI As Boolean, Cancel As
Boolean)
Worksheets("Worksheet name").Range("Celled").Value = Now()
End Sub

I recently saved the file as an *.xlsm file with a password required to open
the file. (A password is not required to modify the file)

As long as the file is password protected, the routine does not work. As
soon as I remove the password to open the file, the routine works as it
normally does.

In the trust center:

I have enabled all Macros.

I have checked "Trust access to the VBA ..."

What is happening?

Is there a solution?

I think Microsoft is making security so complicated that most people will
have to undo much of the security to figure out how to get things to work.
I suppose Microsoft can claim that is not their fault that there are
security problems. The tools are there to protect the files, the end users
are just not using them properly.

Reply to this Message

Rick Rothstein (MVP - VB) - 05 Apr 2008 18:47 GMT

Try it this way (substituting your actual password for the "test" one I
used)...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
With Worksheets("Sheet1")
.Unprotect "test"
.Range("Celled").Value = Now()
.Protect "test"
End With
End Sub

You might have to play around with the optional arguments for the Protect
method (see the help files) in order to customize the level of protection.

Rick

>I have used a visual basic routine to save the date and time when I save an
>Excel file.
[quoted text clipped - 26 lines]
> security problems. The tools are there to protect the files, the end
> users are just not using them properly.

Reply to this Message

ChuckN - 05 Apr 2008 19:36 GMT

Good thought, but it still does not work. When I removed the password
protection for accessing the file, it did work. When I added the password
protection to open the file and saved the file, it continued working until I
closed the file. When I reopened the file using the password, it stopped
working.

> Try it this way (substituting your actual password for the "test" one I
> used)...
[quoted text clipped - 43 lines]
>> are security problems. The tools are there to protect the files, the end
>> users are just not using them properly.

Reply to this Message

Rate this thread: