Locking Cells
 

Hi
Please help me for following questions :
1. Function/marco/formula to unlock only 'Blue Text' and all other should be
locked so that nobody can change the formula or overwrite the formulas
2. Function/Macro/Formula to see only result of formula, no formula could be
seen by unauthorized person.

Please help
Thanks
Jai

#1. With your worksheet unprotected:

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range

With ActiveSheet
Set myRng = .UsedRange
.Cells.Locked = True
End With

For Each myCell In myRng.Cells
If myCell.Font.ColorIndex = 5 Then
myCell.Locked = False
End If
Next myCell

End Sub

#2. When you lock the cells, you can hide the formulas.
Format|cell|protection tab|check locked and hidden.

Then protect the worksheet.

ps. Worksheet protection is very, very easy to break.

Jai wrote:

Hi
Please help me for following questions :
1. Function/marco/formula to unlock only 'Blue Text' and all other should be
locked so that nobody can change the formula or overwrite the formulas
2. Function/Macro/Formula to see only result of formula, no formula could be
seen by unauthorized person.

Please help
Thanks
Jai

--

Dave Peterson

Hi Dave,
Thanks
Option 2 is working for me but then I am unable to write in Blue cells.
Option 1 macro, I tried it, but I am not sure why it's not working.
Please help.
THanks
Jai

"Dave Peterson" wrote:

#1. With your worksheet unprotected:

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range

With ActiveSheet
Set myRng = .UsedRange
.Cells.Locked = True
End With

For Each myCell In myRng.Cells
If myCell.Font.ColorIndex = 5 Then
myCell.Locked = False
End If
Next myCell

End Sub

#2. When you lock the cells, you can hide the formulas.
Format|cell|protection tab|check locked and hidden.

Then protect the worksheet.

ps. Worksheet protection is very, very easy to break.

Jai wrote:

Hi
Please help me for following questions :
1. Function/marco/formula to unlock only 'Blue Text' and all other should be
locked so that nobody can change the formula or overwrite the formulas
2. Function/Macro/Formula to see only result of formula, no formula could be
seen by unauthorized person.

Please help
Thanks
Jai

--

Dave Peterson

There are lots of different shades of blue.

Select one of those blue cells and hit alt-f11 (to get to the VBE).

Then hit ctrl-g to see the immediate window.

Type this and hit enter:

activecell.font.colorindex

Use that same number (I used 5, who knows if that was correct) in that routine.

Jai wrote:

Hi Dave,
Thanks
Option 2 is working for me but then I am unable to write in Blue cells.
Option 1 macro, I tried it, but I am not sure why it's not working.
Please help.
THanks
Jai

"Dave Peterson" wrote:

#1. With your worksheet unprotected:

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range

With ActiveSheet
Set myRng = .UsedRange
.Cells.Locked = True
End With

For Each myCell In myRng.Cells
If myCell.Font.ColorIndex = 5 Then
myCell.Locked = False
End If
Next myCell

End Sub

#2. When you lock the cells, you can hide the formulas.
Format|cell|protection tab|check locked and hidden.

Then protect the worksheet.

ps. Worksheet protection is very, very easy to break.

Jai wrote:

Hi
Please help me for following questions :
1. Function/marco/formula to unlock only 'Blue Text' and all other should be
locked so that nobody can change the formula or overwrite the formulas
2. Function/Macro/Formula to see only result of formula, no formula could be
seen by unauthorized person.

Please help
Thanks
Jai

--

Dave Peterson


--

Dave Peterson

Locking Cells
 

hi Dave!

when i try this it's not working,it say that "compiled error" & "inavalid
use of property"..
can help us how enable this macro..

a lot thanks in advance..

kiboypalaboyD;

"Dave Peterson" wrote:

There are lots of different shades of blue.

Select one of those blue cells and hit alt-f11 (to get to the VBE).

Then hit ctrl-g to see the immediate window.

Type this and hit enter:

activecell.font.colorindex

Use that same number (I used 5, who knows if that was correct) in that routine.

Jai wrote:

Hi Dave,
Thanks
Option 2 is working for me but then I am unable to write in Blue cells.
Option 1 macro, I tried it, but I am not sure why it's not working.
Please help.
THanks
Jai

"Dave Peterson" wrote:

#1. With your worksheet unprotected:

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range

With ActiveSheet
Set myRng = .UsedRange
.Cells.Locked = True
End With

For Each myCell In myRng.Cells
If myCell.Font.ColorIndex = 5 Then
myCell.Locked = False
End If
Next myCell

End Sub

#2. When you lock the cells, you can hide the formulas.
Format|cell|protection tab|check locked and hidden.

Then protect the worksheet.

ps. Worksheet protection is very, very easy to break.

Jai wrote:

Hi
Please help me for following questions :
1. Function/marco/formula to unlock only 'Blue Text' and all other should be
locked so that nobody can change the formula or overwrite the formulas
2. Function/Macro/Formula to see only result of formula, no formula could be
seen by unauthorized person.

Please help
Thanks
Jai

--

Dave Peterson


--

Dave Peterson

Locking Cells
 

You're going to have to help.

Share the code you're using (I suspect that you made a change).

And indicate the lines that cause trouble.

kiboypalaboy wrote:

hi Dave!

when i try this it's not working,it say that "compiled error" & "inavalid
use of property"..
can help us how enable this macro..

a lot thanks in advance..

kiboypalaboyD;

"Dave Peterson" wrote:

There are lots of different shades of blue.

Select one of those blue cells and hit alt-f11 (to get to the VBE).

Then hit ctrl-g to see the immediate window.

Type this and hit enter:

activecell.font.colorindex

Use that same number (I used 5, who knows if that was correct) in that routine.

Jai wrote:

Hi Dave,
Thanks
Option 2 is working for me but then I am unable to write in Blue cells.
Option 1 macro, I tried it, but I am not sure why it's not working.
Please help.
THanks
Jai

"Dave Peterson" wrote:

#1. With your worksheet unprotected:

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range

With ActiveSheet
Set myRng = .UsedRange
.Cells.Locked = True
End With

For Each myCell In myRng.Cells
If myCell.Font.ColorIndex = 5 Then
myCell.Locked = False
End If
Next myCell

End Sub

#2. When you lock the cells, you can hide the formulas.
Format|cell|protection tab|check locked and hidden.

Then protect the worksheet.

ps. Worksheet protection is very, very easy to break.

Jai wrote:

Hi
Please help me for following questions :
1. Function/marco/formula to unlock only 'Blue Text' and all other should be
locked so that nobody can change the formula or overwrite the formulas
2. Function/Macro/Formula to see only result of formula, no formula could be
seen by unauthorized person.

Please help
Thanks
Jai

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson