By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,106 Members | 2,335 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,106 IT Pros & Developers. It's quick & easy.

Excel protect sheet macro problem

P: n/a
I have an Access 97 database in which I am running an Excel macro through
automation. The macro in Excel is as follows:

Worksheets("Sheet1").Protect Password:="****", DrawingObjects:=True,
Contents:=True, Scenarios:=True

When I call this macro from the database all works fine except when I
recorded the macro I deselected the "Select locked cells" option under the
"Allow all users of this worksheet to:" section but yet when I access the
worksheet I can still select a locked cell. I get a message when I do so
letting me know it is locked but I don't want the user to be able to select
locked cells.

The macro is run in a new copy of the spreadsheet every time it runs due to
a filecopy command in the database. Could this be causing the problem?

I tried using the following code in the macro:
Worksheets("Sheet1").EnableSelection = xlUnlockedCells but once the
spreadsheet is closed this code has no affect. Adding this code to the on
open event of the spreadsheet is not an option due to the users that will be
receiving this spreadsheet are told to not enable macros on all
spreadsheets. Company policy!

Does anyone have a suggestion? Is there a parameter that I can add to my
protect macro?

Any help would be greatly appreciated.
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
You need to call Worksheet.EnableSelection before you protect the sheet.

IOW you have to

On Thu, 10 Feb 2005 23:40:44 -0500, "Giganews" <he******@yahoo.com>
wrote:
I have an Access 97 database in which I am running an Excel macro through
automation. The macro in Excel is as follows:

Worksheets("Sheet1").Protect Password:="****", DrawingObjects:=True,
Contents:=True, Scenarios:=True

When I call this macro from the database all works fine except when I
recorded the macro I deselected the "Select locked cells" option under the
"Allow all users of this worksheet to:" section but yet when I access the
worksheet I can still select a locked cell. I get a message when I do so
letting me know it is locked but I don't want the user to be able to select
locked cells.

The macro is run in a new copy of the spreadsheet every time it runs due to
a filecopy command in the database. Could this be causing the problem?

I tried using the following code in the macro:
Worksheets("Sheet1").EnableSelection = xlUnlockedCells but once the
spreadsheet is closed this code has no affect. Adding this code to the on
open event of the spreadsheet is not an option due to the users that will be
receiving this spreadsheet are told to not enable macros on all
spreadsheets. Company policy!

Does anyone have a suggestion? Is there a parameter that I can add to my
protect macro?

Any help would be greatly appreciated.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.