Controlling Calculation and Form Controls

[PaulFogel123]

Hello,
I have some code like the one in the attached sample file. A checkbox form control has a macro assigned that triggers certain actions whether the cell link is True or False. If True, there's no problem. But if False, I need to have the workbook go into manual calculation first, otherwise it will trigger a circular argument before the code can be executed. If doing it without code or checkbox, I would first choose manual calculation, copy and paste values, and then enable automatic calculation. With the checkbox, as soon as it's checked, I can't then turn on manual calculation. It's too late.
Any ideas?

 

[NARAYANK991]

Hi Paul ,

I am not sure I have understood the problem.

The CheckBox_Click procedure is executed when ever the check box is clicked.

When the procedure is entered , the link cell reflects the current status of the checkbox ; thus if C5 contains FALSE , it means the checkbox has been unchecked , and it was checked earlier. If C5 contains TRUE , it means the checkbox has been checked , and it was unchecked earlier.

Now , under what circumstances do you want your steps of changing to manual recalculation ,... to be done ?

And what is the problem if you do it once you enter the CheckBox_Click procedure and find out that the check box was checked earlier and has been unchecked now ? Why do you say it is too late ?

Narayan

 

[Marc L]

Hi !​

​

Your code revamped :​

Sub CheckBox1_Click()
    If [C5] Then
        [F2:F3].Value2 = [{5555;16665}]
        MsgBox "All done !"
    Else
        [F2:F3].ClearContents
        MsgBox "Actual data for this month must be loaded first."
    End If
End Sub

 

[PaulFogel123]

I use checkboxes to designate whether a month has been updated with an actual instead of it being a forecast. The formulas are different depending upon whether a month is actual or a forecast. When you click the checkbox, you get TRUE, and that triggers certain formulas written in certain places to be copied and pasted for those months. So far, so good. But if you change your mind, unchecking the box, it changes to FALSE. Those formulas that were triggered when the box was TRUE will now cause a circular calculation error. Formulas that were copied and pasted when the box was TRUE need to be overwritten with different formulas. That needs to be done before the box changes to FALSE, or before FALSE will trigger a circular calculation error.

 

[NARAYANK991]

I use checkboxes to designate whether a month has been updated with an actual instead of it being a forecast. The formulas are different depending upon whether a month is actual or a forecast. When you click the checkbox, you get TRUE, and that triggers certain formulas written in certain places to be copied and pasted for those months. So far, so good. But if you change your mind, unchecking the box, it changes to FALSE. Those formulas that were triggered when the box was TRUE will now cause a circular calculation error. Formulas that were copied and pasted when the box was TRUE need to be overwritten with different formulas. That needs to be done before the box changes to FALSE, or before FALSE will trigger a circular calculation error.

Hi ,

I still find it difficult to visualize your problem.

Assuming that a mistake in checking the box and it was unchecked immediately thereafter.

You say that the formulas which were triggered when the checkbox was first checked , will now cause a circular reference error.

How is this possible ? Isn't the checkbox there to be checked and unchecked , at various times ? The user can make mistakes , but how can the software determine whether a checking of the checkbox was a mistake or whether the unchecking was a mistake ?

Is it possible that you can upload your workbook with only the code in it ? You can remove all of the data.

If we can run the code , then we will have a better idea of what changes need to be made so that this problem is resolved.

Narayan

 

[PaulFogel123]

I have to allow for the user to have made a mistake, uncheck the box, and have it return to where he was. Checking the box yields TRUE, which tells the model this is an actual, not a forecast, month. Code then copies a formula that uses actuals for that month, for example, the percentage of patients actually reported that month, into what would otherwise be the percentage of patients that you would like to forecast for that month. Now, if you uncheck the box, that tells the model that this is a forecast month, not an actual month. The forecast formula then multiplies the percentage of patients reported that month to calculate the forecast. That causes a circular error.
The full file is 20mb and everything is interconnected so I have to make do with a sample.

 

