See this thread, particularly Tom U's post.
This is a discussion on How do I allow only formula driven values in a cell? within the Excel Questions forums, part of the Question Forums category; I would like VBA code help for this problem. I'm trying to limit the data that can populate a range ...
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 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.
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.EnableEvents = True
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
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 ]
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.
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.