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

Roccofan

Board Regular
Joined
Apr 18, 2002
Messages
61
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,218,756
Messages
6,144,302
Members
450,536
Latest member
DavidRankin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top