ScorchedLemonade
Technical User
Hi,
I've got what I presume to be a fairly straightforward problem but none of my solutions has worked so far: here are a couple of attempts by way of example.
Private Sub Workbook_Open()
With Application ' sometimes Windows crashes and the workbook gets set to calculate manually, so this is to correct that and that works OK
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
'the next line is basically what I'm gunning for
Application.OnTime TimeValue("11:40:00"), Autoclose, TimeValue("12:15:00")
' Application.OnTime TimeValue("20:00:00"), Autoclose, TimeValue("23:00:00")
End Sub
(The uncommented line was just to test things - more generally, I just want to make sure that the file can't be left open overnight.)
I also tried something along the lines of this:
in cell D1 I put =present() where I'd defined
Function present() as double
Application.Volatile
present=Now
End Function
And then I'd used something of this type
Private Sub Worksheet_Change(By Val Target as Range)
If Not Intersect(Cells("D1"),Target) is nothing then
If Cells("D1").Value>7/24 Then 'again just a testing value
Cells(2,2).Value=1
End If
If Cells("D1").Value>19/24 Then
Autoclose
End If
End If
End Sub
where Autoclose is just a sub to close the book.
Since present() was volatile, it did indeed regularly change, but this didn't fire my workbook change macro (and produce the test value of 1) - I had to explicitly reenter =present() into D1 to get 1 to appear in B2, so it did work, but not in the way I expected (I tried making the sub volatile too, although maybe that only works for functions) but to no avail. Again, if I deleted the 1, it would not reappear, so I'm not getting it to fire properly.
Am I missing something easy - I'm just wanting to ensure that if the file is open somewhere on our network at night then it will be closed before the morning (when it opens and runs macros on my machine) so that the revised sheets can be saved before I get to work in the morning.
Many thanks.
I've got what I presume to be a fairly straightforward problem but none of my solutions has worked so far: here are a couple of attempts by way of example.
Private Sub Workbook_Open()
With Application ' sometimes Windows crashes and the workbook gets set to calculate manually, so this is to correct that and that works OK
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
'the next line is basically what I'm gunning for
Application.OnTime TimeValue("11:40:00"), Autoclose, TimeValue("12:15:00")
' Application.OnTime TimeValue("20:00:00"), Autoclose, TimeValue("23:00:00")
End Sub
(The uncommented line was just to test things - more generally, I just want to make sure that the file can't be left open overnight.)
I also tried something along the lines of this:
in cell D1 I put =present() where I'd defined
Function present() as double
Application.Volatile
present=Now
End Function
And then I'd used something of this type
Private Sub Worksheet_Change(By Val Target as Range)
If Not Intersect(Cells("D1"),Target) is nothing then
If Cells("D1").Value>7/24 Then 'again just a testing value
Cells(2,2).Value=1
End If
If Cells("D1").Value>19/24 Then
Autoclose
End If
End If
End Sub
where Autoclose is just a sub to close the book.
Since present() was volatile, it did indeed regularly change, but this didn't fire my workbook change macro (and produce the test value of 1) - I had to explicitly reenter =present() into D1 to get 1 to appear in B2, so it did work, but not in the way I expected (I tried making the sub volatile too, although maybe that only works for functions) but to no avail. Again, if I deleted the 1, it would not reappear, so I'm not getting it to fire properly.
Am I missing something easy - I'm just wanting to ensure that if the file is open somewhere on our network at night then it will be closed before the morning (when it opens and runs macros on my machine) so that the revised sheets can be saved before I get to work in the morning.
Many thanks.