Link to home
Start Free TrialLog in
Avatar of davidam
davidamFlag for Canada

asked on

Locked Excel file is unlocked when re-opened

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

SOLUTION
Avatar of rspahitz
rspahitz
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of davidam

ASKER

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.
Avatar of davidam

ASKER

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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of davidam

ASKER

Actually I think I need ActiveWorkbooks.Close SaveChanges:= True
...what I really need is for this project to end!!!  Thanks to all.
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.
Avatar of davidam

ASKER

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.
I would suggest 100 pts awarded to florjan, with grade A if it worked as delivered (or with minor change)
Avatar of davidam

ASKER

That sounds good to me...but I do not know how to do this.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of davidam

ASKER

Both spahitz and florjan found an error in my code; ultimately it was a different error that was the problem