Worksheet_Calculate
 

I have the following code in my work sheet (Excel 2003) for the purpose of
running a macro when a cell value is 1. This value is being moved into
another cell via OPC from Rockwell Software RSLinx. The macro saves a copy
of the workbook as the current date and time. The macro works as I like it
to as far as saving the workbook. The problem is that when the workbook is
first opened, I get a filetype mismatch error. After I stop or debug and
close the VB error, the macro works from then on without error. This is an
obvious nuisance, and I am not a VB programmer, so it is not obvoius to me.
Can someone help me? The line that is highlited in the debug is
"If Me.Range("H1").Value = "1" Then"



Private Sub Worksheet_Calculate()

If Me.Range("H1").Value = "1" Then
'MsgBox "Saving a Dated Copy...."
Application.EnableEvents = False
ActiveWorkbook.SaveCopyAs Filename:="C:\TA0_Data\" & _
Replace(ActiveWorkbook.Name, ".xls", _
" " & Format(Now, "yyyy-mm-dd-hh-mm") & ".xls")
End If
Application.EnableEvents = True

End Sub

Worksheet_Calculate
 

Found it myself...

Private Sub Worksheet_Calculate()

Dim Inrange As Range
Dim rng As Range
Set Inrange = Range("H1")
For Each rng In Inrange.Cells
If Not IsError(rng.Value) Then
If Me.Range("H1").Value = "1" Then
MsgBox "Saving a Dated Copy...."
Application.EnableEvents = False
ActiveWorkbook.SaveCopyAs Filename:="C:\Data\" & _
Replace(ActiveWorkbook.Name, ".xls", _
" " & Format(Now, "yyyy-mm-dd-hh-mm") & ".xls")
Application.EnableEvents = True
End If
End If
Next rng

End Sub



"tmkkoservo" wrote:

I have the following code in my work sheet (Excel 2003) for the purpose of
running a macro when a cell value is 1. This value is being moved into
another cell via OPC from Rockwell Software RSLinx. The macro saves a copy
of the workbook as the current date and time. The macro works as I like it
to as far as saving the workbook. The problem is that when the workbook is
first opened, I get a filetype mismatch error. After I stop or debug and
close the VB error, the macro works from then on without error. This is an
obvious nuisance, and I am not a VB programmer, so it is not obvoius to me.
Can someone help me? The line that is highlited in the debug is
"If Me.Range("H1").Value = "1" Then"



Private Sub Worksheet_Calculate()

If Me.Range("H1").Value = "1" Then
'MsgBox "Saving a Dated Copy...."
Application.EnableEvents = False
ActiveWorkbook.SaveCopyAs Filename:="C:\TA0_Data\" & _
Replace(ActiveWorkbook.Name, ".xls", _
" " & Format(Now, "yyyy-mm-dd-hh-mm") & ".xls")
End If
Application.EnableEvents = True

End Sub