How do I protect only formulas in Excel?

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
0
6/17/2005 5:51:06 PM
excel.misc 78881 articles. 5 followers. Follow

17 Replies
739 Views

Similar Articles

[PageSpeed] 14

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
0
6/17/2005 6:07:03 PM
Hi

Unprotect the cells where you want to allow entries, then click on 
Tools|Protection|Protect sheet.  Type in a password and untick Select locked 
cells.

Click on OK, and when asked, re-enter the password

"RHmcse2003" wrote:

> 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
0
Kassie (262)
6/17/2005 6:23:04 PM
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
0
6/17/2005 8:09:09 PM
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
-- 
http://blog.jausovec.net


"RHmcse2003" je napisal:

> 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
0
6/19/2005 8:12:01 AM
.... 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  orevfyni.frire@mt.g-pbz.ue



Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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



0
bs (25)
6/19/2005 10:21:53 AM
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
-- 
http://blog.jausovec.net


"b&s" je napisal:

> .... 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  orevfyni.frire@mt.g-pbz.ue
> 
> 
> 
> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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
> 
> 
> 
> 
0
6/19/2005 11:11:02 AM
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  orevfyni.frire@mt.g-pbz.ue



Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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  orevfyni.frire@mt.g-pbz.ue
>>
>>
>>
>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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



0
bs (25)
6/19/2005 2:00:27 PM
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  orevfyni.frire@mt.g-pbz.ue
> 
> 
> 
> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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  orevfyni.frire@mt.g-pbz.ue
> >>
> >>
> >>
> >> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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
> 
> 
> 
> 
0
6/19/2005 2:29:02 PM
.... but we are speaking about:
 If Left(Target.Value,1) = "=" Then ...
and
 If Left(Target.Formula, 1) = "=" Then ...

maybe I'm wrong, but if I have the cell with formula: "=A1+A2" (A1 and A2
are empty)  then Left(Target.Value,1) gives as result: "0", until
Left(Target.Formula, 1) gives: "="

--
pozdrav!
Berislav

Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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.
>
>
>> 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  orevfyni.frire@mt.g-pbz.ue
>>
>>
>>
>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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  orevfyni.frire@mt.g-pbz.ue
>>>>
>>>>
>>>>
>>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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




0
bs (25)
6/19/2005 8:28:42 PM
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  orevfyni.frire@mt.g-pbz.ue
> >
> >
> >
> > Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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  orevfyni.frire@mt.g-pbz.ue
> > >>
> > >>
> > >>
> > >> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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
0
ec357201 (5290)
6/20/2005 3:26:03 AM
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 <ec35720@netscapeXSPAM.com> 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  orevfyni.frire@mt.g-pbz.ue
>>>
>>>
>>>
>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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  orevfyni.frire@mt.g-pbz.ue
>>>>>
>>>>>
>>>>>
>>>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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






0
bs (25)
6/20/2005 9:03:20 AM
Hi Berislav, 

Yes, that's true - but the question was how to protect empty cells and don't 
let users to enter a formula, so I was supposing that the cells are empty and 
there are no formulas in the cells. 

The solution Left(Value, 1) will fail if you try to check the cells that 
already contain formulae - but if you're checking the user input the solution 
will behave the same as Left(Formula,1). 

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


"b&s" je napisal:

> .... but we are speaking about:
>  If Left(Target.Value,1) = "=" Then ...
> and
>  If Left(Target.Formula, 1) = "=" Then ...
> 
> maybe I'm wrong, but if I have the cell with formula: "=A1+A2" (A1 and A2
> are empty)  then Left(Target.Value,1) gives as result: "0", until
> Left(Target.Formula, 1) gives: "="
> 
> --
> pozdrav!
> Berislav
> 
> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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.
> >
> >
> >> 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  orevfyni.frire@mt.g-pbz.ue
> >>
> >>
> >>
> >> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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  orevfyni.frire@mt.g-pbz.ue
> >>>>
> >>>>
> >>>>
> >>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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
> 
> 
> 
> 
> 
0
6/20/2005 9:40:02 AM
Hi Peter,
did you test your macro? I reacted because your macro does not work for me!


--
pozdrav!
Berislav

