0

I've set up a VBA code that sets up a form for the user to fill out information. I've set up another VBA code that runs upon sheet calculation that runs some checks and corrections to any information that is out of line. As soon as the second code runs all sheet calculation stops and I can only edit the first unlocked cell, even when sheet protection is off and I have selected another cell. All typing goes to the first unlocked cell.

This script has worked when triggered by selection change or by running the script manually. I've searched the internet but all I find are list of settings (all of which I have checked).

Private Sub Worksheet_Calculate()
'Transfer
'

If Range("AAA1").Value = "Transfer" And Range("AAB1").Value = "Not Empty" Then
    If Range("D3").Value = "0" Or Range("D3").Value = "" Then
        Range("AAB1").Value = "Empty"
    End If
End If
If Range("AAA1").Value = "Transfer" And Range("AAB1").Value = "Empty" Then
    If Range("E3").Value = Empty Or Range("F3").Value = Empty Then
        Else
        Range("D3").Value = Range("AA3").Value
        Range("AAB1").Value = "Not Empty"
    End If
End If
If Range("D3").Value > Range("AA3").Value And Range("AAA1").Value = "Transfer" Then
    Range("D3").Value = Range("AA3").Value
End If

End Sub

The expected result is for cell D3's value to be automatically set to the max value (calculated in cell AA3 using the value in cell E3) when both cells E3 and F3 have been filled out and cell D3 is either empty or equal to zero. I use cell AAB1 as a "switch" so that the user can enter a custom amount without a hasle. Cell AAA1 is set by the first macro that sets up the form. It all works once. Then I encounter the problem described above.

Asxulxet
  • 1
  • 2
    If you have a script that runs at calculate, and a script that changes values, it might clash whey they both trigger at the same time. Try switching off auto-calculate for the duration of the macro and see if that performs any better. https://stackoverflow.com/questions/49743732/turn-on-off-automatic-calculation-in-only-a-few-columns-in-excel – Plutian Aug 28 '19 at 09:11
  • 1
    When using an event to trigger your code, it's usually a good idea to turn off event processing within your routine to avoid getting into a recursive loop. Application.EnableEvents=false at the beginning and =true at the end – Harassed Dad Aug 28 '19 at 09:55

1 Answers1

0

I followed the advice of Harassed Dad and put Application.EnableEvents = FALSE at the very beginning of the script and = TRUE at the very end. It did the trick. I didn't think that changing values would trigger calculation before the script had finished. Thanks guys.

Asxulxet
  • 1