Vba/formulas

[manmah]

Hello, This is really getting me down....
Is there any way at all for VBA to use the returned value in a formula and treat the actual formula as blank.
I want the VBA to look at a cell and when the cell contains "Y" do what the macro says, but all it sees is a formula???

Thanks

 

[shg]

Maybe you could post your code and explain in context?

 

[manmah]

Hi please see below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("D18")) Is Nothing) And Target.Cells.Count = 1 Then
ActiveSheet.Unprotect
If Target.Value = "Y" Then
Target.Offset(0, 3).Locked = False
Else
Target.Offset(0, 3).Locked = True
End If
ActiveSheet.Protect
End If

If Not (Intersect(Target, Range("D22")) Is Nothing) And Target.Cells.Count = 1 Then
ActiveSheet.Unprotect
If Target.Value = "Y" Then
Target.Offset(0, 3).Locked = False
Else
Target.Offset(0, 3).Locked = True
End If
ActiveSheet.Protect
End If

If Not (Intersect(Target, Range("A24")) Is Nothing) And Target.Cells.Count = 0 Then
ActiveSheet.Unprotect
If Target.Value = "Y" Then
Target.Offset(0, 3).Locked = False
Else
Target.Offset(0, 3).Locked = True
End If
ActiveSheet.Protect
End If


End Sub

The first two parts work fine because the cells they refer to are entered by the user, however, the third part does not work as it refers to a cell (A24) containing A formula because to unlock the cell (D24) it requires multiple arguments.
I hope you can help as its driving me mad..

Thanks

 

[shg]

This might work, if the precedents of A24 are on the same worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count = 1 Then
        If Not Intersect(Target, Union(Range("D18, D22"), _
                                       Range("A24").Precedents)) Is Nothing Then
            ActiveSheet.Unprotect
            Target.Offset(0, 3).Locked = Target.Value <> "Y"
            ActiveSheet.Protect
        End If
    End If
End Sub

 

[manmah]

No it didn't like that.. I tried putting .precedents into my code but still did not like it..
What I don't understand is that a conditional format will read the cell as Y but it seems like the VBA just sees the formula?????????

 

[shg]

1. If you select the sheet of interest, open the VBE Immediate window and enter

? Range("A24").Precedents.Address

... what does it return?

2. What's the formula in A24?

 

[manmah]

It returned the following:
$J$4:$J$5,$D$22,$G$22,$D$14

The Formula is:
=IF(J5<>"",IF(J5="DNATYY","Y",IF(J5="DNATYN","Y",IF(J5="SLSYN","Y",IF(J5="SLSYY","N")))),"")

 

[shg]

Then try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D14,D18,D22,G22,J4,J5")) Is Nothing Then
        ActiveSheet.Unprotect
        Target.Offset(0, 3).Locked = Target.Value <> "Y"
        ActiveSheet.Protect
    End If
End Sub

 

[manmah]

That has locked the cells which refer to a manual input but not the one which has a formula in. However that does work better for me for the other cells I wish to lock, but i'm still stumped with the formula thing because to lock that cell I need multiple arguments...Thanks for trying tho....

 

[Peter_SSs]

What I don't understand is that a conditional format will read the cell as Y but it seems like the VBA just sees the formula?????????

Just to clarify a little. It is not vba as such that 'sees' a formula or a value but the Worksheet_Change event. From the vba Help, with my emphasis

Worksheet.Change Event
Occurs when cells on the worksheet are changed by the user or by an external link..
.
.
Remarks

This event doesn't occur when cells change during a recalculation. Use the Calculate event to trap a sheet recalculation.

So, what shg is trying to do for you is to track back and find precedent cells that are changed manually and use those with the Worksheet_Change event to then check the value in the formula cell(s).