Peter Jausovec <PeterJausovec@discussions.microsoft.com> wrote:
> Hi Berislav,
>
> Yes, that's true - but the question was how to protect empty cells
> and don't let users to enter a formula, so I was supposing that the
> cells are empty and there are no formulas in the cells.
>
> The solution Left(Value, 1) will fail if you try to check the cells
> that already contain formulae - but if you're checking the user input
> the solution will behave the same as Left(Formula,1).
>
> Regards,
> Peter
>
>> .... but we are speaking about:
>>  If Left(Target.Value,1) = "=" Then ...
>> and
>>  If Left(Target.Formula, 1) = "=" Then ...
>>
>> maybe I'm wrong, but if I have the cell with formula: "=A1+A2" (A1
>> and A2 are empty)  then Left(Target.Value,1) gives as result: "0",
>> until Left(Target.Formula, 1) gives: "="
>>
>> --
>> pozdrav!
>> Berislav
>>
>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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.
>>>
>>>
>>>> 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  orevfyni.frire@mt.g-pbz.ue
>>>>
>>>>
>>>>
>>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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  orevfyni.frire@mt.g-pbz.ue
>>>>>>
>>>>>>
>>>>>>
>>>>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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




0
bs (25)
6/20/2005 10:58:17 AM
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 <ec35720@netscapeXSPAM.com> 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  orevfyni.frire@mt.g-pbz.ue
> >>>
> >>>
> >>>
> >>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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  orevfyni.frire@mt.g-pbz.ue
> >>>>>
> >>>>>
> >>>>>
> >>>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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
0
ec357201 (5290)
6/20/2005 1:35:20 PM
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 <ec35720@netscapeXSPAM.com> 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 <ec35720@netscapeXSPAM.com> 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  orevfyni.frire@mt.g-pbz.ue
>>>>>
>>>>>
>>>>>
>>>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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  orevfyni.frire@mt.g-pbz.ue
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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



0
bs (25)
6/20/2005 3:51:46 PM
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 <ec35720@netscapeXSPAM.com> 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 <ec35720@netscapeXSPAM.com> 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  orevfyni.frire@mt.g-pbz.ue
> >>>>>
> >>>>>
> >>>>>
> >>>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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  orevfyni.frire@mt.g-pbz.ue
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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
> 
> 
> 
> 
0
6/21/2005 4:29:16 PM
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 <ec35720@netscapeXSPAM.com> 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 <ec35720@netscapeXSPAM.com> 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  orevfyni.frire@mt.g-pbz.ue
> > >>>>>
> > >>>>>
> > >>>>>
> > >>>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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  orevfyni.frire@mt.g-pbz.ue
> > >>>>>>>
> > >>>>>>>
> > >>>>>>>
> > >>>>>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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
0
ec357201 (5290)
6/21/2005 5:11:55 PM
Reply:

Similar Artilces:

Global Footers in Excel
Does anyone know how to take an Excel workbook that has multiple worksheets and create a footer (say one that lists the page number, the tab name, and the file name), and make that footer a "global footer" such that I don't have to set the footer for some 10 to 20 worksheets when they're practically identical? Thanks. DTP David Right-click on one worksheet tab and "select all sheets". Set up your footer on the active sheet and these settings will be replicated on all sheets. DO NOT FORGET to right-click and "ungroup sheets" when done. What you ...

Strange Excel Problem.....
Hi there, We are facing one very strange excel problem in our organization.We have purchased new IBM thinkcenter 851 desktops for our Managers.We have installed Windows 2000 professional as operating system and Office 97 as office in that pcs.Now we are facing one very strange problem....Normally when you click on "New" shortcut( I mean right click anywhere and select "new" option),you will find "Microsoft Excel Worksheet" as shortcut listed in that option and when you select it new excel worksheet will be opened.But here,whenever i click on "new"...

Excel remembers! How?
I type in "FRENCH"(no quatos) in cell A1 The I type in ENGLISH in cell b1 When I get back to cell a2 and type in the letter F the etxt is completed and it says FRENCH How does this happen/what is it called/how do you turn it OFF Thanks for the help Ian, It is called "AutoCompletion". To turn it off, go to the Tools menu, choose Options, then the Edit tab. There, uncheck the "Enable AutoComplete for cell values" options. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "ian&qu...

Excel Jululian 04-28-10
Good morning please help can i create an index sheet for all my excels files (XLS) regrads yes george, if thats what you want to do. RegMigrant "George A. Jululian" wrote: > Good morning > > please help > > can i create an index sheet for all my excels files (XLS) > > regrads oh, go on then two ways - build an excel macro what gets the information from windows so can (kind of) keep itself up to date. I am guessing you have no macro background so i offer the much cheaper way:- got to Start, run type "cmd" wi...

Cursor highlights the wrong rows in excel?
I find that I have to select 3 rows above the row I want to highlight in excel. This carries forward when selecting multiple rows. My mouse is always about 3 lines above the cells being acted upon. ...

excel 2007 installation
Hello: I used to have Excel XP and I install Excel 2007. Problem: I want to ger rid of the personalized features I have in XP and install Excell 2077 with the default parameters as it come from the manufacturer. Please help. Elitor Did you try just uninstalling the Excel XP version first? If so, did that not help? I've not tried this so I'd like to know! "Elitor" <Elitor@discussions.microsoft.com> wrote in message news:8C1B1E5E-0EAA-42EB-8CC2-5A228ADF8578@microsoft.com... > Hello: > I used to have Excel XP and I install Excel 2007. > Problem: I want to ...

