Spellchecking in excel 2003 and 2007 error

[hodgeod]

I am creating an excel version of the following form:
ftp://ftp.dot.state.tx.us/pub/txdot-info/trf/crash_amendments/2010_cr3.pdf
(horizontal lines in the narrative field will be omitted)

First,
I am a police officer, excel and vba are my hobby. I am creating this form for other police officers. Unfortunately, the majority of police officers still struggle with finding the “on” button when it comes to computers.

I am using excel 2003 but I need the form to work in excel 2007. The code works perfectly in 2003 but when I run it in 2007 I get the following error. (The textbox is not the one found under the developer tab under controls, rather it is from the drawing toolbar)

- Check Spelling of Textbox class failed

'=========================================================
Private Sub CheckNarrativeSpelling()

‘ Set dictionary to check spelling for officer’s who write in ALL CAPS
With Application.SpellingOptions
.UserDict = "CUSTOM.DIC"
.IgnoreCaps = False
End With

‘ Activate “Narrative” Texbox, spellcheck, then select next field
ActiveSheet.Shapes("Narrative").Select
Selection.CheckSpelling SpellLang:=1033
Range("AZ45:DO50").Select

‘ Reset Spelling options to ignore ALL CAPS
With Application.SpellingOptions
.IgnoreCaps = True
End With

MsgBox "The spelling check is complete.", vbInformation + vbOKOnly

End Sub
'=========================================================

- My attempts at a solution.
I used the macro recorder in excel 2007 and It would not recognize the “Narrative” textbox. Instead it returned with only:

Cells.CheckSpelling SpellLang:=1033

The result was that spellcheck ran, but it checked the entire worksheet instead of just the textbox.

My reasoning for using the textbox in Excel 2003 was that I only wanted the Textbox checked for spelling and the rest of the worksheet ignored.

I contemplated emedding an office word document into the narrative space on the form, but I know this would confuse normal police officers (because they would have to double-click). Also I used a textbox so the end user could not move or resize the embedded object.

I am open to alternative suggestions, and I have read about using a hidden sheet that is linked to the “Narrative” textbox and spellchecking the hidden sheet, but I don’t want the user to lose the context of the misspelled word.

I am looking for a solution either to amend the code in my procedure so that it works for excel 2003 and 2007, or an alternative suggestion that provides the following:
- one click access to the narrative portion of the form
- If an object is used then the user is not allowed to edit the object (size and move) but is allowed to edit the text.
- User initiated spellcheck for the narrative portion only (clicking a button to run the macro)

As always, any help is greatly appreciated. .

Thanks,
Patrick

 

[Andrew Poulsom]

I wasn't aware that you could spellcheck a textbox, so I've learnt something.

This worked for me in Excel 2007:

Sub Test()
    ActiveSheet.Shapes("Narrative").Select
    SendKeys "{F7}"
End Sub

 

[hodgeod]

Mr. Poulsom,

Thank you for your reply, I didn't even think of using the sendkey method for spellchecking. However, I ran into a problem when I protected the sheet.

Private Sub CheckNarrativeSpelling()
With Application.SpellingOptions
        .UserDict = "CUSTOM.DIC"
        .IgnoreCaps = False
End With
With Worksheets("Back Page")
   .Unprotect
        ActiveSheet.shapes("Narrative").Select
        SendKeys "{F7}"
   .Protect
End With
With Application.SpellingOptions
        .IgnoreCaps = True
End With
End Sub

When I run the code a messagebox states the following: "You cannot use this command on a protected sheet. To unprotect the ..."

When I protect the sheet as the workbook opens I set the UserInterfaceOnly to True (and it has worked on all my other macros).

Is there a method I can use to hold the protection until the spellcheck is complete? I found the wait method in the MSDN library but I don't think it will work for what I am trying to accomplish.

Thanks,
Patrick

 

[Andrew Poulsom]

This worked for me:

    With Worksheets("Back Page")
       .Unprotect
        ActiveSheet.Shapes("Narrative").Select
        Application.CommandBars("Worksheet Menu Bar").Controls("Tools").Controls("Spelling...").Execute
       .Protect
    End With

 

[hodgeod]

Works perfect.

Final Code:

Private Sub CheckNarrativeSpelling()
With Application.SpellingOptions
        .UserDict = "CUSTOM.DIC"
        .IgnoreCaps = False
End With
With Worksheets("Back Page")
        .Unprotect
        ActiveSheet.shapes("Narrative").Select
        Application.CommandBars("Worksheet Menu Bar").Controls("Tools").Controls("Spelling...").Execute
        .Protect
        Range("BP_TimeNotified1").Select
End With
With Application.SpellingOptions
        .IgnoreCaps = True
End With
MsgBox ("The spelling check is complete."), vbInformation + vbOKOnly, ("TxDOT CR-3 Narrative")
End Sub

It works on excel 2003 and excel 2007.
Thanks for all your help Mr. Poulsom!