Nested If....Then...Else statement

[zJenkins]

Hi,

I'm getting an error (else without an if) on the following if then else statement:

Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As Boolean)


Dim Ans As Long


If target.Value = "" Then
    If target.Offset(0, -3) = "" Then
        usf_Main.Show
        Else: Ans = MsgBox("Add new row?", vbYesNo)
    End If
    If Ans = vbNo Then Exit Sub
        Else: target.EntireRow.Insert , copyorigin:=xlFormatFromRightOrAbove
    End If
    Else: usf_Adjust.Show
End If


End Sub

First it tests if the target cell is blank, if it is, then it tests if the cell three columns to the left is blank, if it is then a userform is opened. If the second tested cell isn't blank, then a msgbox opens asking to insert a new row.

 

[zJenkins]

Solved....I had:

[COLOR=#333333]If Ans = vbNo Then Exit Sub[/COLOR]

All as one line...it should have been:

[COLOR=#333333]If Ans = vbNo Then 
Exit Sub[/COLOR]

 

[joeu2004]

Or you could write (more simply, IMHO):

If target.Value = "" Then
    If target.Offset(0, -3) = "" Then
        usf_Main.Show
    Else
        Ans = MsgBox("Add new row?", vbYesNo)
    End If
    If Ans = vbNo Then Exit Sub
    target.EntireRow.Insert , copyorigin:=xlFormatFromRightOrAbove
Else
    usf_Adjust.Show
End If

Since Exit Sub is essentially a "go to", there is no need for an Else part.

Also notice how Else lines up with its matching If and End If. And the Else body starts on a new line and it is indented.

IMHO, that makes the code structure clearer.

FYI, you could also simplify the code structure by using the "one-line" If-Then-Else construct -- although, in this case, I would use the continuation "operator" to put Else on a separate line.

If target.Value = "" Then
    If target.Offset(0, -3) = "" Then usf_Main.Show _
    Else Ans = MsgBox("Add new row?", vbYesNo)
    If Ans = vbNo Then Exit Sub
    target.EntireRow.Insert , copyorigin:=xlFormatFromRightOrAbove
Else
    usf_Adjust.Show
End If

The second If statement is essentially:

If target.Offset(0, -3) = "" Then usf_Main.Show Else Ans = MsgBox("Add new row?", vbYesNo)

with a "line break" (underscore continuation operator).

 

[zJenkins]

Or you could write (more simply, IMHO):

If target.Value = "" Then
    If target.Offset(0, -3) = "" Then
        usf_Main.Show
    Else
        Ans = MsgBox("Add new row?", vbYesNo)
    End If
    If Ans = vbNo Then Exit Sub
    target.EntireRow.Insert , copyorigin:=xlFormatFromRightOrAbove
Else
    usf_Adjust.Show
End If

Since Exit Sub is essentially a "go to", there is no need for an Else part.

Also notice how Else lines up with its matching If and End If. And the Else body starts on a new line and it is indented.

IMHO, that makes the code structure clearer.

FYI, you could also simplify the code structure by using the "one-line" If-Then-Else construct -- although, in this case, I would use the continuation "operator" to put Else on a separate line.

If target.Value = "" Then
    If target.Offset(0, -3) = "" Then usf_Main.Show _
    Else Ans = MsgBox("Add new row?", vbYesNo)
    If Ans = vbNo Then Exit Sub
    target.EntireRow.Insert , copyorigin:=xlFormatFromRightOrAbove
Else
    usf_Adjust.Show
End If

The second If statement is essentially:

If target.Offset(0, -3) = "" Then usf_Main.Show Else Ans = MsgBox("Add new row?", vbYesNo)

with a "line break" (underscore continuation operator).

Thanks! I always appreciate the extra explanations and suggestions!