Excel Caused Error in VBE6.dll. Excel will Close
When trying to open Excel 2000 (Office Professional) / Windows ME the above message appeared and Excel would not open. Renaming the VBE6.DLL file and then repair and then reinstal Office did not work as the original dated VBE6.DLL file reappeared Has anyone had the sme problem? If so did you find a workable solution? ... and what was it? ...

How do I copy a chart made in Excel to Powerpoint?
I have created a number of charts in Excel that I need to export to Powerpoint for a presentation. I also want to turn each graph into a jpeg or pdf file for publication. How do I do this? For sharing charts in Office 2003 or earlier: Using Excel with Other Office Applications http://peltiertech.com/Excel/XL_PPT.html Office 2007 throws some wrenches into the gears. To export charts as image files: Enhanced Export Chart Procedurehttp://peltiertech.com/WordPress/2008/06/09/enhanced-export-chart-procedure/ Use PDF or GIF, which are optimized for line art, not JPG, which is optimized fo...

Excel 2007 Chart SeriesCollection(1).Interior.ColorIndex
I thought I had posted this already, but can't find it. Sorry if it's a duplicate. I have the following code that was written in Excel 2003. Every time I run it in Excel 2007 it crashes. I can't find anything about changing the colors of a series in an Excel 2007 chart. Also, when I try to record a macro to change the chart colors to gain insight, nothing gets written to the macro regarding the color change. Help! Sheets("Chart1").SeriesCollection(1).Interior.ColorIndex = 5 ...

excel spreadsheet 2003 wont show content when moved to another par
i have backed all documents which included excel spreadsheets from c drive to d drive (ANOTHER PARTITION). Now i cannot see the content of the spreadsheet when i access it from the D drive or C drive. what have i done to it? It also wont open when itry to access it from backed up USB stick. Thanks Chris I had a similar issue with some files. What happened was that somehow the windows for the files got minimized or dragged out of view somehow (not sure how this happened). Users would open a file and see nothing. This fixed it for me. Goto the Window item on toolbar, S...

How can I draw a Parabola in Excel? like y = x^2 + 3
In a1:a10 enter numbers such as 1,2,3.... In b1 type =a1^2+3 copy this down to B10 select a1:b10; click chart wizard; select XY chart done now play with values in A to get what you need best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Max" <Max@discussions.microsoft.com> wrote in message news:F72AB641-F882-4DAD-B0C3-322297A124FC@microsoft.com... > ...

An issue regarding the formula
Hi, It's OK to copy the formula from one cell to another one within the same sheet. How about that to copy the formula from one Excel file to another one? It seems only the value of that cell will be copied to that file but not the formula inside it. I even have tried to copy the corr. formula within that again but just* the copied formula will not work successfully in there. * Thks & Rgds, HuaMin -- HuaMin ------------------------------------------------------------------------ HuaMin's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29423 View this t...

saving excel file with it's add in
Hi, I have an Excel file that uses a formula from an add in installed in m computer (*.xla). How can I save the project including the add in in order to use it i another computer that doesn't have the add in. thank -- Message posted from http://www.ExcelForum.com Do you really mean that your workbook uses a function that's in the addin? If yes, I think you have two choices. #1. Take the addin with you (and install it on the other pc). #2. Extract the code/procedure for that function from the addin and put it in your workbook. (If the addin's project is protected, then thi...

Pie Chart (excel 2003)
How do I hide the 0% of the pie chart so that they only show the relevant %? If you mean on the pie then double click the data labels and choose the Number tab, choose Custom and enter the following code on the Type line on the right: 0%;[=0]""; -- Cheers, Shane Devenshire "Xueling" wrote: > How do I hide the 0% of the pie chart so that they only show the relevant %? Thanks! It does look better. But is there a way to not show the data lable of those 0% as well? "ShaneDevenshire" wrote: > If you mean on the pie then double click the data labels ...

How can a set an alarm in Excel?
I don't know if this is possible, but I figured I would ask: I have a spreadsheet that I work with that shows the schedules of my students; each different place my student goes has its own row, so a student may have many rows if they have many activities. currently, i auto filter the day column to the current day so i can see what students have activities on that day, and when/where they are. i was wondering if i could set an alarm on the column labeled 'start time' that would pop a window up when any value in that cell matched the system time... is this possible?? thanks, -...

