VIsual basic code

[Arash_The_A_Level_Student]

Hi Could I have the code for automatically saving and exiting a workbook also when this is done 250 times I would like a message box to come up to say a custom message

 

[smozgur]

Arash_The_A_Level_Student,

Do you want to check 250th save in the same session or 250th save of the workbook?

Oz

 

[Mark O'Brien]

On 2002-03-07 12:48, Arash_The_A_Level_Student wrote:
Sub Exit_Save()
'
' Exit_Save Macro
' Exits And Saves The Excel Workbook
Dim Wkbk As Workbook
Dim i As Integer

'Turn off alerts if you don't care about overwriting workbooks with the same names
Application.DisplayAlerts = False
For Each Wkbk In Workbooks
If Not Wkbk Is ThisWorkbook Then
Wkbk.Save
Wkbk.Close
i = i + 1
If i = 2 Then
MsgBox "You have Saved Your Invoice 250 Times, It's Time To Save Your Invoice Onto Removeable Media"
End If
End If
Next
End Sub

'

This is the code I have entered on the VB editor but it doesn't work could you please give more assistance, thanks

Why are you putting the msgbox up when i = 2? I thought you wanted it after 250 times.


I misread your original post, I thought you wanted to save and close 250 workbooks.

From the message you're throwing up to the user, I can't see the point of hard coding the save and close parts at all. It seems that you just want to keep a track of how many times the user saves and/or closes a workbook.

This is pretty simple to do. All you need to do is put a counter in a cell that isn't used and increment this by one every time the user saves the workbook. You can put this in the workbook close event. Once this value hits 250 then you can throw up your message. I've done the thinking for you, you do the code. Is learning to program part of this A Level?


_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-03-07 13:42
This message was edited by Mark O'Brien on 2002-03-07 13:45

 

[Daniel Cremin]

well if its ict then yes/no - u can use some vba but they dont encourage out and out programming. A level computing does require it.

 

[smozgur]

I think you can increase a value which you store in a builtindocumentproperty when user save it (BeforeSave event in ThisDocument module). Code like this...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error Resume Next
ThisWorkbook.BuiltinDocumentProperties(13) = ThisWorkbook.BuiltinDocumentProperties(13) + 1
'Checking if property set before or not
If Err Then ThisWorkbook.BuiltinDocumentProperties(13) = 1
If ThisWorkbook.BuiltinDocumentProperties(13) = 250 Then
MsgBox "You have Saved Your Invoice 250 Times, It's Time To Save Your Invoice Onto Removeable Media" & vbCrLf & "Counter will be reset.", vbInformation + vbOKOnly, "Warning!"
ThisWorkbook.BuiltinDocumentProperties(13) = 0
End If
End Sub

Just an idea...
Oz

 

[Arash_The_A_Level_Student]

No we don't have to learn the language on the A level but my IT teacher doesn't know much and my text book has no reference on how to do this so I have to use this thing