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]

Thanks for stepping in with a clear explanation, Peter.

 

[Peter_SSs]

Wrong

 

[Peter_SSs]

Sorry, I mis-read an earlier part of the thread and posted incorrectly. I'll try again now.

Thanks for stepping in with a clear explanation, Peter.

Cheers

For the A24 part of the code, I think the OP needs this slight variation to your suggestion.

I also don't think J5 needs to be in the precedent list since I believe it also contains a formula.

If Not Intersect(Target, Range("J4, D22, G22, D14")) Is Nothing Then
    ActiveSheet.Unprotect
    With Range("A24")
        .Offset(0, 3).Locked = .Value <> "Y"
    End With
    ActiveSheet.Protect
End If

 

[manmah]

Thanks both of you.. Just got home from work but will give that a whirl in the morning...
Cheers
Mark

 

[manmah]

Didn't work but have done it using :

Private Sub Worksheet_Calculate()
Me.Unprotect
Range("D24").Locked = Range("A24").Value <> "Y"
Me.Protect
End Sub

Thanks guys
Mark

 

[Peter_SSs]

Didn't work but have done it using :

Private Sub Worksheet_Calculate()
Me.Unprotect
Range("D24").Locked = Range("A24").Value <> "Y"
Me.Protect
End Sub

Thanks guys
Mark

Glad you have something you are happy with. Using the Worksheet_Calculate event means that the code may be running considerably more than is necessary but that may not be a problem if you don't have much going on in your sheet.

As a matter of interest, do any of the cells J4, D22, G22, D14 contain formulas?

 

[manmah]

J4 contains a vlookup.

You say that this may not cause a problem if there is not much going on on the sheet, What sort of problem could this cause as the book is rather large with multiple modules running to send the information to multiple sheets which then works out costs for different scenarios.

Thanks

 

[Peter_SSs]

J4 contains a vlookup.

So, presumably the Worksheet_Change code that was suggested was not working because some cell(s) that affected the result in J4 that flowed through to affect A24 were changed. To use the Worksheet_Change event you would have to track back to all the cells that are changed manually that could flow through to affect A24. That list of cells would be the ones that would need to be listed in the code I suggested.

You say that this may not cause a problem if there is not much going on on the sheet, What sort of problem could this cause as the book is rather large with multiple modules running to send the information to multiple sheets which then works out costs for different scenarios.

To be honest, your Worksheet_Calculate code is short so it probably won't cause a problem at all. The issue is that it will run every time the sheet recalculates, which may be very often, even if the recalculation has no effect on cell A24. If you have been using the sheet with the Worksheet_Calculate code and haven't noticed any performance issues, then you haven't got a problem.

 

[manmah]

Hi Peter
Thanks for all the help, i've just come up against one more (hopefully the last) small issue... When I have filled in my data sheet i send it to other sheets using the following code:

Sub CONTROL5()
Dim historyWks As Worksheet
    Dim inputWks As Worksheet
    Dim nextRow As Long
    Dim oCol As Long
    Dim myRng As Range
    Dim myCopy As String
    Dim myCell As Range
    
    'cells to copy from Input sheet - some contain formulas
    myCopy = "D8,F8,D10,D12,D14,G14,D16,G16,D18,G18,D20,D22,G22,D24,G24,D26,D28,M30,D34,D36,G36"
    Set inputWks = Worksheets("DATA SHEET")
    Set historyWks = Worksheets("DMD SHEET")
    With historyWks
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    End With
    With inputWks
        Set myRng = .Range(myCopy)
    End With
    
    With historyWks
        With .Cells(nextRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm"
        End With
        .Cells(nextRow, "B").Value = Application.UserName
        oCol = 3
        For Each myCell In myRng.Cells
            historyWks.Cells(nextRow, oCol).Value = myCell.Value
            oCol = oCol + 1
       Next myCell
    End With
    Dim c As Range
    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
For Each c In Sheets("DATA SHEET").UsedRange
If c.Locked = False Then
c.Value = ""
End If
Next
              Application.GoTo .Cells(1) ', Scroll:=True
      On Error GoTo 0
    End With
End Sub

The problem I now have is that all the cells I have been automatically locked and unlocked and protected etc whilst entering the data, obviously lock when the above macro clears the contents so it does not clear the locked cells..I need these to unlock and clear somehow so the data sheet is empty again ready for next batch of info... Any ideas??? Thanks

 

[Peter_SSs]

The problem I now have is that all the cells I have been automatically locked and unlocked and protected etc whilst entering the data, obviously lock when the above macro clears the contents so it does not clear the locked cells..I need these to unlock and clear somehow so the data sheet is empty again ready for next batch of info... Any ideas??? Thanks

I don't exactly follow all that but I think what you need is something like this.

'Stop 'events' (eg Worksheet_Calculate) from being triggered
Application.EnableEvents = False

'Code to unlock/clear cells or whatever you want goes here

'Re-enable 'events'
Application.EnableEvents = True