This should be 2 separate lines, rather than 1. So remove the appeand and continuation character
MsgBox "An error has occurred. Make sure the directory in the macro is valid."
ActiveSheet.Protect "hi"
The routine looks like an event routine, so you should not really be changing the Target variable reference.
if you want to run the code when D1 changes then
Private Sub WorkSheet_Change(ByVal Target As Range)
Dim txtLocation As String
Dim txtExtension As String
Dim txtFileName As String
Dim response As Integer
Dim strTargetName As String
If Target.Cells.Count = 1 Then
If Not Intersect(Target, Range("D1")) Is Nothing Then
strTargetName = Range("D1")
txtLocation = "D:\picking logs\" 'Change this to the location for the files always with a \ at end
txtExtension = ".xls" 'The extention for the file
txtFileName = txtLocation & "Batch " & strTargetName & txtExtension
'lets check of the file exists
If ThisWorkbook.FullName = txtFileName Then
'if the above is true then save the workbook
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
Else
On Error GoTo Err1
ThisWorkbook.SaveAs Filename:=txtFileName
End If
End If
End If
Exit Sub
Err1:
MsgBox "An error has occurred. Make sure the directory in the macro is valid."
ActiveSheet.Protect "hi"
End Sub
If you want it to run whenever a cell changes then remove the 2 IF commands and their matching End If
Bookmarks