BeforeDoubleClick Error 13 Type Mismatch - please help :)

[Bonnister]

Hello all

I'm using the following code in my workbook:

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
If Not Intersect(Target, Range("g28:bx48")) Is Nothing Then
Cancel = True

If Target.Value = "No" Then
Target.Value = "Yes"
Else
Target.Value = "No"
 
End If
End If

End Sub

However when I double click in the range the following error appears:

Run-Time Error 13 - Type Mismatch

I understand why the error is occuring - the worksheet is set up such that every 'cell' I click in within the range G28:BX48 comprises several columns merged into one cell (specifically 7 columns and 1 row). Every 'cell' in the range has the same merging conditions, ie: 7 columns, 1 row.

Is it possible to amend my code so that it recognises the 'target' as a merged cell range rather than a single cell? If so how do I do this please??

Any help is appreciated

Thank you all

Kenny

[HaHoBe]

Hi, Kenny,

best use would be to avoid any merged cells. You could try using MergeArea for determining the merged cells or maybe just try

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
If Not Intersect(Target, Range("g28:bx48")) Is Nothing Then
  Cancel = True
  With Target.Cells(1)
    If .Value = "No" Then
      .Value = "Yes"
    Else
      .Value = "No"
    End If
  End With
End If

End Sub

or

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
If Not Intersect(Target, Range("g28:bx48")) Is Nothing Then
  Cancel = True
  With Target.Cells(1)
    .Value = IIf(.Value = "No", "Yes", "No")
  End With
End If

End Sub

Ciao,
Holger

[Bonnister]

Hi Holger

Both solutions work a treat, agree with you about avoiding merged cells although in this instance the worksheet can't really avoid it without a thorough rebuild which isn't viable.

Thanks for your help

Kenny