Determining if Cell has Formula
 

I have a worksheet that contains formulas that are linked dynamically to an
outside source. I have created a Workbook_BeforeClose routine that asks the
user if formulas should be removed from the worksheet (to lock in the values
from outside source).

If the formulas have already been removed, I don't want to ask the user
about formulas that have already been removed.

How can I determine if a specific cell contains a formula or an actual value?

Determining if Cell has Formula
 

maybe one of these:

If ActiveCell.HasFormula = True Then
MsgBox ActiveCell.Address & " contains a formula"
End If


If Range("A1").HasFormula = True Then
MsgBox Range("A1").Address & " contains a formula"
End If

--

Gary
Excel 2003


"PosseJohn" wrote in message
...
I have a worksheet that contains formulas that are linked dynamically to an
outside source. I have created a Workbook_BeforeClose routine that asks
the
user if formulas should be removed from the worksheet (to lock in the
values
from outside source).

If the formulas have already been removed, I don't want to ask the user
about formulas that have already been removed.

How can I determine if a specific cell contains a formula or an actual
value?

Determining if Cell has Formula
 

Thank you Gary, couldn't recall the 'HasFormula' property. That was exactly
the answer I needed.

Have a great Turkey day!

"Gary Keramidas" wrote:

maybe one of these:

If ActiveCell.HasFormula = True Then
MsgBox ActiveCell.Address & " contains a formula"
End If


If Range("A1").HasFormula = True Then
MsgBox Range("A1").Address & " contains a formula"
End If

--

Gary
Excel 2003


"PosseJohn" wrote in message
...
I have a worksheet that contains formulas that are linked dynamically to an
outside source. I have created a Workbook_BeforeClose routine that asks
the
user if formulas should be removed from the worksheet (to lock in the
values
from outside source).

If the formulas have already been removed, I don't want to ask the user
about formulas that have already been removed.

How can I determine if a specific cell contains a formula or an actual
value?

Determining if Cell has Formula
 

Another way if you don't want to loop all cells looking for formulas

Sub test()
Dim rng As Range
Dim ws As Worksheet

Set ws = ActiveSheet
' or say
' Set ws = ActiveWorkbook.Worksheets("Sheet1")

On Error Resume Next
Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas, 23)
On Error GoTo 0

If Not rng Is Nothing Then
MsgBox rng.Count & " formula cells" & vbCr & _
"address of first formula " & rng(1).Address(0, 0), , _
rng.Parent.Name
End If

End Sub

Regards,
Peter T

"PosseJohn" wrote in message
...
I have a worksheet that contains formulas that are linked dynamically to an
outside source. I have created a Workbook_BeforeClose routine that asks
the
user if formulas should be removed from the worksheet (to lock in the
values
from outside source).

If the formulas have already been removed, I don't want to ask the user
about formulas that have already been removed.

How can I determine if a specific cell contains a formula or an actual
value?