CODEWITHSUNDEEP
vbaexceltime
Vbasic4now
Vbasic4now

Reputation: 589

excel stopping a cell form updating based on the time

I have a table that calculates the expected number of pieces to be made for each hour based on a hand full of criteria. as time passes a user is entering information into the actual pieces column. when the criteria (located elsewhere on the sheet) is changed the entire expected pieces column updates. the table look as follows:

enter image description here

I am trying to make it so that if the current time is after the time in the first column the expected pieces column will not update and will keep the previous value. for example if at 8:30 the expected pieces was 20, after it becomes 9:00 the 8:00 and 7:00 row will hold their expected pieces value while the rest of the column updates as the criteria changes.

I can not for the life of me figure out how to accomplish this. is it possible to do on the front end or should I write some VBA code to control this?

How should I approach this problem? thank you!

Upvotes: 1

Views: 124

Answers (2)

Ulli Schmid
Ulli Schmid

Reputation: 1167

This has to be coded in VBA.

As the values in your "drop down cells" will change, it is not possible for simple Excel formulas to store or access their previous states.

One could, howerver, imagine some quite ugly workaround with a different set of those "drop down cells" for every time slot.

Some VBA suggestions:

1) Disable Auto-Calculation

Private Sub Workbook_Open()
  Application.Calculation = xlCalculateManual
End Sub

Then only re-calculate those cells that fulfill your criteria:

Sub foo()
  If myCriterion Then
    Range("B3:D7").Calculate
  End If
End Sub

2) If your criterion is met, replace the formula with its current result

Private Sub Worksheet_Change(ByVal Target as Range) 
  If myCriterion Then
    Range("B3").Value = Range("B3").Value
  End If        
End Sub

Note that if you want to use the same sheet on day2, you will have to restore the previous "formula" version of those cells.

Please keep in mind user interaction. You should maybe block some cells from user input to prevent undesired effects.

Upvotes: 1

Preston
Preston

Reputation: 8177

The problem with the following method is that the book has to be open; but you could use the application.OnTime method to cut and paste the values based on the time (copy paste values for one cell at a time on the hour every hour). You'd have to have a formula in the cells to calculate them. Does it sound like im barking up the right tree with this or am i missing the point?

Upvotes: 0

Related Questions