[PaulFogel123]

Hi !​

​

Your code revamped :​

Sub CheckBox1_Click()
    If [C5] Then
        [F2:F3].Value2 = [{5555;16665}]
        MsgBox "All done !"
    Else
        [F2:F3].ClearContents
        MsgBox "Actual data for this month must be loaded first."
    End If
End Sub

Thanks! I will try adapting this and let you know. Paul

 

[NARAYANK991]

Hi ,

If Marc's code resolves your problem , that is great , otherwise I suggest you explain this in more detail :

Code then copies a formula that uses actuals for that month, for example, the percentage of patients actually reported that month, into what would otherwise be the percentage of patients that you would like to forecast for that month.

First , the actuals data and the forecast data should be in two separate , different places.

Second , I can understand that the actuals this month can influence the forecast for next month. I cannot understand how the actuals this month can influence the forecasts for either this month or any previous month. How exactly this can generate a circular reference error , I am not able to visualize. Probably someone who has worked extensively with models can make out what is happening.

Third , I cannot understand how a forecast for any month can influence the actuals for any month.

Thus , if your data is being entered in the right places , whether the checkbox is checked or unchecked by mistake or otherwise , calculations can operate on the wrong data ; thus when the actual data for this month needs to be used to generate a forecast for the next month , the forecast for this month will be used to generate the forecast for the next month. However , why should anything be undone ? Surely , if the calculation is rerun with the right initial conditions , the correct results should become available ?

Anyway , let us hope Marc has resolved your problem.

Narayan

 

[PaulFogel123]

Hi ,

If Marc's code resolves your problem , that is great , otherwise I suggest you explain this in more detail :



First , the actuals data and the forecast data should be in two separate , different places.

Second , I can understand that the actuals this month can influence the forecast for next month. I cannot understand how the actuals this month can influence the forecasts for either this month or any previous month. How exactly this can generate a circular reference error , I am not able to visualize. Probably someone who has worked extensively with models can make out what is happening.

Third , I cannot understand how a forecast for any month can influence the actuals for any month.

Thus , if your data is being entered in the right places , whether the checkbox is checked or unchecked by mistake or otherwise , calculations can operate on the wrong data ; thus when the actual data for this month needs to be used to generate a forecast for the next month , the forecast for this month will be used to generate the forecast for the next month. However , why should anything be undone ? Surely , if the calculation is rerun with the right initial conditions , the correct results should become available ?

Anyway , let us hope Marc has resolved your problem.

Narayan

My model has a tab in it for forecast assumptions. These assumptions change the calculations for months. So, if I input a 5% increase for October, say, the calculation for October is September's volume plus 5%. When October actuals are uploaded, that 5% assumption won't have any effect on October; it's no longer a forecast month. But the assumptions cell for October still says 5%. I want that cell to display the actual increase now that we have actuals. Let's say it's 3% (October minus Sept, divided by Sept). I created some code to copy this formula into the cell upon October actuals updating. I want to see the formula, not just the value, so I can see how it's derived. This works fine. But suppose I notice a problem with October actuals? Let's say something has been misreported. I decide to uncheck the box, which will designate October as a forecast month, not an actual month, just like it was before. But the assumptions cell still has the formula in it, (October minus Sept, divided by Sept). This triggers a circular reference. If I did this manually, without code, I'd put the workbook in manual calculation, copy a value like 3% into the cell, and then revert to automatic calculation.

My question was asking if a checkbox form control could be made to trigger manual calculation first and then change the linked cell to TRUE or FALSE. I think the answer is no. I could instead use data validation, and by selecting True or False, I could have code execute accordingly. Or I could change the assumption formula to include an IF; if this is an actual month (TRUE), then October minus Sept, divided by Sept; if not (FALSE), then 5%. I decided to do the latter. I reworked my assumptions formulas so that a circular reference wouldn't be triggered. It was easy but time-consuming.

Thanks for your suggestions.