|
End If
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 |
End If
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 -- Rick (MVP - Excel) |
End If
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 wrote: 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 -- Dave Peterson |
End If
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" wrote: 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 wrote: 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 -- Dave Peterson |
End If
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. art wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 07:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com