How to display message with Macro in Excel

[Observer]

I have to design an excel spreadsheet with a range of cells with formula.

I would like to have a dispaly of message " This cell contains formula.
Please do not type here" when a user cliks on a range of cells with formula.

 

[Alan]

It would be easier to format the cells under 'Protection' to locked and
hidden then password protect the sheet. Then the user can't select the cells
at all. If you want them to see the formulas, just check locked and leave
hidden unchecked.
Regards,
Alan.

 

[Observer]

Thanks for your prompt response.

However, I need to show them the message while they select on it. I was
aware of one method with Macro that would pop up a message that the
particular "cell is protected, it contained formula and please do not type
on it ".

I would really appreciate the help

 

[Dave Peterson]

I think using worksheet protection is much easier and safer (macros can be
disabled).

But if you want:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim FormulaCells As Range

Set FormulaCells = Nothing
On Error Resume Next
Application.EnableEvents = False
Set FormulaCells = Intersect(Target, _
Target.Cells.SpecialCells(xlCellTypeFormulas), _
Me.UsedRange)
Application.EnableEvents = True
On Error GoTo 0

If FormulaCells Is Nothing Then
'ok
Else
MsgBox "Your selection contains formulas"
Application.EnableEvents = False
Application.Goto Me.Range("a1")
Application.EnableEvents = True
End If

End Sub


If you want to try, rightclick on the worksheet tab that should have this
behavior. Select view code. Paste this into the newly opened code window.

I went back to A1. You should change that to where you want them to be if they
selected a range with at least one cell with a formula.

Then back to excel to test it out.

Ps. Lots of things get disabled when you run macros. Try the undo or redo
buttons.

 

[Observer]

Thank you Dave for the very kind support.
Have a great Day
Observer