That wasn't an error message. It was just displaying that just because a cell
starts with an equal sign, it doesn't always contain a formula.

If you want to check to see if a cell contains a formula, I think the best thing
to do is look at the .hasformula property.



RHmcse2003 wrote:

First off, I'd like to thank every one who has replied. I sincerely
appreciate the help.
I've now tried the formula on the sheet and I get an error stating
"False--=" I am definitely not up to par on my VB coding but I'm assuming
that this is the line

MsgBox .HasFormula & "--" & Left(.Formula, 1)

Seems like that would work though. Any suggestions?

Great Ideas everyone, thanks again!

"b&s" wrote:

Hi Dave,
I didn't comment your post and your recommendation ... who am I? :-)
I only tried to explain my answer to OP regarding his macro.

--
regards/pozdrav!
Berislav

Dave Peterson wrote:
But it was relevant to this portion of your post:

If Left(Target.Formula, 1) = "=" Then ...

This can give misleading results.


Just the relevant portion of my post.

Option Explicit
Sub testme()
With Range("A1")
.NumberFormat = "@" 'Text
.Formula = "=b1+b2"
MsgBox .HasFormula & "--" & Left(.Formula, 1)
End With
End Sub




b&s wrote:

Hi Dave,
thank You for Your commentary. My remark was that:
Left(Target.Value,1) is not relevant formula for equation: If
Left(Target.Value,1) = "=" Then ... (testing cells with formulas),
therefore my recommendation, for that case, was:
Left(Target.Formula, 1)

--
regards/pozdrav!
Berislav

Dave Peterson wrote:
I think for the majority of cases, it probably won't matter. But
personally, I like to user .formula when I'm plopping in the
formula. I'll use .value when I'm putting in a constant value.

But since some people actually use '=============== as a separator
(for visual effect), I find it much better to check .hasformula than
=left(.formula,1)

For instance:

Option Explicit
Sub testme()
With Range("A1")
.NumberFormat = "General"
.Formula = "=b1+b2"
MsgBox .HasFormula & "--" & Left(.Formula, 1)
.NumberFormat = "@" 'Text
.Formula = "=b1+b2"
MsgBox .HasFormula & "--" & Left(.Formula, 1)
End With
End Sub




Peter Jausovec wrote:

Well, if you use .Value to specify a formulae (e.g. Value =
"=A1+A2") and you check if formulae exist (Range(..).HasFormula)
you will get True.

Maybe an MVP will describe if there is a difference.

--
http://blog.jausovec.net

"b&s" je napisal:

Hi Peter,
Both of them have same result :)

I'm not 100% sure :-)

--
pozdrav!
Berislav

Always nice to hear if a suggestion works or not.
************************************************** *********
ROT13 - email address



Peter Jausovec wrote:
Hi Berislav,

Well, basically in this case it doesn't matter if you use .Value
or .Formula

Range("A1").Value = "=A3+A4"
Range("A2").Formula = "=A3+A4"

Both of them have same result :)

Regards,
Peter

.... Hi Peter, maybe this:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object,
ByVal Target As Range)
If Left(Target.Formula, 1) = "=" Then
' user is trying to enter a formula - do something"
End If
End Sub


--
pozdrav!
Berislav

Always nice to hear if a suggestion works or not.
************************************************** *********
ROT13 - email address



Peter Jausovec wrote:
Hi,

Use the SelectionChangeEvent:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object,
ByVal Target As Range)
If (Left(Target.Value,1) = "=") Then
' user is trying to enter a formula - do something
End If
End Sub

That sounds pretty logical Peter, any idea on the syntax for
that?

Thanks for the replies!

"Peter Jausovec" wrote:

Hi,

Just an idea:
try catching OnChange event and check if = is entered and
then popup a password dialog

Hope this helps.

Peter
--
http://blog.jausovec.net


"RHmcse2003" je napisal:

Hi, I am looking for a solution to be able to allow some
cells that are unlocked to have other users enter data into
them, however I don't wan't other users to be able to enter
formulas without a password. I have heard of this being
done before but I didn't know if it was an option I've
overlooked or some kind of advanced VB code.

Any help is greatly appreciated,

RH





--

Dave Peterson