End If

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

art - 13 Oct 2008 04:10 GMT

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

Reply to this Message

Rick Rothstein - 13 Oct 2008 05:59 GMT

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

Signature

Rick (MVP - Excel)

Reply to this Message

Dave Peterson - 13 Oct 2008 13:07 GMT

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

> Hello all:
>
[quoted text clipped - 20 lines]
> End If
> End Sub

Signature

Dave Peterson

Reply to this Message

art - 13 Oct 2008 14:26 GMT

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.

> I'm not sure what you're doing and why you sometimes use Activecell instead of
> Target, but maybe...
[quoted text clipped - 71 lines]
> > End If
> > End Sub

Reply to this Message

Dave Peterson - 13 Oct 2008 14:46 GMT

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.

> 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
[quoted text clipped - 81 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message