473,327 Members | 1,896 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Excel protect sheet macro problem

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
1 4542
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Steven Stewart | last post by:
I have a user who has been using Excel for a while to keep statistics and print reports. She finds using it cumbersome because of long formulas and a lot of copying and pasting. I have designed...
8
by: Nick M | last post by:
Hello All, Excellent info here Thanks! I am very new to using access in general and I am on a learning curve. I'm trying to import an excel workbook (with worksheets) into an access db via a...
14
by: pmud | last post by:
Hi, I need to use an Excel Sheet in ASP.NET application so that the users can enter (copy, paste ) large number of rows in this Excel Sheet. Also, Whatever the USER ENETRS needs to go to the...
4
by: Rich Wallace | last post by:
Is there a way to open an Excel file and either respond to or supress the Macro warning window via VB.NET? Dim oExcel As Excel.Workbook Dim sFilePath As String = "C:\DailyReport.xls" oExcel...
4
by: Jiro Hidaka | last post by:
Hello, I would like to know of a fast way to export data source data into an Excel sheet. I found a way from C# Corner(Query Tool to Excel using C# and .NET) which is a neat little way of...
4
by: e.h.doxtator | last post by:
All I'm a Python newbie, and I'm just getting to the wonders of COM programming. I am trying to programmatically do the following: 1. Activate Excel 2. Add a Workbook 3. Add a Worksheet...
4
by: shara | last post by:
Hello, I have a php script that outputs an excel sheet.The user has to download macro every time he wants to run the macro on the excel sheet. Is there anything in php where the code downloads...
0
by: Taxman | last post by:
Windows XP, MS Office Excel 2003 If the tasks, I’m trying accomplish have been addressed previously (separately or in combination). Please, provide the links or keyword search to find them. I’ve...
2
by: grego9 | last post by:
Is it possible to create a macro that prompts the user to enter a password to access a specific sheet in Excel 2000? I can protect a sheet - but I cannot get a password prompt if the user tries to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.