Hi,

I have tried the following code, but doesn't work unless I protect the
sheet:
Range("A1:A4").Select
Selection.Locked = True
Selection.FormulaHidden = False

If I protect the sheet, then it works fine, but how can I make a cell
read only
WITHOUT protecting the sheet ??

Any help is appreciated.

Thanks.

You can't, sorry.

Best wishes Harald

"ndalal" skrev i melding
ups.com...

If I protect the sheet, then it works fine, but how can I make a cell
read only
WITHOUT protecting the sheet ??

Technically you can't, but depending on your needs you can do something
as follows, where "F7" is the cell you want protected:

Private Sub Worksheet_Change(ByVal Target As Range)
Range("F7").Value = "Your Formula or Text Goes Here"
End Sub

Harald Staff Wrote:
You can't, sorry.

Best wishes Harald

"ndalal" skrev i melding
ups.com...

If I protect the sheet, then it works fine, but how can I make a
cell
read only
WITHOUT protecting the sheet ??


--
Ikaabod
------------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371
View this thread: http://www.excelforum.com/showthread...hreadid=540414

Also, if you're trying to prevent people from knowing what formula
you're using I would suggest having the macro calculate the formula
(instead of having the formula in the cell) and just put a value in the
cell and then you can lock your macro.


--
Ikaabod
------------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371
View this thread: http://www.excelforum.com/showthread...hreadid=540414

Hi,
You could use a Worksheet_Change Sub to kick the user out of A1:A4
should they ever try to click into any of those four cells.

If that would do then just paste this into that worksheet's code
module...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(ActiveCell, Range("A1:A4")) Is Nothing Then
Range("B1").Select
End If
End Sub

Ken Johnson

This is also true, but be careful when using this method because you can
still select B4:A1 and delete/edit the contents.

Ken Johnson Wrote:
Hi,
You could use a Worksheet_Change Sub to kick the user out of A1:A4
should they ever try to click into any of those four cells.

If that would do then just paste this into that worksheet's code
module...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(ActiveCell, Range("A1:A4")) Is Nothing Then
Range("B1").Select
End If
End Sub

Ken Johnson


--
Ikaabod
------------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371
View this thread: http://www.excelforum.com/showthread...hreadid=540414

Hi Ikaabod,

Good point!

How about...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Selection, Range("A1:A4")) Is Nothing Then
Range("B1").Select
End If
End Sub

Ken Johnson

Thanks a lot for all the good points. I will try them out today and let
you guys know.

That was great help....really appreciate it.

Ken, do you have a way to make the whole column read only?

Ikaabod, your solution works only if the cell has fixed data, however
my data keeps changing in those cells so Ken's solution works better.

- Nirav

Ken's last suggestion is probably the best. If you want the entire
column just change:
If Not Intersect(Selection, Range("A1:A4"))
to
If Not Intersect(Selection, Range("A:A"))

ndalal Wrote:
Ken, do you have a way to make the whole column read only?

Ikaabod, your solution works only if the cell has fixed data, however
my data keeps changing in those cells so Ken's solution works better.

- Nirav


--
Ikaabod
------------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371
View this thread: http://www.excelforum.com/showthread...hreadid=540414