Workbook autocalculation except in one column

[Patrao]

Dear experts
After a few hours of trying different things and not getting anywhere I’ve decided to post something.
So basically I have a spreadsheet that will be used as a logging sheet of received goods. The spread sheet is very basic and it has only a few columns that have to be field in sequential order.
However, one of my columns (G) has a formula that uses, “randbetween” this is used to decide if an item has to be audit or not.
So what I would like is to make sure that automatic calculation is disabled in column G until I a point where I decide to calculate and immediately after, disable it again. What I try to achieve is that people don’t cherry pick what needs to be audit by click F9 until it suits them.

So something like:

Private Sub Workbook_Open()
Range("MyRange").Calculate = False
End Sub

And then create a small macro that at the very end will do something like:

Range("MyRange").Calculate
Range("MyRange").Calculate = False

Unfortunately this does not work. Those anyone has an idea how to make it work.
Many thanks
Patrao

 

[MarcelBeug]

My suggestion would be a workaround: just have your numbers in column G and have them recalculated by a macro, like:

'
' Macro1 Macro
      
    Range("G1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormulaR1C1 = "=RANDBETWEEN(0, 100)"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

 

[Patrao]

Hi Marcel,

You have just solved the problem. That’s some out of the box thinking!
I was so worried about the auto calculation that I’ve never thought in applying the formula from VBA.

Many thanks for your help
Cheers
Patrao