Check to see if macro has run

[BENSON]

Ihave a macro that when runs backs up certain data from one worksheet (Daily Report)to to another (History ).Is it possible to check if the macro has been run prior to a user closing the first work sheet ,and if it has not been run for a message box to appear giving the user a choice to back up the data or not.Both sheets are used on a daily basis.

Thanks For Any Help

[Ozol]

I would have a Yes No message box come up on the before close, perhaps something like this



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Msg, Style, Title, Response, MyString
Msg = "Do you want to back up ?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "MsgBox Demonstration" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes"
' Run Macro hero
Else ' User chose No.
MyString = "No"
' Close.
End If
End Sub

[BENSON]

Thanks for suggestion, but only want message box to show if back up has not been done

[mdmackillop]

You could use CustomDocumentProperties in your event procedures


Sub TestBackup()
ActiveWorkbook.CustomDocumentProperties("Status") = "BackedUp"
If ActiveWorkbook.CustomDocumentProperties("Status") <> "BackedUp" Then
MsgBox "Do Backup"
End If
End Sub

[BENSON]

Thanks mdmackillop have not come across this type of code be for where would our place it in relation to the code below ?


Sub CopyRange()
Dim WsTgt As Excel.Worksheet
Dim rngCopy As Excel.Range
Application.ScreenUpdating = False
Set WsTgt = Workbooks("Gardens History.xls").Sheets(1)
With WsTgt.Range("A" & NextEmptyRow(WsTgt))
.Value = Date
.NumberFormat = "ddd dd mmm yy"
' Add C285 and C286
ActiveSheet.Range("C284").Copy
.Offset(, 1).PasteSpecial xlPasteValues
ActiveSheet.Range("C286").Copy
.Offset(, 2).PasteSpecial xlPasteValues
ActiveSheet.Range("C288").Copy
.Offset(, 3).PasteSpecial xlPasteValues
Set rngCopy = ActiveSheet.Range("G260:AZ260")
rngCopy.Copy
.Offset(, 4).PasteSpecial xlPasteValues
Application.ScreenUpdating = True
End With
End Sub


Function NextEmptyRow(Wks As Worksheet) As Long
Dim Rng As Range
Set Rng = Wks.Range("A" & Wks.Rows.Count).End(xlUp)
If Rng <> "" Then Set Rng = Rng.Offset(1)
NextEmptyRow = Rng.Row
End Function


Thanks for any help

[mdmackillop]

You may need to set the Custom Property manually initially.
In Worksheet module

Private Sub Worksheet_Activate()
ActiveWorkbook.CustomDocumentProperties("Status") = ""
End Sub

Private Sub Worksheet_Deactivate()
If ActiveWorkbook.CustomDocumentProperties("Status") <> "BackedUp" Then
If MsgBox("Do backup?", vbYesNo) = vbYes Then
CopyRange
Else
Exit Sub
End If
End If
End Sub

In Standard Module


Sub CopyRange()
Dim WsTgt As Excel.Worksheet
Dim WsSource As Excel.Worksheet
On Error GoTo Exits
Application.ScreenUpdating = False
Set WsSource = ActiveWorkbook.Sheets("Sheet1")
Set WsTgt = Workbooks("Gardens History.xls").Sheets(1)
With WsTgt.Range("A" & NextEmptyRow(WsTgt))
.Value = Date
.NumberFormat = "ddd dd mmm yy"
' Add C285 and C286
WsSource.Range("C284, C286, C288").Copy
.Offset(, 1).PasteSpecial xlPasteValues, , , True
WsSource.Range("G260:AZ260").Copy
.Offset(, 4).PasteSpecial xlPasteValues
End With
Application.ScreenUpdating = True
ActiveWorkbook.CustomDocumentProperties("Status") = "BackedUp"
MsgBox "Copied"
Exit Sub
Exits:
MsgBox "Not copied"
End Sub

Function NextEmptyRow(Wks As Worksheet) As Long
Dim Rng As Range
Set Rng = Wks.Range("A" & Wks.Rows.Count).End(xlUp)
If Rng <> "" Then Set Rng = Rng.Offset(1)
NextEmptyRow = Rng.Row
End Function

[Cyberdude]

I do something a little less elegant in situations like this one. I create a "flag" cell somewhere (doesn't matter too much where), and give it a defined name like "SAVERanFlg". Every time the workbook is opened (or whatever criteria you want) I have a statement in "Workbook_Open" something like:

Range("SAVERanFlg") = 0
where zero means that the save has not been performed.
Then in my save macro I would have the statement:

Range("SAVERanFlg") = 1
after the save has been successful to establish that the save had been done.
Then in macro "Workbook_BeforeClose" I test the flag cell to see if it's a 0 or a 1:

If Range("SAVERanFlg") = 0 Then . . .
which tests the flag value and takes whatever action you want if it's a 0.

As I said, it's clumsy, but gets the job done.

[BENSON]

Not sure what you mean re: (You may need to set the Custom Property manually initially.)
I copied the code as suggested but keep getting the message box "Not copied) which is the correct message as the macro is not copying and pasting .Could this be because I am doing somthing wrong? re "Custom Property" thanks again for taking the time and trouble to help me

[mdmackillop]

File/Properties/Custom
Select Status and insert value.

[BENSON]

Followed instructions , when you say insert value what do I enter ?

ie: the word value are some thing else

Thanks

[mdmackillop]

If ActiveWorkbook.CustomDocumentProperties("Status") <> "BackedUp" Then

Does this give you a clue?