excelvbaspell-checkingarray-merge

VBA Excel- Spell Check on Merged Cells


I have several merged cell ranges (unlocked) on a protected sheet that I want to spell check, respectively, based on the color index. Problem is 1) that it checks all these ranges and 2) after it is done checking, a message pops up asking 'Do you want to continue checking at the beginning of the sheet?' to which if you select 'yes', it will check the entire sheet (which is locked by the way)!!! Spell check for single cells I guess doesn't work the same for merged cells?

Questions are:

How do I spell check merged cells only?

How do I get rid of that message at the end (what ever happened to 'Spell check complete')?

        ActiveSheet.Unprotect

        If Range("A6:A8").Interior.ColorIndex = 16 Then
            ActiveSheet.Range("G33:P36").CheckSpelling
            
        ElseIf Range("A9:A13").Interior.ColorIndex = 16 Then
            ActiveSheet.Range("W33:AF36").CheckSpelling
            
        ElseIf Range("A14:A17").Interior.ColorIndex = 16 Then
            ActiveSheet.Range("AM33:AV36").CheckSpelling
            ActiveSheet.Range("BC33:BL36").CheckSpelling
            
        ElseIf Range("A18:A24").Interior.ColorIndex = 16 Then
            ActiveSheet.Range("BS33:CB36").CheckSpelling
            ActiveSheet.Range("CI33:CR36").CheckSpelling
            
        ElseIf Range("A25:A30").Interior.ColorIndex = 16 Then
            ActiveSheet.Range("CY33:DH36").CheckSpelling
            ActiveSheet.Range("DO33:DX36").CheckSpelling
            
        ElseIf Range("A31:A34").Interior.ColorIndex = 16 Then
            ActiveSheet.Range("EE33:EN36").CheckSpelling

        End If

        ActiveSheet.Protect

Solution

  • A workaround of this issue is to Unmerge and after spell checking Merge the cells. In this case at the end of the range the Spell checker will not show the pop-up.

    For the merged cells use this:
    (assume that DO33:DX36 is the merged range)

    ActiveSheet.Range("DO33:DX36").UnMerge
    ActiveSheet.Range("DO33:DX36").CheckSpelling
    ActiveSheet.Range("DO33:DX36").Merge
    

    In this case the popup is not shown.

    To check if a cell is merged Range("P39").MergeArea.Cells.Count>1 return TRUE if the cell (P39) is cell of a merged range.
    Also can be used Range("P39").DisplayFormat.MergeCells or directly Range("P39").MergeCells which returns a Boolean value.