Timer advance cell by cell

[smcelv]

Hi guys
Need advice on how to set up a function to move and colour cells after 30 minute intervals - for each working day of the week.

 

[sijpie]

I am assuming you mean that during working hours on work days you want to update a spreadsheet every 30 minutes changing some formatting.

where is the spreadsheet held, on th esame PC that runs the macro?

VBA cannot run assynchroneously, ie if a macro runs, then no other excel operation can be done. This includes timer events or wait events.

So there are a few options:
1)The spreadsheet is held on a file server, and a PC runs the excel macro which opens the file on the server every half hour and updates it as required, then closes the file again for other users (or processes) to use.
Disadvantage - if someone else has the file open at that point the update won't happen. And no other excel activity can be run on this PC

2) you create a spreadsheet with the macro to update the main sheet. Then you create a task under Control Panel / Services / Task Scheduler with the name of your spreadsheet (with path) to run every half hour.
The macro should update the formatting as required and then close itself.

 

[smcelv]

Hi sijpie
Thank you for your reply.
Yes your right - would be during working hours AND on the one PC.
Had this suggestion but I'm afraid I get a bit lost with the location I'm to direct the program to.

Do you think it would be practical?

In the ThisWorkbook Module put . . . . . . . .

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Run "CancelOnTime" On Error Goto 0
End Sub

Private Sub Workbook_Open()
Run "ColorCell"
End Sub

In a normal Module put; . . . . . .

Public dTime As Date
Public lRow As Long

Sub ColorCell()
Dim rCell As Range 'Named range you create and link to a Checkbox
If Range("CheckBoxLInk") = True Then
dTime = Time + TimeValue("00:30:00")
Application.OnTime dTime, "ColorCell"
lRow = lRow + 1
'Change "1" and "A" to suit
Sheets(1).Cells(lRow, "A").Interior.Color = 65535
Else
Run "CancelOnTime"
End If

End Sub


Sub CancelOnTime()
Application.OnTime dTime, "ColorCell", , False
lRow = 0
dTime = 0
End Sub

 

[sijpie]

Well your code seems o be constructed correctly for the use of OnTime.
So it should work. What is the area you have problems with?

 

[smcelv]

With the "Named Range and link to Checkbox"
Would the "Named Range" be one of the rows i.e. D5:BW5 that I need the process to occur?
ALso "link to Checkbox" - is this the cell that would contain say, a radio button?

 

[sijpie]

Well the example was set about having a checkbox in the spreadsheet that the user can tick. if ticked then the process updates every 30min. If cleared then the process doesn't run.

When you create a checkbox in Excel (from the Forms menu) then right click on the checkbox and select 'Format Control...' you can set which cell is to be linked to the checkbox.
This is the cell that is referred to here.

So say you link the checkbox to cell A10. Then when you tich the checkbox cell A10 will get the value 1, if yu clear the box, A10 will be 0.

You can give A10 a name (Insert Name), say you call it CheckBoxLink.

Now in the macro (Range("CheckboxLink") ) it refers to this cell (using a named range is better than refering to Range("A10") in the macro in case someone inserts a row or a column. Then the macro wouldn't work properly anymore)

Another point that is important is that you need to link the checkbox to the ColorCell macro. Otherwise the process wouldn't start until you close and reopen the spreadsheet. Rightclick the checkbox and select 'Assign Macro'.

 

[smcelv]

Thank you so much for your help.
Will try this out and let you know.

Again - much appreciated

Sam

 

[smcelv]

Hi sijpie
Sorry to hound you but I know i'm close to getting this working if you'll just indulge me a little bit longer.
I've uploaded a screen shot of the module I'm trying to activate. Can you please tell me where I'm going wrong?

 

[sijpie]

Well first of all you have an error in the code:

Sub ColorCell() 
Dim rCell As Range 
If (E5("CheckBoxLink")) = True Then 
...

should read:

Sub ColorCell() 
Dim rCell As Range 
If Range("CheckBoxLink") = True Then 
...

Then my question to you is: To what cell is the checkbox linked (ie what cell will get a value 1 if the checkbox is checked or 0 when not). You can set this up in the property of the checkbox (right click on it)
The code above assumes that you have given this cell the name CheckboxLink

 

[smcelv]

Hi sijpie
The cell linked to the Checkbox is D5. I have now named that cell as Checkboxlink.

I'm now getting this error
http://homepage.ntlworld.com/sammcelveen/Error.JPG