Solved

VBA - Excel - Locking Part of a spreadsheet

Posted on 2002-06-24
16
399 Views
Last Modified: 2010-05-02
Hello

Is there a way to lock part of an excewl spreadsheet from users amending that part.

for example I want to lock cells a1:t1200 but allow users to amend all other cells in the sheet is there a way to do this ????

Deletion etc of relevant cells shpould be prohibited.
0
Comment
Question by:marksynnott
  • 6
  • 6
  • 2
  • +1
16 Comments
 
LVL 3

Expert Comment

by:Elmo_
ID: 7104200
Mark,

You have already posted this question.  If you hit the Refresh button It will keep posting the question.  Use the Reload Question option instead.

This comment is also posted on your previous post of this question.

ed.

--------------------------------


This should allow you to do this.

By Default all cells are Locked so, If you select all cells and change the locked property to false and then select the cells you would like to protect and change their locked property back to True.  You can not protect your worksheet.

For this just go to tools/protection/protect sheet

Here is the macro you will need.

Cheers,

Ed.

Sub Protect1()
   Cells.Select
   Selection.Locked = False
   Selection.FormulaHidden = False
   Range("A1").Select
   Application.Goto Reference:="R1C1:R1200C20"
   Selection.Locked = True
   Selection.FormulaHidden = False
   ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
0
 

Author Comment

by:marksynnott
ID: 7104213
Thanks Wlmo,

On the 2nd line i gen an error

"Unable to lock property of the range class"

Sorryu I have hit the refresh in error a few times.
0
 
LVL 3

Expert Comment

by:Elmo_
ID: 7104236
Mark,

Did you enter this macro into a Module in the Visual Basic Editor for Excel?

(Open a Fresh excel Workbook. Press alt+F11.  Goto Insert/Module.  Paste the code.  Press Play)

The way I generated this code is, I recorded a macro.  I usually do this and then modify the code to suit my needs.

Its best to test code like this on a fresh sheet.

Cheers,

Ed.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:marksynnott
ID: 7104439
Thanks

I got it working. Just one more thing, anyone can unprotect it, can i add a password through VBA ???

Mark
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7104662
You can, but user could read it from VBA source code.
0
 

Author Comment

by:marksynnott
ID: 7105078
Doeas know how to eneter the password through vba

Well they wont be able to dsee the protection password as the vba code is protected by a anoither password.

Also when i send the sheet on it wont contain the vba as its an add in.

0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7105153
In VBA IDE:
go to tolls, VBA project properties.
Go to  Protection (no so strong ;) tab and there you could set the password for your project.
0
 
LVL 13

Expert Comment

by:WJReid
ID: 7105695
Hi marksynnott,

You can protect the cells without code by the following:

cover the range A1:T1200 and select data validation. From the allow box select custom and the formula box will appear. In the formula box type: =""

Click the Input Message  tab and type in a message such as "This cell is protected".

Click on the Error Alert tab and put in a message such as "You cannot enter data in this cell". Users will seee this message if they try to type anything in the cell. Set the error level from the style box, then click OK.
If you try to type anything in the cells, you will receive the error message above.

Regards,

WJReid
0
 

Author Comment

by:marksynnott
ID: 7106814
Hi All

I think there is a bit of confusion here.

I need to protect the worksheet through vba. When i do it through the menus i have an option to enter a password.


Richie
I am well aware how to lock a VBA project but that is completely different from protecting a spreadsheet
0
 
LVL 13

Expert Comment

by:WJReid
ID: 7106830
Hi marksynnott,

You do not protect the sheet through the menus with the method I have given above. You do it by Data|Validation, selecting custom and then typing in =""

You will not be asked if you want to enter a password. You can put whatever messages you like when users select the cell and a different message when they try to enter data into it.

Regards,

WJReid

0
 
LVL 3

Expert Comment

by:Elmo_
ID: 7106902
Mark,

This should Work now.

It will do as before but this time it will allow you to add a password.

If you have any more problems, just post.

Cheers,

Ed.

---------------------

Sub Protect1()
  Cells.Select
  Selection.Locked = False
  Selection.FormulaHidden = False
  Range("A1").Select
  Application.Goto Reference:="R1C1:R1200C20"
  Selection.Locked = True
  Selection.FormulaHidden = False
  ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="ED"
End Sub
0
 

Author Comment

by:marksynnott
ID: 7106903
Thanks WJ

For your help.

However the problem with that soloution is that any user can go in and remove the data validation. Also everything must be done ythrough VBA as the user clicks on a button.

Does anyone know how i look through the VBA object model to try ands see how i put a password in ??????


Thanks again
0
 
LVL 3

Accepted Solution

by:
Elmo_ earned 100 total points
ID: 7106905
Mark,

This should Work now.

It will do as before but this time it will allow you to add a password.

If you have any more problems, just post.

Cheers,

Ed.

---------------------

Sub Protect1()
 Cells.Select
 Selection.Locked = False
 Selection.FormulaHidden = False
 Range("A1").Select
 Application.Goto Reference:="R1C1:R1200C20"
 Selection.Locked = True
 Selection.FormulaHidden = False
 ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="ED"
End Sub
0
 
LVL 3

Expert Comment

by:Elmo_
ID: 7106907
Sorry about posting twice!!
0
 

Author Comment

by:marksynnott
ID: 7110158
Thanks a million, that works brilliantly.

Just a quick question

Whatr is the difference between

password = "ww"

password:= "ww"

as i tried the = on its own and it didnt work.

0
 
LVL 3

Expert Comment

by:Elmo_
ID: 7110176
As far as I can remember off hand the difference is that with:

password = "ww" - You are assigining a value to this "varaible"

password:= "ww" - You are setting a parameter which is part of an Excel command.

Hope this helps.

Ed.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA saving file message display 5 67
SSRS expression Issue finding a string 10 79
Paint/Redraw window while dragging 16 78
VBA - If Bookmark = "XXBOOKMARKXX" then 15 29
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

831 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