What I am aiming for is as follows:-
I have a file with DDE links to an external MDB database and I would like to
save the file with the date and time in the file name.
I would like this option to have an 'on/off' command button or check box and
reference a cell value,say B5, in minutes, as the period at which the file is
saved.
I have found the following code (by Gary's Student) but I don't know how to
trigger it automatically.

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Thnaks in advance for any help.

In a standard code module add this code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("Sheet1").Range("B5").Value
Application.OnTime nTime, gsnu
End Sub


and in thisworkbook, add

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, gsnu, , False
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mark Dullingham" wrote in
message ...
What I am aiming for is as follows:-
I have a file with DDE links to an external MDB database and I would like
to
save the file with the date and time in the file name.
I would like this option to have an 'on/off' command button or check box
and
reference a cell value,say B5, in minutes, as the period at which the file
is
saved.
I have found the following code (by Gary's Student) but I don't know how
to
trigger it automatically.

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Thnaks in advance for any help.

Thanks Bob for your reply.

I've added the code as you said and added a hotspot to enable the module but
I have a choice of 2 macro's to choose from 1- gsnu and 2- SaveOften.

I have modified it to add the time, heres the code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Mark\My Documents\Work"
s2 = "DDE Sheet" & "." & Format(Date, dd - mm - yy) & "." & Format(Time, hh
- mm - ss)
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("216 Ground Floor").Range("E1").Value
Application.OnTime nTime, gsnu
End Sub

With gsnu assigned, when I click the hotspot I get a 'compile error variable
not defined' and the 'dd' of the date format is highlighted.

With SaveOften assigned I get a compile error of 'expected function or
variable'

I have appsolutely no idea what this means. I have tried the help files but
with no luck.

Your help and advice would be gratefully received

Mark

"Bob Phillips" wrote:

In a standard code module add this code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("Sheet1").Range("B5").Value
Application.OnTime nTime, gsnu
End Sub


and in thisworkbook, add

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, gsnu, , False
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mark Dullingham" wrote in
message ...
What I am aiming for is as follows:-
I have a file with DDE links to an external MDB database and I would like
to
save the file with the date and time in the file name.
I would like this option to have an 'on/off' command button or check box
and
reference a cell value,say B5, in minutes, as the period at which the file
is
saved.
I have found the following code (by Gary's Student) but I don't know how
to
trigger it automatically.

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Thnaks in advance for any help.

Bob

Sorted out the error, I'd missed "" form the date and time format.

This is sort of working now but as I have the mudule assigned to a hotspot
the save only works when I click it.

What I would really like to do is allow the user to select a check box to
run the macro at the interval entered into a cell, in this case E1.

I have tried putting all or part of the module code into a command button
code and tried 'Run xlgsnu' in a command button but nothing seems to work!

"Mark Dullingham" wrote:

Thanks Bob for your reply.

I've added the code as you said and added a hotspot to enable the module but
I have a choice of 2 macro's to choose from 1- gsnu and 2- SaveOften.

I have modified it to add the time, heres the code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Mark\My Documents\Work"
s2 = "DDE Sheet" & "." & Format(Date, dd - mm - yy) & "." & Format(Time, hh
- mm - ss)
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("216 Ground Floor").Range("E1").Value
Application.OnTime nTime, gsnu
End Sub

With gsnu assigned, when I click the hotspot I get a 'compile error variable
not defined' and the 'dd' of the date format is highlighted.

With SaveOften assigned I get a compile error of 'expected function or
variable'

I have appsolutely no idea what this means. I have tried the help files but
with no luck.

Your help and advice would be gratefully received

Mark

"Bob Phillips" wrote:

In a standard code module add this code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("Sheet1").Range("B5").Value
Application.OnTime nTime, gsnu
End Sub


and in thisworkbook, add

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, gsnu, , False
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mark Dullingham" wrote in
message ...
What I am aiming for is as follows:-
I have a file with DDE links to an external MDB database and I would like
to
save the file with the date and time in the file name.
I would like this option to have an 'on/off' command button or check box
and
reference a cell value,say B5, in minutes, as the period at which the file
is
saved.
I have found the following code (by Gary's Student) but I don't know how
to
trigger it automatically.

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Thnaks in advance for any help.

Mark,

Create a checkbox on the worksheet from the Forms toolbar, and add this
macro to a standard code module

Sub CheckBox1_Click()
If ActiveSheet.CheckBoxes(Application.Caller).Value = 1 Then
SaveOften
End If
End Sub


BTW, the line

Dim nTime As Double

that I gave you previously should be

Public nTime As Double

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mark Dullingham" wrote in
message ...
Bob

Sorted out the error, I'd missed "" form the date and time format.

This is sort of working now but as I have the mudule assigned to a hotspot
the save only works when I click it.

What I would really like to do is allow the user to select a check box to
run the macro at the interval entered into a cell, in this case E1.

I have tried putting all or part of the module code into a command button
code and tried 'Run xlgsnu' in a command button but nothing seems to work!

"Mark Dullingham" wrote:

Thanks Bob for your reply.

I've added the code as you said and added a hotspot to enable the module
but
I have a choice of 2 macro's to choose from 1- gsnu and 2- SaveOften.

I have modified it to add the time, heres the code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Mark\My Documents\Work"
s2 = "DDE Sheet" & "." & Format(Date, dd - mm - yy) & "." & Format(Time,
hh
- mm - ss)
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("216 Ground Floor").Range("E1").Value
Application.OnTime nTime, gsnu
End Sub

With gsnu assigned, when I click the hotspot I get a 'compile error
variable
not defined' and the 'dd' of the date format is highlighted.

With SaveOften assigned I get a compile error of 'expected function or
variable'

I have appsolutely no idea what this means. I have tried the help files
but
with no luck.

Your help and advice would be gratefully received

Mark

"Bob Phillips" wrote:

In a standard code module add this code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("Sheet1").Range("B5").Value
Application.OnTime nTime, gsnu
End Sub


and in thisworkbook, add

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, gsnu, , False
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Mark Dullingham" wrote in
message ...
What I am aiming for is as follows:-
I have a file with DDE links to an external MDB database and I would
like
to
save the file with the date and time in the file name.
I would like this option to have an 'on/off' command button or check
box
and
reference a cell value,say B5, in minutes, as the period at which the
file
is
saved.
I have found the following code (by Gary's Student) but I don't know
how
to
trigger it automatically.

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Thnaks in advance for any help.

Bob,

Sorry to have to come back to you again but I'm now getting this error-

Run-time error '1004'
Unable to get the CheckBoxes property of the Worksheet Class


?????

I've looked at some other posts about specifying a file name and from them
put together the following code-

Sub CheckBox1_Click()

Dim wb As Workbook
Dim ws As Worksheet.Name
Dim x As Variant
Dim y As Variant
Dim z As Variant
Dim strFileName As String
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("idrmterm")
x = Format(Date, "dd-mmm-yyyy")
y = Format(Time, "hh-mm-ss")
z = Now + Worksheets("DDE Sheet").Range("E1").Value
Dim strPath As String
strPath = "C:\Documents and Settings\Mark\My Documents\Work"
strFileName = x & " " & y & ws & ".xls"
If CheckBox1.Value = 1 Then
Application.OnTime Now, ActiveWorkbook.SaveCopyAs Filename:=strPath &
strFileName, Now + Worksheets("DDE Sheet").Range("E1").Value, True

End If
End Sub

If I've understood the OnTime correctly this would save the file instantly
and allow the process to be repeated in the amount of time specified in cell
E1

Only problem is I get a compile error Expected: end of statement with
Filename Highlighted

In the post I got this from 'Save with dates' on 2/28/2007 CV323 suggests
this part works.

Have you any ideas as to why this is happening and would this bir of code
work?

Many thanks once again

Mark


"Bob Phillips" wrote:

Mark,

Create a checkbox on the worksheet from the Forms toolbar, and add this
macro to a standard code module

Sub CheckBox1_Click()
If ActiveSheet.CheckBoxes(Application.Caller).Value = 1 Then
SaveOften
End If
End Sub


BTW, the line

Dim nTime As Double

that I gave you previously should be

Public nTime As Double

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mark Dullingham" wrote in
message ...
Bob

Sorted out the error, I'd missed "" form the date and time format.

This is sort of working now but as I have the mudule assigned to a hotspot
the save only works when I click it.

What I would really like to do is allow the user to select a check box to
run the macro at the interval entered into a cell, in this case E1.

I have tried putting all or part of the module code into a command button
code and tried 'Run xlgsnu' in a command button but nothing seems to work!

"Mark Dullingham" wrote:

Thanks Bob for your reply.

I've added the code as you said and added a hotspot to enable the module
but
I have a choice of 2 macro's to choose from 1- gsnu and 2- SaveOften.

I have modified it to add the time, heres the code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Mark\My Documents\Work"
s2 = "DDE Sheet" & "." & Format(Date, dd - mm - yy) & "." & Format(Time,
hh
- mm - ss)
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("216 Ground Floor").Range("E1").Value
Application.OnTime nTime, gsnu
End Sub

With gsnu assigned, when I click the hotspot I get a 'compile error
variable
not defined' and the 'dd' of the date format is highlighted.

With SaveOften assigned I get a compile error of 'expected function or
variable'

I have appsolutely no idea what this means. I have tried the help files
but
with no luck.

Your help and advice would be gratefully received

Mark

"Bob Phillips" wrote:

In a standard code module add this code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("Sheet1").Range("B5").Value
Application.OnTime nTime, gsnu
End Sub


and in thisworkbook, add

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, gsnu, , False
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Mark Dullingham" wrote in
message ...
What I am aiming for is as follows:-
I have a file with DDE links to an external MDB database and I would
like
to
save the file with the date and time in the file name.
I would like this option to have an 'on/off' command button or check
box
and
reference a cell value,say B5, in minutes, as the period at which the
file
is
saved.
I have found the following code (by Gary's Student) but I don't know
how
to
trigger it automatically.

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Thnaks in advance for any help.

I showed you how to do it in the previous posting.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mark Dullingham" wrote in
message ...
Bob,

Sorry to have to come back to you again but I'm now getting this error-

Run-time error '1004'
Unable to get the CheckBoxes property of the Worksheet Class


?????

I've looked at some other posts about specifying a file name and from them
put together the following code-

Sub CheckBox1_Click()

Dim wb As Workbook
Dim ws As Worksheet.Name
Dim x As Variant
Dim y As Variant
Dim z As Variant
Dim strFileName As String
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("idrmterm")
x = Format(Date, "dd-mmm-yyyy")
y = Format(Time, "hh-mm-ss")
z = Now + Worksheets("DDE Sheet").Range("E1").Value
Dim strPath As String
strPath = "C:\Documents and Settings\Mark\My Documents\Work"
strFileName = x & " " & y & ws & ".xls"
If CheckBox1.Value = 1 Then
Application.OnTime Now, ActiveWorkbook.SaveCopyAs Filename:=strPath &
strFileName, Now + Worksheets("DDE Sheet").Range("E1").Value, True

End If
End Sub

If I've understood the OnTime correctly this would save the file instantly
and allow the process to be repeated in the amount of time specified in
cell
E1

Only problem is I get a compile error Expected: end of statement with
Filename Highlighted

In the post I got this from 'Save with dates' on 2/28/2007 CV323 suggests
this part works.

Have you any ideas as to why this is happening and would this bir of code
work?

Many thanks once again

Mark


"Bob Phillips" wrote:

Mark,

Create a checkbox on the worksheet from the Forms toolbar, and add this
macro to a standard code module

Sub CheckBox1_Click()
If ActiveSheet.CheckBoxes(Application.Caller).Value = 1 Then
SaveOften
End If
End Sub


BTW, the line

Dim nTime As Double

that I gave you previously should be

Public nTime As Double

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Mark Dullingham" wrote in
message ...
Bob

Sorted out the error, I'd missed "" form the date and time format.

This is sort of working now but as I have the mudule assigned to a
hotspot
the save only works when I click it.

What I would really like to do is allow the user to select a check box
to
run the macro at the interval entered into a cell, in this case E1.

I have tried putting all or part of the module code into a command
button
code and tried 'Run xlgsnu' in a command button but nothing seems to
work!

"Mark Dullingham" wrote:

Thanks Bob for your reply.

I've added the code as you said and added a hotspot to enable the
module
but
I have a choice of 2 macro's to choose from 1- gsnu and 2-
SaveOften.

I have modified it to add the time, heres the code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Mark\My Documents\Work"
s2 = "DDE Sheet" & "." & Format(Date, dd - mm - yy) & "." &
Format(Time,
hh
- mm - ss)
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("216 Ground Floor").Range("E1").Value
Application.OnTime nTime, gsnu
End Sub

With gsnu assigned, when I click the hotspot I get a 'compile error
variable
not defined' and the 'dd' of the date format is highlighted.

With SaveOften assigned I get a compile error of 'expected function or
variable'

I have appsolutely no idea what this means. I have tried the help
files
but
with no luck.

Your help and advice would be gratefully received

Mark

"Bob Phillips" wrote:

In a standard code module add this code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("Sheet1").Range("B5").Value
Application.OnTime nTime, gsnu
End Sub


and in thisworkbook, add

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, gsnu, , False
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"Mark Dullingham" wrote
in
message ...
What I am aiming for is as follows:-
I have a file with DDE links to an external MDB database and I
would
like
to
save the file with the date and time in the file name.
I would like this option to have an 'on/off' command button or
check
box
and
reference a cell value,say B5, in minutes, as the period at which
the
file
is
saved.
I have found the following code (by Gary's Student) but I don't
know
how
to
trigger it automatically.

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Thnaks in advance for any help.

Bob,

I appologise I didn't mean to cause offence or seem ungrateful.

I followed you answers carefully and still got the error I descibed before (
Unable to get the CheckBoxes property of the Worksheet Class) and I have no
idea what that means, but to show that, although I am a novice at VBA, I do
read alot of previous post and do have a go myself I tried the code I posted.

Once again, sorry if I've caused offence and thank you for you help on this
issue and on previous ones you've helped me with.

Mark



"Bob Phillips" wrote:

I showed you how to do it in the previous posting.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mark Dullingham" wrote in
message ...
Bob,

Sorry to have to come back to you again but I'm now getting this error-

Run-time error '1004'
Unable to get the CheckBoxes property of the Worksheet Class


?????

I've looked at some other posts about specifying a file name and from them
put together the following code-

Sub CheckBox1_Click()

Dim wb As Workbook
Dim ws As Worksheet.Name
Dim x As Variant
Dim y As Variant
Dim z As Variant
Dim strFileName As String
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("idrmterm")
x = Format(Date, "dd-mmm-yyyy")
y = Format(Time, "hh-mm-ss")
z = Now + Worksheets("DDE Sheet").Range("E1").Value
Dim strPath As String
strPath = "C:\Documents and Settings\Mark\My Documents\Work"
strFileName = x & " " & y & ws & ".xls"
If CheckBox1.Value = 1 Then
Application.OnTime Now, ActiveWorkbook.SaveCopyAs Filename:=strPath &
strFileName, Now + Worksheets("DDE Sheet").Range("E1").Value, True

End If
End Sub

If I've understood the OnTime correctly this would save the file instantly
and allow the process to be repeated in the amount of time specified in
cell
E1

Only problem is I get a compile error Expected: end of statement with
Filename Highlighted

In the post I got this from 'Save with dates' on 2/28/2007 CV323 suggests
this part works.

Have you any ideas as to why this is happening and would this bir of code
work?

Many thanks once again

Mark


"Bob Phillips" wrote:

Mark,

Create a checkbox on the worksheet from the Forms toolbar, and add this
macro to a standard code module

Sub CheckBox1_Click()
If ActiveSheet.CheckBoxes(Application.Caller).Value = 1 Then
SaveOften
End If
End Sub


BTW, the line

Dim nTime As Double

that I gave you previously should be

Public nTime As Double

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Mark Dullingham" wrote in
message ...
Bob

Sorted out the error, I'd missed "" form the date and time format.

This is sort of working now but as I have the mudule assigned to a
hotspot
the save only works when I click it.

What I would really like to do is allow the user to select a check box
to
run the macro at the interval entered into a cell, in this case E1.

I have tried putting all or part of the module code into a command
button
code and tried 'Run xlgsnu' in a command button but nothing seems to
work!

"Mark Dullingham" wrote:

Thanks Bob for your reply.

I've added the code as you said and added a hotspot to enable the
module
but
I have a choice of 2 macro's to choose from 1- gsnu and 2-
SaveOften.

I have modified it to add the time, heres the code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Mark\My Documents\Work"
s2 = "DDE Sheet" & "." & Format(Date, dd - mm - yy) & "." &
Format(Time,
hh
- mm - ss)
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("216 Ground Floor").Range("E1").Value
Application.OnTime nTime, gsnu
End Sub

With gsnu assigned, when I click the hotspot I get a 'compile error
variable
not defined' and the 'dd' of the date format is highlighted.

With SaveOften assigned I get a compile error of 'expected function or
variable'

I have appsolutely no idea what this means. I have tried the help
files
but
with no luck.

Your help and advice would be gratefully received

Mark

"Bob Phillips" wrote:

In a standard code module add this code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("Sheet1").Range("B5").Value
Application.OnTime nTime, gsnu
End Sub


and in thisworkbook, add

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, gsnu, , False
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"Mark Dullingham" wrote
in
message ...
What I am aiming for is as follows:-
I have a file with DDE links to an external MDB database and I
would
like
to
save the file with the date and time in the file name.
I would like this option to have an 'on/off' command button or
check
box
and
reference a cell value,say B5, in minutes, as the period at which
the
file
is
saved.
I have found the following code (by Gary's Student) but I don't
know
how
to
trigger it automatically.

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Thnaks in advance for any help.

Mark, I wasn't chiding you mate, just pointing out that those instructions
were in my previous post.

Do you still have the problem?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mark Dullingham" wrote in
message ...
Bob,

I appologise I didn't mean to cause offence or seem ungrateful.

I followed you answers carefully and still got the error I descibed before
(
Unable to get the CheckBoxes property of the Worksheet Class) and I have
no
idea what that means, but to show that, although I am a novice at VBA, I
do
read alot of previous post and do have a go myself I tried the code I
posted.

Once again, sorry if I've caused offence and thank you for you help on
this
issue and on previous ones you've helped me with.

Mark



"Bob Phillips" wrote:

I showed you how to do it in the previous posting.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Mark Dullingham" wrote in
message ...
Bob,

Sorry to have to come back to you again but I'm now getting this error-

Run-time error '1004'
Unable to get the CheckBoxes property of the Worksheet Class


?????

I've looked at some other posts about specifying a file name and from
them
put together the following code-

Sub CheckBox1_Click()

Dim wb As Workbook
Dim ws As Worksheet.Name
Dim x As Variant
Dim y As Variant
Dim z As Variant
Dim strFileName As String
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("idrmterm")
x = Format(Date, "dd-mmm-yyyy")
y = Format(Time, "hh-mm-ss")
z = Now + Worksheets("DDE Sheet").Range("E1").Value
Dim strPath As String
strPath = "C:\Documents and Settings\Mark\My Documents\Work"
strFileName = x & " " & y & ws & ".xls"
If CheckBox1.Value = 1 Then
Application.OnTime Now, ActiveWorkbook.SaveCopyAs Filename:=strPath
&
strFileName, Now + Worksheets("DDE Sheet").Range("E1").Value, True

End If
End Sub

If I've understood the OnTime correctly this would save the file
instantly
and allow the process to be repeated in the amount of time specified in
cell
E1

Only problem is I get a compile error Expected: end of statement with
Filename Highlighted

In the post I got this from 'Save with dates' on 2/28/2007 CV323
suggests
this part works.

Have you any ideas as to why this is happening and would this bir of
code
work?

Many thanks once again

Mark


"Bob Phillips" wrote:

Mark,

Create a checkbox on the worksheet from the Forms toolbar, and add
this
macro to a standard code module

Sub CheckBox1_Click()
If ActiveSheet.CheckBoxes(Application.Caller).Value = 1 Then
SaveOften
End If
End Sub


BTW, the line

Dim nTime As Double

that I gave you previously should be

Public nTime As Double

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Mark Dullingham" wrote in
message ...
Bob

Sorted out the error, I'd missed "" form the date and time format.

This is sort of working now but as I have the mudule assigned to a
hotspot
the save only works when I click it.

What I would really like to do is allow the user to select a check
box
to
run the macro at the interval entered into a cell, in this case E1.

I have tried putting all or part of the module code into a command
button
code and tried 'Run xlgsnu' in a command button but nothing seems to
work!

"Mark Dullingham" wrote:

Thanks Bob for your reply.

I've added the code as you said and added a hotspot to enable the
module
but
I have a choice of 2 macro's to choose from 1- gsnu and 2-
SaveOften.

I have modified it to add the time, heres the code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Mark\My Documents\Work"
s2 = "DDE Sheet" & "." & Format(Date, dd - mm - yy) & "." &
Format(Time,
hh
- mm - ss)
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("216 Ground Floor").Range("E1").Value
Application.OnTime nTime, gsnu
End Sub

With gsnu assigned, when I click the hotspot I get a 'compile error
variable
not defined' and the 'dd' of the date format is highlighted.

With SaveOften assigned I get a compile error of 'expected function
or
variable'

I have appsolutely no idea what this means. I have tried the help
files
but
with no luck.

Your help and advice would be gratefully received

Mark

"Bob Phillips" wrote:

In a standard code module add this code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("Sheet1").Range("B5").Value
Application.OnTime nTime, gsnu
End Sub


and in thisworkbook, add

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, gsnu, , False
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the
worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail
in
my
addy)



"Mark Dullingham"
wrote
in
message
...
What I am aiming for is as follows:-
I have a file with DDE links to an external MDB database and I
would
like
to
save the file with the date and time in the file name.
I would like this option to have an 'on/off' command button or
check
box
and
reference a cell value,say B5, in minutes, as the period at
which
the
file
is
saved.
I have found the following code (by Gary's Student) but I don't
know
how
to
trigger it automatically.

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Thnaks in advance for any help.

Sorry Bob I was having a hard time getting my head around the OnTime thingy
and in particular stopping it.
I reposted to get a better understanding of the time variant needed and Chip
Pearson pointed me to his site, which I'd read a number of times before but
didn't quite get 'calling' a procedure. then the penny dropped so all is
rosey now.

Just battling with a userform which I want to run 2 Ontime events from, I'm
sure I'll need to post on something to do with it!

Thanks for you reply.

Mark

"Bob Phillips" wrote:

Mark, I wasn't chiding you mate, just pointing out that those instructions
were in my previous post.

Do you still have the problem?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mark Dullingham" wrote in
message ...
Bob,

I appologise I didn't mean to cause offence or seem ungrateful.

I followed you answers carefully and still got the error I descibed before
(
Unable to get the CheckBoxes property of the Worksheet Class) and I have
no
idea what that means, but to show that, although I am a novice at VBA, I
do
read alot of previous post and do have a go myself I tried the code I
posted.

Once again, sorry if I've caused offence and thank you for you help on
this
issue and on previous ones you've helped me with.

Mark



"Bob Phillips" wrote:

I showed you how to do it in the previous posting.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Mark Dullingham" wrote in
message ...
Bob,

Sorry to have to come back to you again but I'm now getting this error-

Run-time error '1004'
Unable to get the CheckBoxes property of the Worksheet Class


?????

I've looked at some other posts about specifying a file name and from
them
put together the following code-

Sub CheckBox1_Click()

Dim wb As Workbook
Dim ws As Worksheet.Name
Dim x As Variant
Dim y As Variant
Dim z As Variant
Dim strFileName As String
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("idrmterm")
x = Format(Date, "dd-mmm-yyyy")
y = Format(Time, "hh-mm-ss")
z = Now + Worksheets("DDE Sheet").Range("E1").Value
Dim strPath As String
strPath = "C:\Documents and Settings\Mark\My Documents\Work"
strFileName = x & " " & y & ws & ".xls"
If CheckBox1.Value = 1 Then
Application.OnTime Now, ActiveWorkbook.SaveCopyAs Filename:=strPath
&
strFileName, Now + Worksheets("DDE Sheet").Range("E1").Value, True

End If
End Sub

If I've understood the OnTime correctly this would save the file
instantly
and allow the process to be repeated in the amount of time specified in
cell
E1

Only problem is I get a compile error Expected: end of statement with
Filename Highlighted

In the post I got this from 'Save with dates' on 2/28/2007 CV323
suggests
this part works.

Have you any ideas as to why this is happening and would this bir of
code
work?

Many thanks once again

Mark


"Bob Phillips" wrote:

Mark,

Create a checkbox on the worksheet from the Forms toolbar, and add
this
macro to a standard code module

Sub CheckBox1_Click()
If ActiveSheet.CheckBoxes(Application.Caller).Value = 1 Then
SaveOften
End If
End Sub


BTW, the line

Dim nTime As Double

that I gave you previously should be

Public nTime As Double

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Mark Dullingham" wrote in
message ...
Bob

Sorted out the error, I'd missed "" form the date and time format.

This is sort of working now but as I have the mudule assigned to a
hotspot
the save only works when I click it.

What I would really like to do is allow the user to select a check
box
to
run the macro at the interval entered into a cell, in this case E1.

I have tried putting all or part of the module code into a command
button
code and tried 'Run xlgsnu' in a command button but nothing seems to
work!

"Mark Dullingham" wrote:

Thanks Bob for your reply.

I've added the code as you said and added a hotspot to enable the
module
but
I have a choice of 2 macro's to choose from 1- gsnu and 2-
SaveOften.

I have modified it to add the time, heres the code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Mark\My Documents\Work"
s2 = "DDE Sheet" & "." & Format(Date, dd - mm - yy) & "." &
Format(Time,
hh
- mm - ss)
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("216 Ground Floor").Range("E1").Value
Application.OnTime nTime, gsnu
End Sub

With gsnu assigned, when I click the hotspot I get a 'compile error
variable
not defined' and the 'dd' of the date format is highlighted.

With SaveOften assigned I get a compile error of 'expected function
or
variable'

I have appsolutely no idea what this means. I have tried the help
files
but
with no luck.

Your help and advice would be gratefully received

Mark

"Bob Phillips" wrote:

In a standard code module add this code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("Sheet1").Range("B5").Value
Application.OnTime nTime, gsnu
End Sub


and in thisworkbook, add

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, gsnu, , False
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the
worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail
in
my
addy)



"Mark Dullingham"
wrote
in
message
...
What I am aiming for is as follows:-
I have a file with DDE links to an external MDB database and I
would
like
to
save the file with the date and time in the file name.
I would like this option to have an 'on/off' command button or
check
box
and
reference a cell value,say B5, in minutes, as the period at
which
the
file
is
saved.
I have found the following code (by Gary's Student) but I don't
know
how
to
trigger it automatically.

Sub gsnu()

two ontime is quite straight-forward as long as you keep two very separate
time variables.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mark Dullingham" wrote in
message ...
Sorry Bob I was having a hard time getting my head around the OnTime
thingy
and in particular stopping it.
I reposted to get a better understanding of the time variant needed and
Chip
Pearson pointed me to his site, which I'd read a number of times before
but
didn't quite get 'calling' a procedure. then the penny dropped so all is
rosey now.

Just battling with a userform which I want to run 2 Ontime events from,
I'm
sure I'll need to post on something to do with it!

Thanks for you reply.

Mark

"Bob Phillips" wrote:

Mark, I wasn't chiding you mate, just pointing out that those
instructions
were in my previous post.

Do you still have the problem?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Mark Dullingham" wrote in
message ...
Bob,

I appologise I didn't mean to cause offence or seem ungrateful.

I followed you answers carefully and still got the error I descibed
before
(
Unable to get the CheckBoxes property of the Worksheet Class) and I
have
no
idea what that means, but to show that, although I am a novice at VBA,
I
do
read alot of previous post and do have a go myself I tried the code I
posted.

Once again, sorry if I've caused offence and thank you for you help on
this
issue and on previous ones you've helped me with.

Mark



"Bob Phillips" wrote:

I showed you how to do it in the previous posting.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Mark Dullingham" wrote in
message ...
Bob,

Sorry to have to come back to you again but I'm now getting this
error-

Run-time error '1004'
Unable to get the CheckBoxes property of the Worksheet Class


?????

I've looked at some other posts about specifying a file name and
from
them
put together the following code-

Sub CheckBox1_Click()

Dim wb As Workbook
Dim ws As Worksheet.Name
Dim x As Variant
Dim y As Variant
Dim z As Variant
Dim strFileName As String
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("idrmterm")
x = Format(Date, "dd-mmm-yyyy")
y = Format(Time, "hh-mm-ss")
z = Now + Worksheets("DDE Sheet").Range("E1").Value
Dim strPath As String
strPath = "C:\Documents and Settings\Mark\My Documents\Work"
strFileName = x & " " & y & ws & ".xls"
If CheckBox1.Value = 1 Then
Application.OnTime Now, ActiveWorkbook.SaveCopyAs
Filename:=strPath
&
strFileName, Now + Worksheets("DDE Sheet").Range("E1").Value, True

End If
End Sub

If I've understood the OnTime correctly this would save the file
instantly
and allow the process to be repeated in the amount of time specified
in
cell
E1

Only problem is I get a compile error Expected: end of statement
with
Filename Highlighted

In the post I got this from 'Save with dates' on 2/28/2007 CV323
suggests
this part works.

Have you any ideas as to why this is happening and would this bir of
code
work?

Many thanks once again

Mark


"Bob Phillips" wrote:

Mark,

Create a checkbox on the worksheet from the Forms toolbar, and add
this
macro to a standard code module

Sub CheckBox1_Click()
If ActiveSheet.CheckBoxes(Application.Caller).Value = 1 Then
SaveOften
End If
End Sub


BTW, the line

Dim nTime As Double

that I gave you previously should be

Public nTime As Double

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"Mark Dullingham" wrote
in
message ...
Bob

Sorted out the error, I'd missed "" form the date and time
format.

This is sort of working now but as I have the mudule assigned to
a
hotspot
the save only works when I click it.

What I would really like to do is allow the user to select a
check
box
to
run the macro at the interval entered into a cell, in this case
E1.

I have tried putting all or part of the module code into a
command
button
code and tried 'Run xlgsnu' in a command button but nothing seems
to
work!

"Mark Dullingham" wrote:

Thanks Bob for your reply.

I've added the code as you said and added a hotspot to enable
the
module
but
I have a choice of 2 macro's to choose from 1- gsnu and 2-
SaveOften.

I have modified it to add the time, heres the code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Mark\My Documents\Work"
s2 = "DDE Sheet" & "." & Format(Date, dd - mm - yy) & "." &
Format(Time,
hh
- mm - ss)
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("216 Ground
Floor").Range("E1").Value
Application.OnTime nTime, gsnu
End Sub

With gsnu assigned, when I click the hotspot I get a 'compile
error
variable
not defined' and the 'dd' of the date format is highlighted.

With SaveOften assigned I get a compile error of 'expected
function
or
variable'

I have appsolutely no idea what this means. I have tried the
help
files
but
with no luck.

Your help and advice would be gratefully received

Mark

"Bob Phillips" wrote:

In a standard code module add this code

Option Explicit

Dim nTime As Double

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

Sub SaveOften()
nTime = Now + Worksheets("Sheet1").Range("B5").Value
Application.OnTime nTime, gsnu
End Sub


and in thisworkbook, add

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, gsnu, , False
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the
worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be
gmail
in
my
addy)



"Mark Dullingham"
wrote
in
message
...
What I am aiming for is as follows:-
I have a file with DDE links to an external MDB database and
I
would
like
to
save the file with the date and time in the file name.
I would like this option to have an 'on/off' command button
or
check
box
and
reference a cell value,say B5, in minutes, as the period at
which
the
file
is
saved.
I have found the following code (by Gary's Student) but I
don't
know
how
to
trigger it automatically.

Sub gsnu()