Solved

Locked Excel file is unlocked when re-opened

Posted on 2011-02-15
12
910 Views
Last Modified: 2012-05-11
I use the following code to lock a new file and then close it.  When I open it is unlocked and headings are visible.  The code works fine as I step through it...but the file is not locked when I open it. Can someone tell me what I am doing wrong? Thanks
Workbooks.Add
ActiveWorkbook.SaveAs Filename:= ...etc
With ActiveWindow
.DisplayHeadings = False
.DisplayVertivleScrollBar = False
.DisplayWorkTabs = False
End With
ActiveSheet.Protec Password = 1234
ActiveWorkbook.Close SaveChanges = True

Open in new window

0
Comment
Question by:davidam
  • 7
  • 3
  • 2
12 Comments
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 100 total points
Comment Utility
maybe you misspelled the word protect on line 8?

ActiveSheet.Protec Password = 1234
0
 
LVL 4

Assisted Solution

by:florjan
florjan earned 150 total points
Comment Utility
I belive you had a typo. So it's Protect not Protec.
ActiveSheet.Protect Password = 1234

Open in new window

0
 

Author Comment

by:davidam
Comment Utility
No, it is correct in the code.  I typed it on a different computer for this.  When I step throught the code it locks the file and then closes it...it is unlocked when I open it.
0
 

Author Comment

by:davidam
Comment Utility
Further to the above...and probably a very good clue...I stepped through the code again...right up until the last line (close save)..then I suspended the macro and manually closed the file.  Upon opening it it was fully locked.
0
 
LVL 4

Assisted Solution

by:florjan
florjan earned 150 total points
Comment Utility
ActiveWorkbooks.Close SaveChanges = True

Open in new window

Workbooks not workbook?
0
 

Author Comment

by:davidam
Comment Utility
Actually I think I need ActiveWorkbooks.Close SaveChanges:= True
...what I really need is for this project to end!!!  Thanks to all.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
Seems that florjan should get a little credit for finding the answer, but maybe not 500 points...I guess it depends on whether author would have found the answer without EE help.
0
 

Author Comment

by:davidam
Comment Utility
I have no objection to rewarding points.  I accepted my solution because I did not want anyone to waste any more time on this and because I had indeed discovered the true issue.  rspahitz was certainly zeroing in on the line that was causing the problem.  I am new to this and I would like someone else to decide how many points should be awarded.
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
I would suggest 100 pts awarded to florjan, with grade A if it worked as delivered (or with minor change)
0
 

Author Comment

by:davidam
Comment Utility
That sounds good to me...but I do not know how to do this.
0
 

Accepted Solution

by:
davidam earned 0 total points
Comment Utility
This is a syntax problem, the solution is
ActiveWorkbooks.Close SaveChanges:= True
0
 

Author Closing Comment

by:davidam
Comment Utility
Both spahitz and florjan found an error in my code; ultimately it was a different error that was the problem
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now