|
|
|
|
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
|