PDA

View Full Version : Worksheet DoubleClick Event



coliervile
03-22-2008, 11:08 AM
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

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

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

End Sub

Bob Phillips
03-22-2008, 11:13 AM
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

coliervile
03-22-2008, 11:15 AM
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
03-22-2008, 11:40 AM
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
03-22-2008, 11:59 AM
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

coliervile
03-22-2008, 12:11 PM
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
03-22-2008, 12:18 PM
Isn't that what I gave you. DoublClick to add, Rightlick to subtract.

coliervile
03-22-2008, 12:19 PM
I read the whole coding now...thanks Bob.

coliervile
03-22-2008, 01:49 PM
Is there a way to prevent (disabling) the pop-up window from displaying when using the BeforeRightClick in this coding?

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

mdmackillop
03-22-2008, 02:10 PM
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
03-22-2008, 02:30 PM
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
03-22-2008, 02:40 PM
So use the whole row

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

coliervile
03-22-2008, 03:05 PM
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.