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?

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" <(E-Mail Removed)> wrote in message
news:E011D5B3-A4A5-453B-B912-(E-Mail Removed)...
>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?

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" <(E-Mail Removed)> wrote in message
> news:E011D5B3-A4A5-453B-B912-(E-Mail Removed)...
> >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?
>
>

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" <(E-Mail Removed)> wrote in message
news:E011D5B3-A4A5-453B-B912-(E-Mail Removed)...
>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?