Excel 2007 sorting problem
In Excel 2007, every time I change ranges, and even sometimes when I move away from a range and then come back to an old range, I have to re-enter the sort parameters every time. In Excel 2003, and for some versions before, if I sorted a selected range (say A4:Q54) by Col A Asc, Col G Asc, and Col C Desc then Excel would remember the sort parameters so that if I changed ranges to say A60:Q100 it would keep those same parameters loaded and I could just simply sort my data without having to re-enter the sort parameters all over again. Please direct me to where I can get some help on this issue...

Graphic features of EXCEL..........
Hi, I want to display the table data on Pivot GRAPH from two different sheets. How should we do that? Any advice will be great. Bill ...

Excel text to Word text
How can I copy Excel text, then paste it into MS Word without carrying over the grid lines? Any help would be appreciated. Thanks Ed English I don't believe you can copy and paste without the gridlines. However, in Word, select the data and Table>Convert>Table to Text. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Ed" <ee15803@att.net> wrote in message news:3f7e21e9.53324199@netnews.worldnet.att.net... > How can I copy Excel text, then paste it into MS Word without carrying > over the grid lines? Any help would be appreciated. Thanks &g...

Extra prompts from Excel 2007 after saving a CSV file
Here's the scenario: 1. I open a .CSV file in Excel 2007. 2. I make some data change to the file. 3. I save the file. Excel prompts me to use the CSV format again. This is a little annoying, but I answer Yes, I want to keep this format. 4. I try to close Excel. 5. Excel prompts me with "Do you want to save the changes you made to '(filename)?' Yes/No/Cancel. This is INFURIATING. I just saved the darn file. So I click No, thereby training myself to click no when prompted by Excel to save changes, guaranteeing I'll lose work in the future. Does anyone know a way to get E...

Excel Formula Guidance. Formula need to determine if cell is popul
Hi, Hopefully someone will be able to provide some guidance. I have been tasked with setting up some spreadsheets at work to application coming into my department and the number of days it takes for us to deal with them. To work this out is easy enough using formula =NETWORKDAYS(B4,E4,$A$119:$A$158) with the numbers involved with the $ referencing bank holidays listed in the spreadsheet and B4 received date and E4 being the completed date. I now need to include some extra lines as I need to be able to monitor written enquiries as well so the headings would read Application ...

Re: Excel Problem
This is a multi-part message in MIME format. ------=_NextPart_000_0046_01C5D5BA.E1DDCCD0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have some sheets 1;2;3;4; ETC................. I would like the new page list release with one botton: A1-Nome de sheet(1;2;3;4; ETC) A2-Date(C50:C80) A3-Import (D50:D90) Best regards Marcello "Bob Phillips" <bob.phillips@notheretiscali.co.uk> ha scritto nel = messaggio=20 news:uk86X7v0FHA.3188@TK2MSFTNGP14.phx.gbl... > Marcello, > > I am not absolutely clear as to what you...

Excel Calculations Open in Manual Occasionally
MSOffice 2003, WinXP. When I open Excel, the Calculation Option defaults to Manual instead of remaining on Automatic. Is this a bug in Excel? I never set it to Manual. Thoughts can be posted or sent rich32822(Remove this)@hotmail.com. Regards, If the first workbook you open is set to manual it will change the setting for other workbooks as well meaning that if someone is sending you a workbook with this set, you open it by opening the attachment and Excel opens. Then you close this particular workbook and you open a new workbook, this workbook will now be set to manual -- Regards,...

Excel Table to Individual product list and spend
Hi, Basically I have an Excel data table and I have a Product name drop down list. I want to create a formula or macro so when a Product is selected, it would output the list of companies and their spend with the product. Below is a sample but the real spreadsheet has thousands of vendor names and spend for each product and there are probably ten product names altogether. Company Product1 Product2 Product3 Product4 Total ABC 7,009 8,000 15,009 Key 805,036 805,036 PC 56,016 57,470 568 114,054 Water 3,090 651 298,250 301,991 Total 66,115 863,157 306,818 -...

Connecting two series in a scatter chart
Hello - I have a user who created a scatter chart. The chart contains two series: "HO" and "PD" - the HO data point has a related PD data point. She would like to have Excel add a connector line from the HO data point to the corresponding PD data point. Is this possible? I apologize that I am not familiar with this type of chart, or the lingo, so please let me know what other information you would need to answer this question. We are using Excel 2007. Thanks very much! Hi, Double-click one of the series, in the format series dialog box choose the Options tab, and ch...

Wildcards with Array Formulae
Hi, I'm looking for some help with a formula I'm using to pull statistic out of a client spreadsheet. The formula uses the SUMIF command to loo at one column, check for a text value then look at another colum within the same row and check for another text value. It returns value of one if both are true and a value of 0 if one or none are tru then keeps a total. It's a pretty standard array formula, but I need i to use wildcards because the second column can contain value A, value or value AB. I want the formula to pick up all instances where value appears, including where it a...