PDA

View Full Version : Enabling Spellcheck in Locked Textbox



cbh35711
03-02-2012, 12:06 PM
Hello all,

Is there any way to enable spell checking in a textbox when the sheet is locked?

I'm working with excel 2007.

I appreciate any help you can provide.

Chris

GTO
03-02-2012, 10:58 PM
If you mean to use spellcheck manually, I do not know a way without first unprotecting the sheet.

If you are wanting to do this through code, here's what I came up with for a one-word textbox.


Option Explicit

Sub example()
Dim WD As Object
Dim shp As Shape
Dim strText As String
Dim strCorrection As String
Dim bolQuitApp As Boolean

'// See if we have an instance of Word or create one //
On Error Resume Next
Set WD = GetObject(, "Word.Application")
On Error GoTo 0

If WD Is Nothing Then
bolQuitApp = True
Set WD = CreateObject("Word.Application")
End If

'// Quickly checked and no research, but at least in 2010, it seems that we need to //
'// have at least one document, or .GetSpellingSuggestions(strText).Count jams. Not //
'// sure if that is true or I am missing something, or if it is true, why? //
'WD.Application.Visible = True
If WD.documents.Count = 0 Then
WD.documents.Add
End If

'// I wrote this in a worksheet's module, change to suit. //
Set shp = Me.Shapes("Textbox 1")

'// Just to show text box's initial value //
Me.Unprotect "1234"
shp.TextFrame.Characters.Text = "deallt"
Me.Protect "1234"

strText = shp.TextFrame.Characters.Text

'// See if Word's Application Method returns False, indicating a miss-spell. //
If Not WD.Application.CheckSpelling(strText) Then
'// See if there is at least one suggested replacement. //
If WD.Application.GetSpellingSuggestions(strText).Count > 0 Then
'// Return and use the first suggestgion. //
strCorrection = WD.Application.GetSpellingSuggestions(strText)(1)
Me.Unprotect "1234"
shp.TextFrame.Characters.Text = strCorrection
Me.Protect "1234"
End If
End If

'// If Word wasn't running, kill the copy we created. //
If bolQuitApp Then
WD.Application.Quit
Set WD = Nothing
End If
End Sub

Hope that helps,

Mark

mdmackillop
03-03-2012, 03:02 AM
Why not spell check as you enter the data?
Sheet module
Private Sub TextBox1_LostFocus()
Sheets(2).Cells(1, 1) = Me.TextBox1
Macro1
End Sub

Standard module
Sub Macro1()
Sheets(2).Cells.CheckSpelling SpellLang:=2057
Sheets(1).TextBox1.Text = Sheets(2).Cells(1, 1)
Sheets(2).Cells(1, 1).Clear
End Sub

cbh35711
03-03-2012, 08:05 AM
Works perfectly Mdmackillop.

Thank you,

Chris