clock - display message every half hour

[CCkfm2000]

hi all...

i have a spreadsheet that's open all the time.

what i need to do is to disable the update button every half hour.

only for a 2 mintues.

i found this bit of code which i'm trying to modify.

Sub xauto_open()
If ThisWorkbook.ActiveSheet.Range("d1").Value = "x" Then Exit Sub
ThisWorkbook.ActiveSheet.Range("m3").Value = Format(Now, "hh:mm:ss")
Application.OnTime Now + TimeSerial(0, 0, 1), "xauto_open"
Application.Calculate
End Sub


please help

thanks

[JimmyTheHand]

:hi:

This subroutine below works for me. It goes to a standard code module. First run should be initiated e.g. by OnOpen event of the workbook. Reference to the button should be changed as needed. I used an embedded button in Sheet1. Also, I used 28 and 2 seconds, instead of 28 and 2 minutes, schedules, for testing purpose.

Public Sub Toggle_Enable_Button()
Dim sht As Worksheet
Set sht = Sheets(1)

If sht.OLEObjects("CommandButton1").Enabled Then
sht.OLEObjects("CommandButton1").Enabled = False
Application.OnTime Now + TimeValue("00:00:28"), "Toggle_Enable_Button"
Else
sht.OLEObjects("CommandButton1").Enabled = True
Application.OnTime Now + TimeValue("00:00:02"), "Toggle_Enable_Button"
End If
End Sub

HTH

Jimmy

EDIT:
I made a new discovery. I closed the workbook that I used to test the code above. When the scheduled time arrived, Excel attempted to open the workbook again. Conclusion: you should make sure there's no more active schedule by the time you close the workbook. The 4th parameter of OnTime method can be useful, see the VBA Help.

[Bob Phillips]

I made a new discovery. I closed the workbook that I used to test the code above. When the scheduled time arrived, Excel attempted to open the workbook again. Conclusion: you should make sure there's no more active schedule by the time you close the workbook. The 4th parameter of OnTime method can be useful, see the VBA Help.

To overcome this, you need to issue an Ontime command with exactly the same time as the last ontime was issued for, so you need to create a global variable and store the run time there, and issue an Ontime close in the workbook_beforeclose event with that time.

[CCkfm2000]

update!!!


found this bit of code on the net and modified it so far.


Sub auto_open()


If ThisWorkbook.ActiveSheet.Range("a1").Value = "x" Then Exit Sub
ThisWorkbook.ActiveSheet.Range("m3").Value = Format(Now, "dd/mm/yy hh:mm:ss")
Application.OnTime Now + TimeSerial(0, 0, 1), "auto_open"
tme = Now() ' get current time
currhr = Hour(tme) ' current hour
currmin = Minute(tme)
currsec = Second(tme)
If currmin = 30 Or currmin = 0 Then ' if it is 00 or 30 minutes
' Call myprocess ' deal with the data
End If

If currmin < 30 Then ' find next half hour
nextMin = "30"
nextHr = CStr(currhr)

Else
nextMin = "00"
nextHr = CStr(currhr + 1)
End If

Application.OnTime nextTime, "auto_open"
'End If
Application.Calculate
End Sub

[Bob Phillips]

What was worng with Jimmy's? Seems a darned sight clearer to me, not to mention it would probably work (which this one doesn't look as though it will).