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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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.
 
Upvote 0
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
 
Upvote 0
What's the problem with entering a cell reference as an argument? If it's relative you can copy it elsewhere.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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