Worksheet DoubleClick Event

[coliervile]

I want to add the number 1 to the cell value when I doubleclick the cell e.g. cell F10 value is 2 and when I doubleclick cell F10 the value changes to 3. I was trying this but it's not working. I receive a Run-time error'13': Type mismatch

[VBA]Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

mycount = Range("F1:F10") + 1
Range("F1:F10") = mycount

End Sub[/VBA]

[Bob Phillips]

[vba]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const WS_RANGE As String = "F1:F10"


If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If IsNumeric(.Value) Then

.Value = .Value + 1
End If
End With
End If
End Sub
[/vba]

[coliervile]

Thanks Bob I was looking through the KB's because I thought this issue was covered once before. Have a good rest of the day.

[coliervile]

I did modify your coding to come out of the cell with an offset...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const WS_RANGE As String = "F1:F10"


If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If IsNumeric(.Value) Then

.Value = .Value + 1
Target.Offset(0, 1).Select
End If
End With
End If
End Sub

Since the double_click has been used can another code be added to the Worksheet Event to subtract one (1) from the same cell ranges???

[Bob Phillips]

[vba]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const WS_RANGE As String = "F1:F10"


If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If IsNumeric(.Value) Then

.Value = .Value + 1
End If
.Offset(0, 1).Select
End With
End If

'Cancel = True
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Const WS_RANGE As String = "F1:F10"


If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If IsNumeric(.Value) Then

.Value = .Value - 1
End If
.Offset(0, 1).Select
End With
End If

'Cancel = True
End Sub
[/vba]

[coliervile]

Sorry Bob I messed you up by the way I asked the question. If I use the first code to add (+1) to cell F10 and changed the value from (2) to (3) and then wanted to go back to cell F10 and subtract (-1) from F10 value and make it (2) again. I would think something else would have to be used to do the subtraction part???

[Bob Phillips]

Isn't that what I gave you. DoublClick to add, Rightlick to subtract.

[coliervile]

I read the whole coding now...thanks Bob.

[coliervile]

Is there a way to prevent (disabling) the pop-up window from displaying when using the BeforeRightClick in this coding?

[VBA]Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Const WS_RANGE As String = "F1:F10" If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If IsNumeric(.Value) Then

.Value = .Value - 1
End If
.Offset(0, 1).Select
End With
End If

'Cancel = True
End Sub[/VBA]

[mdmackillop]

Here's something I've been playing around with. DoubleClick to the left in the cell to subtract and to the right to add. It seems to work in full screen, but not in an offset window.

[coliervile]

mdmackillop that's an interesting idea you designed. In my worksheet "Baseball Scorecard" alot of the cells are small in size and it would be hard for someone not to make an error when clicking. I will take a look at the code and see if I can use it though. I appreciate you help.

[mdmackillop]

So use the whole row
[VBA]
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Target.Column < 6 Then Cells(Target.Row, 6) = Cells(Target.Row, 6) - 1
If Target.Column > 6 Then Cells(Target.Row, 6) = Cells(Target.Row, 6) + 1
End Sub

[/VBA]

[coliervile]

I'll have to play around with this a bit and see how I can adjust for what I'm looking for. Thanks "MD" for the point in the right direction.