+ Reply to Thread
Results 1 to 2 of 2

I keep getting a syntax erring in my code?

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-18-2013
    Location
    utah
    MS-Off Ver
    2010
    Posts
    668

    I keep getting a syntax erring in my code?

    Private Sub WorkSheet_Change(ByVal Target As Range)
    
    Dim txtLocation As String
    Dim txtExtension As String
    Dim txtFileName As String
    Dim response As Integer
    
    Set Target = 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 " & Target & 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
    
    Exit Sub
       
    Err1:
    MsgBox "An error has occurred. Make sure the directory in the macro is valid." & _
        ActiveSheet.Protect "hi"
            
    End Sub

    I keep getting a syntax erro for this code please help.
    Last edited by arlu1201; 10-18-2013 at 02:00 AM. Reason: Corrected code tags.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,482

    Re: I keep getting a syntax erring in my code?

    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
    Last edited by Andy Pope; 10-18-2013 at 03:56 AM.
    Cheers
    Andy
    www.andypope.info

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Outlook Macro .MoveFile Erring
    By Anexandra in forum Outlook Programming / VBA / Macros
    Replies: 0
    Last Post: 10-16-2013, 01:49 PM
  2. code syntax
    By cmccabe in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-10-2013, 01:16 AM
  3. SUM Formula Erring
    By CriticalBill in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-20-2008, 06:33 PM
  4. [SOLVED] Syntax of VBA Code
    By Ken Hudson in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-14-2005, 06:05 PM
  5. Syntax of this code
    By ExcelMonkey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-21-2005, 07:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1