+ Reply to Thread
Results 1 to 6 of 6

Block If without End If

Hybrid View

  1. #1
    Registered User
    Join Date
    05-17-2015
    Location
    Bakersfield, CA
    MS-Off Ver
    Mac
    Posts
    42

    Block If without End If

    Why am I getting a Compile Error: "Block If without End If" with this code?

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      If Not Intersect(Target, Range("E65,J65,O65,M15,I15,C15")) Is Nothing Then
        Target.Value = Date
        Cancel = True
      ElseIf Not Intersect(Target, Range("B15,H15,J15,L15,J50:J57,C65,H65,M65")) Is Nothing Then
        Target.Value = Time
        Cancel = True
        ElseIf Not Intersect(Target, Range("O86:O96,N71:O78,G24:I31")) Is Nothing Then Exit Sub
    With Target
    If .Value = "£" Then
    .Value = "R"
    Else
    .Value = "£"
    Cancel = True
    End If
    End With
    End Sub

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Block If without End If

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      If Not Intersect(Target, Range("E65,J65,O65,M15,I15,C15")) Is Nothing Then
        Target.Value = Date
        Cancel = True
      ElseIf Not Intersect(Target, Range("B15,H15,J15,L15,J50:J57,C65,H65,M65")) Is Nothing Then
        Target.Value = Time
        Cancel = True
        ElseIf Not Intersect(Target, Range("O86:O96,N71:O78,G24:I31")) Is Nothing Then Exit Sub
    End if
    With Target
    If .Value = "£" Then
      .Value = "R"
    Else
      .Value = "£"
      Cancel = True
    End If
    
    End With
    End Sub

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: Block If without End If

    Try this.
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
        If Not Intersect(Target, Range("E65,J65,O65,M15,I15,C15")) Is Nothing Then
            Target.Value = Date
            Cancel = True
        ElseIf Not Intersect(Target, Range("B15,H15,J15,L15,J50:J57,C65,H65,M65")) Is Nothing Then
            Target.Value = Time
            Cancel = True
        ElseIf Not Intersect(Target, Range("O86:O96,N71:O78,G24:I31")) Is Nothing Then
            Exit Sub
        End If
        
        With Target
            If .Value = "£" Then
                .Value = "R"
            Else
                .Value = "£"
                Cancel = True
            End If
        End With
        
    End Sub
    If posting code please use code tags, see here.

  4. #4
    Registered User
    Join Date
    05-17-2015
    Location
    Bakersfield, CA
    MS-Off Ver
    Mac
    Posts
    42

    Re: Block If without End If

    That sort of worked... Except that my entire sheet throws £ everywhere now.

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: Block If without End If

    This part of the code will be executed whenever any cell on the sheet is double clicked.
        With Target
            If .Value = "£" Then
                .Value = "R"
            Else
                .Value = "£"
                Cancel = True
            End If
        End With
    If you want to restrict the range it's executed on then use If statements similar to those in the preceding code.

  6. #6
    Registered User
    Join Date
    05-17-2015
    Location
    Bakersfield, CA
    MS-Off Ver
    Mac
    Posts
    42

    Re: Block If without End If

    Thank you Norie

+ 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. [SOLVED] Block If without End If
    By katieshields in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-30-2014, 08:14 AM
  2. Block if without end if
    By malfrey in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-28-2013, 01:41 AM
  3. Replies: 5
    Last Post: 10-09-2012, 10:44 AM
  4. End if without If Block
    By cbh35711 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-12-2012, 04:32 PM
  5. Block if without end if?
    By vonblart in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-16-2010, 06:43 PM
  6. Replies: 2
    Last Post: 06-22-2010, 07:55 AM
  7. Block If
    By DA@PD in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 12-16-2005, 10:10 PM

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