excel - Double click to insert character on merged cells -

excel - Double click to insert character on merged cells -

i want insert or remove "x" within cells within range ("a1:a19"), double clicking. code below placed on "microsoft excel objects\thisworkbook" in project macro.

private sub workbook_sheetbeforedoubleclick(byval sh object, byval target range, cancel boolean) if not intersect(target, range("a1:a19")) nil if len(trim(target)) = 0 target.value = "x" cancel = true elseif ucase(trim(target)) = "x" target.clearcontents cancel = true end if end if end sub

this code works non merged cells. however, have situation cells must merged ( 2 2, in column), , in situation next error:

"run-time error '13'" type mismatch

how must code modified prevent this?

try

private sub workbook_sheetbeforedoubleclick(byval sh object, byval target range, cancel boolean) if not intersect(target, range("a1:a19")) nil if target.cells.count = 1 ' handle single cell if len(trim(target)) = 0 target.value = "x" cancel = true elseif ucase(trim(target)) = "x" target.clearcontents cancel = true end if else ' handle merged dim theaddress string theaddress = split(target.address, ":")(0) & ":" & split(target.address, ":")(0) if len(trim(range(theaddress))) = 0 target.value = "x" cancel = true elseif ucase(trim(range(theaddress))) = "x" target.clearcontents cancel = true end if end if end if end sub

excel vba