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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.