End If

[art]

Hello all:

I have the following vba code, but it seems I have to many or too little
"else" or "End If". Please help me..

The code is as follows:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim MyOption As Long
If Target.Cells.Count > 1 Then Exit Sub
Cancel = True 'stop editing in cell
If MyOption = 1 Then
If ActiveCell.Value <= 1 Then
ActiveCell = ""
Else
If IsNumeric(Target.Value) Then
ActiveCell.Value = ActiveCell.Value - 1
End If
Else
If IsNumeric(Target.Value) Then
Target.Value = Target.Value + 1
End If
End Sub

 

[Rick Rothstein]

You are missing two End If statements (see the two marked lines)...


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim MyOption As Long
If Target.Cells.Count > 1 Then Exit Sub
Cancel = True 'stop editing in cell
If MyOption = 1 Then
If ActiveCell.Value <= 1 Then
ActiveCell = ""
Else
If IsNumeric(Target.Value) Then
ActiveCell.Value = ActiveCell.Value - 1
End If
Else
If IsNumeric(Target.Value) Then
Target.Value = Target.Value + 1
End If
End If ' <= This was missing
End If ' <= This was missing
End Sub

 

[Dave Peterson]

I'm not sure what you're doing and why you sometimes use Activecell instead of
Target, but maybe...

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim MyOption As Long
If Target.Cells.Count > 1 Then Exit Sub
Cancel = True 'stop editing in cell

If MyOption = 1 Then
If ActiveCell.Value <= 1 Then
ActiveCell.Value = ""
Else
If IsNumeric(Target.Value) Then
ActiveCell.Value = ActiveCell.Value - 1
End If
End If
Else
If IsNumeric(Target.Value) Then
Target.Value = Target.Value + 1
End If
End If
End Sub

And I thought MyOption was supposed to be a public variable. As it is written,
myOption will never be 1. So it's not doing much of a check.

This looks equivalent to me (still with the myOption problem).

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim MyOption As Long

If Target.Cells.Count > 1 Then Exit Sub
Cancel = True 'stop editing in cell

If MyOption = 1 Then
If Target.Value <= 1 Then
Target.Value = ""
End If
End If

If IsNumeric(Target.Value) _
And IsEmpty(Target.Value) = False Then
Target.Value = Target.Value + 1
End If

End Sub

 

[art]

I realized like you said that MyOption will never = 1. How else should I
write this so that MyOption should be either 1 or 2 depending on the users
selection?

Please let me know.

 

[Dave Peterson]

If you declare that variable in a General module and use the userform to toggle
its value, then you can use that in your _beforedoubleclick event.

And delete the Dim statement from the event, too.