Saving when Cell Completed

[tawnee jamison]

I am using this code to autosave to a specific server:


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
ActiveWorkbook.SaveAs Filename:="\\DTCNAS-ILSP002\Pricing\Builders Choice Completed\" & ActiveSheet.Range("a9")
End Sub

Can I modify this so that it saves only when a specific cell is filled?

Help!!! Thanks, Tawnee.

[daveydave60]

Put an if statement in to check which cell is selected before executing the save as algorithm. If the cell you want is the selected cell, then save as....., if some other cell is selected, then do nothing.

The macro will always run, but it will not always save (it might do nothing).

[tawnee jamison]

Can you give me an example?

Thanks, T.

[daveydave60]

Say you only want it to save when you change cell A1, your code might be:

----------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If ActiveCell.Address = $A$1 Then

ActiveWorkbook.SaveAs Filename:="\\DTCNAS-ILSP002\Pricing\Builders Choice Completed\" & ActiveSheet.Range("a9")

Else
'Do Nothing
End If

End Sub
-----------------------------------

I have not tested this, let me know if it works, or helps

[daveydave60]

Say you only want it to save when you change cell A1, your code might be:

----------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If ActiveCell.Address = $A$1 Then

ActiveWorkbook.SaveAs Filename:="\\DTCNAS-ILSP002\Pricing\Builders Choice Completed\" & ActiveSheet.Range("a9")

Else
'Do Nothing
End If

End Sub
-----------------------------------

I have not tested this, let me know if it works, or helps

[tawnee jamison]

Nope. Doesn't Work. It's a syntax error. This is basically what I keep running into.

Thanks, T.

[daveydave60]

I tried this and this is what i found:

The $A$1 needs to be put in quotes ---> "$A$1"

Even then, this runs whenevr that cell is selcted, not necessarily changed.

[daveydave60]

Here, you must use the Change event and this code should save whenever cell A1 is changed.

----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then

ActiveWorkbook.SaveAs Filename:="\\DTCNAS-ILSP002\Pricing\Builders Choice Completed\" & ActiveSheet.Range("a9")

Else

'Do Nothing

End If

End Sub
---------------------------------

Let me know how it works out.