run vb based on cell chance

[Trevor3007]

hi,

I have the following code:-

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Set Target = Range("d3")
If Target = "" Then Exit Sub
On Error GoTo Badname

ActiveSheet.Name = "Mytimes_WC_" & Format(Range("d3").Value, "dd-mm-yyyy")

Exit Sub
Badname:
MsgBox "Please revise the entry in d3." & Chr(13) _
& "It appears to contain one or more " & Chr(13) _
& "illegal characters." & Chr(13)
Range("d3").Activate
End Sub

and this code

Sub savetab()
'
' savetab Macro
'
ActiveWorkbook.SaveAs Filename:="C:\Users\Trevor\Desktop\Syn Timesheets From 010117" & Application.PathSeparator & ActiveWorkbook.Sheets(1).Name
'
End Sub



How do I combine them as one but only run when cell d3 has changed...

Also... when it saves, it checks to see if there is an existing same file name ( which is great) but if you say 'no' it comes back with the 'debug' error. Could you sort this too please.

MTIA
KR
Trevor3007

 

[MARK858]

Also... when it saves, it checks to see if there is an existing same file name

Where does it do this?

 

[Trevor3007]

Where does it do this?

Hi Mark ( again
it does this upon saving. At the moment there are 2 vb modules. This is the 2nd one


Sub savetab()
'
' savetab Macro
'
ActiveWorkbook.SaveAs Filename:="C:\Users\Test\Desktop\times 01042017" & Application.PathSeparator & ActiveWorkbook.Sheets(1).Name
'
End Sub

of the 2 I sent in my previous.

 

[MARK858]

There is nothing in that code that checks if the file exists, all it does is overwrite it if you click the yes in the box that appears when the code runs.

This is the same as if you saved the file manually to make sure you aren't accidently saving and overwriting the old file.

To answer your initial question you just put the code at the end of the first code.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Set Target = Range("d3")
    If Target = "" Then Exit Sub
    On Error GoTo Badname

    ActiveSheet.Name = "Mytimes_WC_" & Format(Range("d3").Value, "dd-mm-yyyy")

    Exit Sub
Badname:
    MsgBox "Please revise the entry in d3." & Chr(13) _
           & "It appears to contain one or more " & Chr(13) _
           & "illegal characters." & Chr(13)
    Range("d3").Activate
    ActiveWorkbook.SaveAs Filename:="C:\Users\Trevor\Desktop\Syn Timesheets From 010117" & Application.PathSeparator & ActiveWorkbook.Sheets(1).Name
End Sub

Although you should also have the file extension and fileformat in the code.