"Type Mismatch" Error

[Ark68]

Sorry about the title ... I couldn't come up with an appropriate title to explain my issue.
The code I am posting below I have posted before in another thread. Of course it has evolved since then, but I wasn't sure if I should start a new thread fearing a "duplicate post" or if it was OK to start a new thread. The code is the same, the problem I believe to be different. My apologies in advance if I've double posted.

Sub chg_dsup()
    Stop
    If Not mbevents Then Exit Sub
    'MsgBox sctarget.Address
    If sctarget = "" Then
        MsgBox "Please ensure a value is set."
        mbevents = False
        Application.Undo
       Exit Sub
    End If
    With ws_form
        .Unprotect
        br = .Range(sctarget.Address).Row - 7
        bc = .Range(sctarget.Address).Column + 16
        compval = ws_blocks.Cells(br, bc)
        
        'if a cell identified as having missing info
        If .Range(sctarget.Address).Interior.Color = RGB(242, 146, 146) And sctarget <> "" Then
            .Range(sctarget.Address).Interior.Color = RGB(221, 235, 247)
            ws_blocks.Cells(br - 7, bc) = .Range(sctarget.Address).Value
        ' user change
        Else
            If sctarget <> ws_blocks.Cells(br, bc).Value Then
                chg = chg + 1
                With .Range(sctarget.Address)
                    .Font.Color = RGB(13, 58, 247)
                    .Font.Bold = True
                End With
            Else
                chg = chg - 1
                With .Range(sctarget.Address)
                    .Font.Color = vbBlack
                    .Font.Bold = False
                End With
            End If
        End If
        
        .Protect
    End With
   Stop
End Sub

The above code is executed by a worksheet change event when the user changes the value of a cell in a defined range. In testing, lets use K8. (just one of 11 possible cells open for the user to change to trigger this module). These cells have a data validation list of values for the user to select from.

The user changes the value of K8 and this routine is triggered. I assume it is working as I am getting the results I'm expecting. However, if the user deletes the value resulting in the cell being blank, my code (in blue) that I thought would protect my application from that comes back with an error "Type mismatch" with the line in red.

sctarget is publically defined as a range.

public sctarget as range

sctarget is set at the beginning of my worksheet change module ...

Set sctarget = target

This erronous line doesn't fail when the value is a permitted value (ie from the validation list), only when cell value of the cell that changed is "empty". When I watch the value of sctarget, if usually indicates the value of the cell although it is supposed to be a range so that is why I felt I culd use sctarget this way. But when the cell is empty, it appears I can't use it that way. I use the value of sctarget in other lines of code that have not created problems when the cell value is appropriate.

Anyone able to offer up a solution?

 

[jasonb75]

Even if it works without, it is always better to use .Value where appropriate to avoid problems like this (voice of experience). This applies to anything, not just range objects with .Value omitted.

If sctarget.Value = "" Then

If that doesn't cure it then I would guess that there is no range set to sctarget.

 

[Ark68]

Hi JasonB. Thanks for offering up your suggestion. Unfortunately, that didn't resolve the issue.
I 'm unsure why their would be no value associated with it. sctarget.address has a value.
Now, sctarget address is coming back as "$K$8:$L$8" as it is merged. (Always is merged which doesn't affect the performance of this code when the value of sctarget is legit). I know I know ... merged cells are the evil, but for I can't avoid it as part of the user interface.

 

[jasonb75]

I can't avoid it as part of the user interface

You can avoid it by using a single cell and changing the alignment to 'centre across selection'
Depending on the action taken with a merged cell, some things work, some don't. Comparing range value to string only works with single cells.

This should suffice as a workaround, but it is just that, not a solution.

If sctarget.Cells(1, 1).Value = "" Then

 

[Ark68]

JasonB, thanks agin for your support. The workaround works.
I tried the center-across-selection alignment, but I wasn't able to maintain the functionality of the interface the users had to rely on. The users have to double click these cells. When the value has to extend across 4 cells for instance, the cell needed to be one (ie merged) to accept a double clip. Perhaps there is a way to do that without merging, but that's a lesson for another day (and a lot of rewriting).

 

[jasonb75]

The workaround should be good for any similar occurrence where a merged cell is not accepted due to a single value / item being expected.

The time that you will come unstuck is if you need to loop through a range of merged cells, there will probably be workarounds, but they will be come increasingly more complicated.