Excel 4.0 Macro replacement OR hide warning

joeycatanese

New Member
Joined
Jan 12, 2011
Messages
4
Hello All,

I have a workbook with several sheets that have a mixture of formulas (that pull information from another sheet) and manual overwrites. I want to differentiate the cells that have formulas in them and those that are overwritten or user entered values.

Currently I am using Conditional Formatting and a Excel 4.0 Macro as follows:

Insert > Name > Define:
'Name in workbook': CellHasFormula
"Refers to':
Code:
=GET.CELL(48,INDIRECT ("rc",FALSE))
Then in the conditional formatting dialog I simply set the formula to:
Code:
=CellHasFormula
and apply it to all the cells I need it to.

It is simple and works wonderfully, the only problem is I get a dialog upon opening that states:
"This workbook contains one or more Microsoft Excel 4.0 macros. These macros may contain viruses or other harmful code. Open this workbook only if you trust the source."

I would like to get around this warning by one of two ways:
1) Hide this warning. (NOTE: This workbook is shared many different people over different locations so changing security settings in each version of excel is not really an option)
2) Replace the Excel 4.0 macro

Any suggestions?
Thanks in advance!
jc
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You could use this UDF:

Code:
Function HasFormula(Cell As Range) As Boolean
    HasFormula = Cell.HasFormula
End Function
but your users would have to enable macros.
 

joeycatanese

New Member
Joined
Jan 12, 2011
Messages
4
Andrew,

Thanks for the quick reply. We already have the enable macros dialog box so that is not a problem, I just wanted to limit it down to one dialog box. But is there something I can put into the function to have it point to the current cell. For Example:

Code:
=HasFormula(Cell.Current)
as opposed to hard-coding:

Code:
=HasFormula(A1)
=HasFormula(A2)
I know I could to a Paste Special > Formatting which will carry the Conditional Formatting with it, but don't want to overwrite the current cell formatting (We have about 10 different background colors denoting different types are parts spread across several worksheets). I would like to avoid that if all possible.

Thanks
jc
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What's the problem with entering a cell reference as an argument? If it's relative you can copy it elsewhere.
 

joeycatanese

New Member
Joined
Jan 12, 2011
Messages
4
The problem with entering it as a cell reference is that I am retrofitting this to an existing worksheet with many different formats. I would like to select the entire range and apply a single Conditional Format as I did with the 4.0 Macro. I just do not know how to reference a cell to itself.

Does this make sense?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If you select eg A1:D100 and enter the formula:

=HasFormula(A1)

the cell reference will adjust relatively applied to the rest of the range.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,115
Messages
5,466,765
Members
406,497
Latest member
Bryanlim

This Week's Hot Topics

Top