How to control inbuilt information or display alerts in Excel 2007 Hello, I have

Question

How to control inbuilt information or display alerts in Excel 2007

Hello,

I have written a macro to run spell-check at sheetchange by sending F7 key. F7 key. When I copy/paste data in multiple cells then it is working fine wwithout any pop-up/message.

But when I write in a cell then it checks across sheet. Even if there is no error it gives message "The spelling check is complete for the cell text", The spelling check is complete for the entire sheet" or "Do you want to continue checking at the beginning of the sheet".

Is it possible to do the following:

Explanation / Answer

I am at a loss as to what "oXLApp" means in your sub name so I have just tested with the standard Workbook_SheetChange. However, the code below is tested and addresses all 3 problems but with some caveats as follows.

Exclude 'Delete' function in 'sheetchange'  Does not perform Spell Check if entire cell has been deleted but if you just delete some text from the cell then it will perform a spell check.

Exclude 'F2' or 'double click' It is not performing a Spell check when entering edit mode (ie. Double click or F2) but it does the Spell Check after completing the edit whether anything is changed or not but I should think that if anything is not spelt correctly after edit mode then you would want to perform a spell check. If you are using a Double click event then ensure you use Cancel = True as the first line of the event like the following example.

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

    Cancel = True 'First line of Double click event
    
    'Remaining code here.
End Sub

How can I control MS inbuilt pop-ups like "The spelling check is complete for the cell text".... This only occurs when only one cell is checked in the spell check so I have resized single cell to 2 cells. Of course it checks both cells but I am hoping that this will not cause too much of a problem.

Disabling events is to stop the sub being called again after a spelling mistake is corrected. Using the On Error routine is so that events do not remain off if the code errors for some reason. The MsgBoox is simply to provide a notification that an error occurred otherwise yoou would never be aware of it. If an errorr should be reported then you will need to comment out the On Error line and test without it and if an error occurs during testing thenn you need to re-enable events with the following code in the immediate window  Application.EnableEvents = True

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    On Error GoTo ReEnableEvents
    Application.EnableEvents = False
    
    If Target.Cells.Count > 1 Then
        Target.CheckSpelling
    Else
        If Target.Value = "" Then GoTo ReEnableEvents 'If value was deleted
        Target.Resize(1, 2).CheckSpelling
    End If
ReEnableEvents:
    If Err.Number <> 0 Then
        MsgBox "An error occurred in module ""ThisWorkbook, Private Sub Workbook_SheetChange." _
            & vbCrLf & "Report to Administrator of this application."
    End If
    Application.EnableEvents = True
End Sub

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.