Identifying formulas using a Validation rule

[Bob]

A member of this forum provided the following UDF to display a formula within
a cell:

Function GetFormula(Cell As Range) As String
GetFormula = Cell.Formula
End Function

I tried creating the following “custom†Validation rule for cell A1 (which
happens to contain a formula):

=LEFT(GetFormula(A1),1)="="

but it is not working correctly (I get a “Named range cannot be found†error
message).

I want to ensure that someone doesn't inadvertently over-write a cell
containing a formula.

Can anyone show me how to do this using a Validation rule? I am aware of a
solution using Conditional Formatting, but I really need to use a Validation
rule.

Any help would be greatly appreciated.

Thanks,
Bob

 

[Ken Puls]

Personally, I would create a style that has the "Locked" flag selected
(as it is by default). Highlight the entire sheet and unlock all the
cells, then apply the style to your formula cells only. Protect the
sheet and you're done.

Is there any special reason why you're trying to protect your formula
via a validation rule and not the built in protection methods?

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

 

[Bob]

Ken,
The reason I am not using Excel's protection capabilities is because the
workbook is "shared" and uses macros. Once shared, you cannot
protect/unprotect a workbook. Hence, the reason I need to use Validation
rules.
Bob

 

[Ken Puls]

To my knowledge it is not possible to use a UDF as a Data Validation
criteria.

Based on that, the options that I can see are to either unshare the
workbook, which you need to do to apply the validation rules anyway, and
apply the method I outlined before.

Honestly, I hope someone comes along and proves me wrong here, or
provides an alternate route.

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

 

[Dave Peterson]

I'd use a UDF like this:

Option Explicit
Function IsFormula(rng As Range) As Boolean
IsFormula = CBool(rng.Cells(1).HasFormula)
End Function


Then I could put a formula in another cell (say B1)
=isformula(a1)
(and hide that column???)

And use that cell for data|validation
Custom
=B1=TRUE

But be aware that
=1234
is a formula.

 

[Bob]

Dave,
Interesting solution. Given that my workbook contains over 130 columns, and
over 550 rows of data, I'm reluctant to add the 6 additional hidden columns
needed to do the job. The file size is huge as it is!
Thanks all the same. I will definitely keep your solution in mind for
future workbooks.
Bob

 

[Bob]

Ken,
Thanks for the info. I was really hoping that I could use a UDF as a Data
Validation criteria. Thanks again for all your help.
Bob

 

[Ken Puls]

Happy to, Bob, only I wish I could have given better news.

I agree that it would be a very nice feature to have. I haven't tested
to see if you can do that in Excel 2007 (i doubt it though), but maybe
MS will develop it in the next release after? Late would be better than
never, after all.

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

 

[Biff]

Try this:

I don't know if this will work on a shared file but it works on non-shared
files.

Create this named formula:

Name: IsFormula
Refers to:

=GET.CELL(48,INDIRECT("RC",FALSE))

Set the validation:
Select the cells you want to validate.
Allow: Custom
Formula: =IsFormula
Uncheck: Ignore blank

I can't figure out why, but it doesn't work unless Ignore blank is
unchecked.

Biff