Autosave Code Problem

[excellearnerz]

Good day,

I scrounged up a code I dont fully understand, but it seems to work. The goal of the code is that when there are any changes in the cell range I defined, the spreadsheet will autosave/export as a pdf file. However, I want that when another set of changes are submitted to the spreadsheet, it will save again with a sequential file name, and not overwrite the old save.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Step 1: Does the changed range intersect?
If Intersect(Target, Range("A1:AT55")) Is Nothing Then
'Step 2: If there is no intersection, exit procedure
Exit Sub
'Step 3: If there is an intersection, save the workbook
Else
Sheet4.ExportAsFixedFormat xlTypePDF, "C:\Users\Office-PC\Desktop\Destiny\INRToday.pdf"
'Step 4: Close out the If statement
End If
End Sub

Any help will be greatly appreciated.

 

[mole999]

i'm sure I have seen somewhere that formula changes don't trigger the worksheet change event, but I can't find it either way to prove or disprove

 

[excellearnerz]

It's true based on what I read a few seconds ago. An alternative is a worksheet calculate event. Pls sir can you show me a simple worksheet calculate code?

 

[mole999]

It's true based on what I read a few seconds ago. An alternative is a worksheet calculate event. Pls sir can you show me a simple worksheet calculate code?

I found this > How to Tell if a Cell Changed with VBA - Spreadsheets Made Easy

 

[Joe4]

The issue with Worksheet_Calculate event procedure code is that you cannot limit it to just changes on certain ranges. It will run anytime any cell on that sheet is updated by formula, and you cannot (within this code), determine which cell changed. So if there are other formulas on the sheet that you DON'T want to trigger this automated code to run, you will not want to use Worksheet_Calculate.

If you are manually updated cells on another sheet, that are used in formulas, why not use a Worksheet_Change on the sheet where you are entering the data?

 

[mole999]

daft idea, have a hidden sheet that monitors changes to the designed range and have that do the calculate, though as I type this, it won't be the active sheet so probably a non starter

 

[excellearnerz]

I need the event on the sheet itself because I will be adding 5 more sheets that will have their own event. The appropriate sheet will be updated with data based on the combination of input in my data entry form.

So I have this spreadsheet that lists all the submissions from the data entry form, and they are automatically numbered 1,2,3, so on. I added a worksheet calculate event on the spreadsheet I want to autosave, which is the sum function so, the first time I enter any data, it will be 1, then 3, then 6, then 10, etc. The problem is, even the range is only one cell, the event is still not triggering. What am I doing wrong Sirs?

 

[Joe4]

I added a worksheet calculate event on the spreadsheet I want to autosave,

Please post that code so we can see it.
We really cannot tell you want is wrong with if we cannot see it.

 

[excellearnerz]

Private Sub Worksheet_Calculate()
    Dim target As Range
    Set target = Range("AT55")

    If Not Intersect(target, Range("AT55")) Is Nothing Then

    Exit Sub

    Else

    Sheet4.ExportAsFixedFormat xlTypePDF, "C:\Users\Office-PC\Desktop\Destiny\INRToday.pdf" & Format(Now(), "DD-MMM-YYYY hh:mm:ss AMPM") & ".pdf")

    End If

End Sub

This is the code I tried last night.

AT55 is the cell with the sum formula. Does it fit the requirements for a worksheet calculate event?

 

[excellearnerz]

I need the event on the sheet itself because I will be adding 5 more sheets that will have their own event. The appropriate sheet will be updated with data based on the combination of input in my data entry form.

So I have this spreadsheet that lists all the submissions from the data entry form, and they are automatically numbered 1,2,3, so on. I added a worksheet calculate event on the spreadsheet I want to autosave, which is the sum function so, the first time I enter any data, it will be 1, then 3, then 6, then 10, etc. The problem is, even the range is only one cell, the event is still not triggering. What am I doing wrong Sirs?

Clarification: the sum function's range is the column of the serial no. that automatically gets numbered 1, 2, 3, and so on.

 

[Joe4]

The issue is you are trying to use the Worksheet_Calculate event like Worksheet_Change, and you cannot do that, as I mentioned before.

Look how each procedure is declared:
Private Sub Worksheet_Change(ByVal Target As Range)

Private Sub Worksheet_Calculate()

As you can see, Worksheet_Change has a Target parameter, but Worksheet_Calculate does NOT.

The problem with Worksheet_Calculate is there is no way to determine which cell's value changed. All that it can tell you is that "at least one cell on the sheet was re-calculated". So that means if you try to use Worksheet_Calculate, i is going to run anytime there is any re-calculation done anywhere on that sheet. Your export code might then be running a lot more than you want it to. If you manually update 5 cells elsewhere that are used in formulas on that sheet, you will have 5 exports.