Visual Basic in Excel 2007

  • Thread starter Thread starter ChuckN
  • Start date Start date
C

ChuckN

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