[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Locked Excel file is unlocked when re-opened

Posted on 2011-02-15
12
Medium Priority
?
980 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
12 Comments
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 400 total points
ID: 34899965
maybe you misspelled the word protect on line 8?

ActiveSheet.Protec Password = 1234
0
 
LVL 4

Assisted Solution

by:florjan
florjan earned 600 total points
ID: 34899969
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
ID: 34899994
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:davidam
ID: 34900044
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 600 total points
ID: 34900060
ActiveWorkbooks.Close SaveChanges = True

Open in new window

Workbooks not workbook?
0
 

Author Comment

by:davidam
ID: 34900224
Actually I think I need ActiveWorkbooks.Close SaveChanges:= True
...what I really need is for this project to end!!!  Thanks to all.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34901418
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
ID: 34901701
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
ID: 34902161
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
ID: 34902678
That sounds good to me...but I do not know how to do this.
0
 

Accepted Solution

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

Author Closing Comment

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

656 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