Changing Data in a cell after a certain date

[Excellinginexcel]

Hi

I have a data set that is set out that each column is a day, reading from left to right.


I need to find a VBA whereby on automatically opening the spreadsheet it will look at my data set, and every column that has a date less than today will be 'frozen'.


The problem is that the data in these cells is driven by data in another sheet. As time passes this data will be changed but I want to freeze the historic data so you could look back. If the data is not 'frozen' then in a weeks time when the other sheet is changed, the old data will also change. As the cells are formula driven, the vba would need to copy the current cell contents and re-paste them back in as 'values' rather than 'formulas' if that makes sense??

 

[SirJB7]

Hi, Excellinginexcel!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


A few questions:

a) how many columns do you have in your worksheet?

b) what is the cycle period? weekly if 7 columns, monthly if 30, anually if 365, ...

c) how is that data driven into your worksheet? manually, by a macro from same workbook, inserted from another application procedure, ...

d) are there only values or formulas too?


Consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you.

Give a look at the second green sticky post at this forums main page for uploading guidelines.


Regards!

 

[Excellinginexcel]

SirJB7,


I have searched the forum however was finding it difficult as some previous codes would do aspects of what I wanted, but not the entire shebang!


To answer your questions, I have used all available columns up to IV! Column A contains field headings and cells B1,C1,D1 are merged containing the date. B2,C2,D2 are merged containing the day of the week that that date pertains to. After that, the cells then split again so B3 is "Early", C3 is "Late", D3 "Night Shift". There is then a list of employee names. I have included a sample spreadsheet.


http://speedy.sh/h5xH6/Rota-Sample.xls


My problem is that the cells in the Deployment tab are driven by the priorities defined in Priorities tab! But when the date ticks over to the next day I want to freeze the information from the day before. This way, when the priorities are changed, historic shifts remain visible. This way I can look back at Bob for example and see how often he is on the tills.


Does this make sense?


Regards in advance

 

[SirJB7]

Hi, Excellingexcel!


First of all, I've made changes in the cell formulas of first 3 rows of Scheduling and Deployment worksheets:

B1: starting date (no change in Deployment, reference to Deployment in Scheduling)

E1: =B1+1

B2: =B1 (format as Custom "dddd")

B3: =ELEGIR(RESIDUO(COLUMNA()-1;3)+1;"N";"E";"L") -----> in english: =CHOOSE(MOD(COLUMN()-1,3)+1,"N","E","L")


Copy E1 thru H1:IT1, copy B2 thru E2:IT2, copy B3 thru C3:IV3. That simplifies maintenance.


Then I defined 3 named ranges:

DateStartCell: =Deployment!$B$1

DateList: =Deployment!$B$1:$IV$1

TaskTable: =DESREF(Deployment!$B$6;0;0;(CONTARA(Deployment!$A:$A)-4)*2;CONTARA(Deployment!$4:$4)-1) -----> in english: =OFFSET(Deployment!$B$6,0,0,(COUNTA(Deployment!$A:$A)-4)*2,COUNTA(Deployment!$4:$4)-1)


Finally there is the procedure Sub ToFreezeOrNotToFreeze, here's the code:

-----

[pre]

Option Explicit

Sub ToFreezeOrNotToFreeze()
' constants
Const ksWS = "Deployment"
Const ksDate = "DateList"
Const ksTask = "TaskTable"
Const kiStep = 3
' declarations
Dim rngD As Range, rngT As Range, rngW As Range
Dim lPerson As Long, lDate As Long, dToday As Date, dDate As Date
' start
Set rngD = Worksheets(ksWS).Range(ksDate)
Set rngT = Worksheets(ksWS).Range(ksTask)
dToday = Int(Now())
' process
With rngD
' column
lDate = 1
Do
dDate = .Cells(1, lDate).Value
lDate = lDate + kiStep
Loop Until dDate >= dToday Or lDate > .Columns.Count
If lDate <= .Columns.Count Then lDate = lDate - kiStep
lDate = lDate - kiStep
Debug.Print lDate, dDate, .Cells(1, lDate).Value
' check
If .Cells(1, lDate).Value < dToday Then
' unprotect worksheet
Worksheets(ksWS).Unprotect
' freeze
Set rngW = Range(rngT.Columns(1), rngT.Columns(lDate + kiStep - 1))
Debug.Print lDate, rngW.Address, ,
rngW.Cells.Locked = True
' copy values
rngW.Copy
rngW.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
If lDate + kiStep < .Columns.Count Then
' unfreeze
Set rngW = Range(rngT.Columns(lDate + kiStep), rngT.Columns(.Columns.Count))
Debug.Print lDate, rngW.Address, ,
rngW.Cells.Locked = False
End If
Set rngW = Nothing
' protect worksheet
Worksheets(ksWS).Protect _
DrawingObjects:=False, Contents:=True, Scenarios:=False
End If
End With
' end
Range("A1").Select
Set rngT = Nothing
Set rngD = Nothing
End Sub

[/pre]
-----


Which is called at open time (ThisWorkbook, Sub Workbook_Open) or whenever cell B1 of worksheet Deployment is changed (Deployment, Sub Worksheet_Change).


Link to the file:

https://dl.dropbox.com/u/60558749/Changing%20Data%20in%20a%20cell%20after%20a%20certain%20date%20-%20Rota_Sample%20%28for%20Excellingexcel%20at%20chandoo.org%29.xls


Just advise if any issue.


Regards!

 

[Excellinginexcel]

Sir JB7


Many thanks for this. I really appreciate the time you have spent on this!


Just quickly, this does everything I have asked but it does 'freeze' the cells - i was hoping to be able to edit them in order to hyperlink a document to them etc.... any chance of an edit to freeze the cells but also hyperlink stuff in?


Regards

 

[Excellinginexcel]

Just to add to this - I suppose all I am asking for is for the sheet to finish unprotected rather than protected!

 

[SirJB7]

Hi, Excellingexcel!


Just remove the groups of statements:

a)

[pre]

' unprotect worksheet
Worksheets(ksWS).Unprotect
b)

' protect worksheet
Worksheets(ksWS).Protect _
DrawingObjects:=False, Contents:=True, Scenarios:=False

[/pre]
But that won't freeze previous dates cells, just make them independent of the changes in table used for searching.


Regards!