PDA

View Full Version : [SOLVED] Protecting cells



SailFL
08-07-2005, 05:02 AM
I have a spreadsheet that I would like to be able to password protect individual cells. Having done some research, I have learned that the newer version of Excel has that capability but is it possible to do the same thing using VBA?

Thanks

johnske
08-07-2005, 05:45 AM
Hi SailFL, and welcome to VBAX,

Yes, you can use VBA to protect (lock) cells, here's an example


Option Explicit

Sub TryThis()
With Sheet1
.Unprotect
'unlock all cells on worksheet
Cells.Locked = False
'put your own range(s) to lock below
Range("A1:C10, F1:F10, H1").Locked = True
.Protect
End With

HTH,
John :)

SailFL
08-07-2005, 09:44 AM
John,

Thanks that does work but I need to be able to password protect the fields so that once they are protected others that use the sheet can not have access to the formulas that have been used to calculate the field. We want to protect propritary information. If you point me in the direction of some examples, I would appreicate that also.

Thanks

SailFL
08-07-2005, 11:15 AM
John,

Never mind. I was able to figure out how to protect with a password.
Thanks for your code.

SailFL
08-07-2005, 04:02 PM
John,

After thinking about my problem, I realize that need some additional help. What I want to achieve is to be able to protect the formula used for a cell so that they can not be changed but I also want to stop them from being copied or used. I formulas are propritary. I can't hid the cell because the data needs to be looked at. How can I achieve what I want?

Is it possible to embed a formula that is not seen?

Is it possible to do what I want?

Thanks

SailFL
08-07-2005, 04:37 PM
I keep answering my own questions but please look at the code. The protect in working fine and I can hide formuls but I only want to hide a range of formulas and the code I am including hides the formulsa for the whole column.

What am I doing wrong.
Thanks


Sub TryThis()
With Sheet1
.Unprotect
'unlock all cells on worksheet
Cells.Locked = False
'This hides the Formula for Column B
'Worksheets("Sheet1").Columns("B").FormulaHidden = True
'But I want to only hide the Formulas in cells B3:B5 but it is
'hidding the formulas in all of column B
Worksheets("Sheet1").Range("B3:B5").FormulaHidden = True
'put your own range(s) to lock below
Range("B3:B5").Locked = True
.Protect ("mypass")
End With
End Sub

johnske
08-07-2005, 06:38 PM
John,

After thinking about my problem, I realize that need some additional help. What I want to achieve is to be able to protect the formula used for a cell so that they can not be changed but I also want to stop them from being copied or used. I formulas are propritary. I can't hid the cell because the data needs to be looked at. How can I achieve what I want?

Is it possible to embed a formula that is not seen?

Is it possible to do what I want?

ThanksHi,

There's no way that I know of to securely protect worksheet coding as you suggest. By its very nature such coding can be readily seen whenever the worksheet's unprotected, but some protection can be provided as follows.


Option Explicit

Sub TryThis()
With Sheet1
.Unprotect password:="123"
'unlock all cells on worksheet
Cells.Locked = False
'put your own range(s) to lock below
With Range("A1:C10, F1:F10")
.Locked = True
.FormulaHidden = True
End With
.Protect password:="123"
End With
End Sub


If you do all the coding as VBA coding you stand a chance of making it more secure, as it's unseen (if you password protect your project). However this still only provides a degree of security, that can still be broken by those that really know what they're doing.

HTH,
John :)

SailFL
08-08-2005, 01:44 AM
I have realized that these changes would not be completely secure but it will protect from most people.

Thank you for your assistance.

excelliot
08-09-2005, 04:23 AM
So u wants to protect cell & also hide the content of the cell so that user can not figure it out how u have extracted details in the cell.

Why dont u
first lock all cells which contents u wants to hide
then protect sheet with password