To make a Cell Display Only

[bnageshrao]

I have a cell whose value is dependent on a combination of another cell's value and current date. I wrote the macro for this and it works fine. I want to make this cell value as a display only so that user cannot change the content of the cell. If I lock the cell then the macro doesnot work. Is there a way to make it happen. Thanks.

 

[JrClown]

I'm sure there's a better way but, what I do is Conditional Formatt that cell with the values it should be only. QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>

 

[DarkSun]

A funny way to do it is don't let the user be able to select it.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = &quot;$A$1&quot; Then
Interaction.SendKeys &quot;{down}&quot;
End If
End Sub

Another way is to let the user change it but then set it back.

Dim temp

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = &quot;$A$1&quot; Then
If Target.Value <> temp Then
Target.Value = temp
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = &quot;$A$1&quot; Then
temp = Target.Value
End If
End Sub


I'm sure the must be a way to set it to display only but I haven't found it yet.

 

[JrClown]

Great1 DarkSun QUOTE OF THE DAY
Not to know if bad; not to wish to know is worse.
<%
Jr Clown
%>

 

[Katerine]

What fun tips! :-D

Another way is to use Excel's built-in &quot;Protect Worksheet&quot; ability.. you could select the entire workbook, select Format | Cells | Protection Tab, and uncheck &quot;Locked&quot;. Then select the one cell you want to protect, and go in and check &quot;Locked&quot; for that one. Then go into Tools | Protection | Protect sheet (or workbook). Password is optional.

Don't know how all of this is done via code, though...

 

[bnageshrao]

It is not quite what i wanted. I wanted the cells value to be updated based on someother cells value and hence it should be display only so that user cannot edit that cell at all. I can lock the cell but then i cannot write the vb code for that cell because it errs.

 

[LoNeRaVeR]

Unprotect the worksheet in your macro prior to making cell changes using the Unprotect method.

ActiveSheet.Unprotect Password:=&quot;password&quot;

Protect it again at the end of the macro using the same password with the Protect method.

ActiveSheet.Protect Password:=&quot;password&quot;

Since the password can be obtained by viewing the code, protect your VBA Project with a password, as well.

LoNeRaVeR