How do I allow only formula driven values in a cell?

[Roccofan]

I would like VBA code help for this problem. I'm trying to limit the data that can populate a range of cells to only formula driven data. I don't think data validation menu options will help here. The cells already have formula's and I don't want users to manually input data.

Thanks.

RF

 

[lenze]

See this thread, particularly Tom U's post.

http://www.mrexcel.com/board/viewtopic.php?topic=15008&forum=2

 

[Roccofan]

Lenze,

Thanks for the response, but when I try the code I'm asked for a name for the macro, and when I run it I don't receive a msg box.

RF

 

[lenze]

The code needs to go in the worksheet module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address <> "$A$1" Then Exit Sub
Application.EnableEvents = False
MsgBox "Hey, leave me alone!", 48, "Sorry, I'm protected."
Application.Undo
Application.EnableEvents = True
End Sub

Since you said you had a RANGE of cells, you should name the range and change the If target address line to something like

If Intersect (Target, Range("YourRange"))is Nothing then Exit Sub

HTH

 

[Roccofan]

Lenze,

The new line you provided works. When I view the code and press F5 to run it when the cursor is in the code, I am prompted to name the macro or run the macro under the name I gave it when I was last asked for to name it. Is there a way to fix this?
This message was edited by Roccofan on 2002-08-26 11:21

 

[lenze]

I'm a little confused at why you're Running the code. The code as written when placed in the WorkSheet module runs automatically whenever a cell value is changed on the worksheet. See Paddy's explanation at the above link.

 

[Roccofan]

I'm used to running my macros when I create them. I'm not familiar with macros that run automatically. Everything works well. Thank you for all your help.

RF