Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Spell Check Unlocked Cells Only
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 29
Default Spell Check Unlocked Cells Only
Excell 2003: I'm using the macro found in the posts to unprotect and spell
check a worksheet. However, it checks the entire sheet, when I only want it
to check the unlocked cells.

Thanks in advance. ~Dorci
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 29
Default Spell Check Unlocked Cells Only
Sorry, I was rushed -- I misspelled Excel and didn't post an actual question.
Is there a way to specify "only unlocked cells" in the following spell check
code?

ActiveSheet.Unprotect

Cells.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True

ActiveSheet.Protect

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,939
Default Spell Check Unlocked Cells Only
Sub test()
Dim rngUnlocked As Range
Dim rng As Range

For Each rng In UsedRange
If rng.Locked = False Then
If rngUnlocked Is Nothing Then
Set rngUnlocked = rng
Else
Set rngUnlocked = Union(rng, rngUnlocked)
End If
End If

Next rng

ActiveSheet.Unprotect

rngUnlocked.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True

ActiveSheet.Protect

End Sub

--
HTH...

Jim Thomlinson


"Dorci" wrote:

Sorry, I was rushed -- I misspelled Excel and didn't post an actual question.
Is there a way to specify "only unlocked cells" in the following spell check
code?

ActiveSheet.Unprotect

Cells.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True

ActiveSheet.Protect

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 29
Default Spell Check Unlocked Cells Only
I'm getting the error "Object required" but it's not specifying where in the
code it's failing.

"Jim Thomlinson" wrote:

Sub test()
Dim rngUnlocked As Range
Dim rng As Range

For Each rng In UsedRange
If rng.Locked = False Then
If rngUnlocked Is Nothing Then
Set rngUnlocked = rng
Else
Set rngUnlocked = Union(rng, rngUnlocked)
End If
End If

Next rng

ActiveSheet.Unprotect

rngUnlocked.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True

ActiveSheet.Protect

End Sub

--
HTH...

Jim Thomlinson


"Dorci" wrote:

Sorry, I was rushed -- I misspelled Excel and didn't post an actual question.
Is there a way to specify "only unlocked cells" in the following spell check
code?

ActiveSheet.Unprotect

Cells.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True

ActiveSheet.Protect

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 29
Default Spell Check Unlocked Cells Only
Any idea why I would get theerror "Object required" when I insert the code
below?

"Jim Thomlinson" wrote:

Sub test()
Dim rngUnlocked As Range
Dim rng As Range

For Each rng In UsedRange
If rng.Locked = False Then
If rngUnlocked Is Nothing Then
Set rngUnlocked = rng
Else
Set rngUnlocked = Union(rng, rngUnlocked)
End If
End If

Next rng

ActiveSheet.Unprotect

rngUnlocked.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True

ActiveSheet.Protect

End Sub

--
HTH...

Jim Thomlinson


"Dorci" wrote:

Sorry, I was rushed -- I misspelled Excel and didn't post an actual question.
Is there a way to specify "only unlocked cells" in the following spell check
code?

ActiveSheet.Unprotect

Cells.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True

ActiveSheet.Protect

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 22,906
Default Spell Check Unlocked Cells Only
Try this change............

For Each rng In ActiveSheet.UsedRange


Gord Dibben MS Excel MVP


On Tue, 21 Jul 2009 12:20:01 -0700, Dorci
wrote:

Any idea why I would get theerror "Object required" when I insert the code
below?

"Jim Thomlinson" wrote:

Sub test()
Dim rngUnlocked As Range
Dim rng As Range

For Each rng In UsedRange
If rng.Locked = False Then
If rngUnlocked Is Nothing Then
Set rngUnlocked = rng
Else
Set rngUnlocked = Union(rng, rngUnlocked)
End If
End If

Next rng

ActiveSheet.Unprotect

rngUnlocked.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True

ActiveSheet.Protect

End Sub

--
HTH...

Jim Thomlinson


"Dorci" wrote:

Sorry, I was rushed -- I misspelled Excel and didn't post an actual question.
Is there a way to specify "only unlocked cells" in the following spell check
code?

ActiveSheet.Unprotect

Cells.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True

ActiveSheet.Protect


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 29
Default Spell Check Unlocked Cells Only
THAT WORKED!!! Thanks so much!
~Dorci

"Gord Dibben" wrote:

Try this change............

For Each rng In ActiveSheet.UsedRange


Gord Dibben MS Excel MVP


On Tue, 21 Jul 2009 12:20:01 -0700, Dorci
wrote:

Any idea why I would get theerror "Object required" when I insert the code
below?

"Jim Thomlinson" wrote:

Sub test()
Dim rngUnlocked As Range
Dim rng As Range

For Each rng In UsedRange
If rng.Locked = False Then
If rngUnlocked Is Nothing Then
Set rngUnlocked = rng
Else
Set rngUnlocked = Union(rng, rngUnlocked)
End If
End If

Next rng

ActiveSheet.Unprotect

rngUnlocked.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True

ActiveSheet.Protect

End Sub

--
HTH...

Jim Thomlinson


"Dorci" wrote:

Sorry, I was rushed -- I misspelled Excel and didn't post an actual question.
Is there a way to specify "only unlocked cells" in the following spell check
code?

ActiveSheet.Unprotect

Cells.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True

ActiveSheet.Protect



Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 29
Default Spell Check Unlocked Cells Only
I discovered a problem with the code. When it protects the sheet at the end,
it doesn't keep all of the protection settings it had originally. The
original settings allow users to (1) select locked cells, (2) select unlocked
cells, (3) format cells, (4) format columns, (5) format rows. After the
spell check, the only options set are (1) and (2).

Can I specify the settings in the ActiveSheet.Protect command?

Thanks!

"Gord Dibben" wrote:

Try this change............

For Each rng In ActiveSheet.UsedRange


Gord Dibben MS Excel MVP


On Tue, 21 Jul 2009 12:20:01 -0700, Dorci
wrote:

Any idea why I would get theerror "Object required" when I insert the code
below?

"Jim Thomlinson" wrote:

Sub test()
Dim rngUnlocked As Range
Dim rng As Range

For Each rng In UsedRange
If rng.Locked = False Then
If rngUnlocked Is Nothing Then
Set rngUnlocked = rng
Else
Set rngUnlocked = Union(rng, rngUnlocked)
End If
End If

Next rng

ActiveSheet.Unprotect

rngUnlocked.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True

ActiveSheet.Protect

End Sub

--
HTH...

Jim Thomlinson


"Dorci" wrote:

Sorry, I was rushed -- I misspelled Excel and didn't post an actual question.
Is there a way to specify "only unlocked cells" in the following spell check
code?

ActiveSheet.Unprotect

Cells.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True

ActiveSheet.Protect



Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 22,906
Default Spell Check Unlocked Cells Only
Remove the "ActiveSheet.Protect" line above "End Sub"

Replace with this construction

With ActiveSheet
.Protect AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
.EnableSelection = xlNoRestrictions
End With


Gord




On Mon, 3 Aug 2009 12:43:01 -0700, Dorci
wrote:

I discovered a problem with the code. When it protects the sheet at the end,
it doesn't keep all of the protection settings it had originally. The
original settings allow users to (1) select locked cells, (2) select unlocked
cells, (3) format cells, (4) format columns, (5) format rows. After the
spell check, the only options set are (1) and (2).

Can I specify the settings in the ActiveSheet.Protect command?

Thanks!

"Gord Dibben" wrote:

Try this change............

For Each rng In ActiveSheet.UsedRange


Gord Dibben MS Excel MVP


On Tue, 21 Jul 2009 12:20:01 -0700, Dorci
wrote:

Any idea why I would get theerror "Object required" when I insert the code
below?

"Jim Thomlinson" wrote:

Sub test()
Dim rngUnlocked As Range
Dim rng As Range

For Each rng In UsedRange
If rng.Locked = False Then
If rngUnlocked Is Nothing Then
Set rngUnlocked = rng
Else
Set rngUnlocked = Union(rng, rngUnlocked)
End If
End If

Next rng

ActiveSheet.Unprotect

rngUnlocked.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True

ActiveSheet.Protect

End Sub

--
HTH...

Jim Thomlinson


"Dorci" wrote:

Sorry, I was rushed -- I misspelled Excel and didn't post an actual question.
Is there a way to specify "only unlocked cells" in the following spell check
code?

ActiveSheet.Unprotect

Cells.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True

ActiveSheet.Protect




Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 29
Default Spell Check Unlocked Cells Only
*Whew* That worked! Thanks Gord!

~Dorci

"Gord Dibben" wrote:

Remove the "ActiveSheet.Protect" line above "End Sub"

Replace with this construction

With ActiveSheet
.Protect AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
.EnableSelection = xlNoRestrictions
End With


Gord




On Mon, 3 Aug 2009 12:43:01 -0700, Dorci
wrote:

I discovered a problem with the code. When it protects the sheet at the end,
it doesn't keep all of the protection settings it had originally. The
original settings allow users to (1) select locked cells, (2) select unlocked
cells, (3) format cells, (4) format columns, (5) format rows. After the
spell check, the only options set are (1) and (2).

Can I specify the settings in the ActiveSheet.Protect command?

Thanks!

"Gord Dibben" wrote:

Try this change............

For Each rng In ActiveSheet.UsedRange


Gord Dibben MS Excel MVP


On Tue, 21 Jul 2009 12:20:01 -0700, Dorci
wrote:

Any idea why I would get theerror "Object required" when I insert the code
below?

"Jim Thomlinson" wrote:

Sub test()
Dim rngUnlocked As Range
Dim rng As Range

For Each rng In UsedRange
If rng.Locked = False Then
If rngUnlocked Is Nothing Then
Set rngUnlocked = rng
Else
Set rngUnlocked = Union(rng, rngUnlocked)
End If
End If

Next rng

ActiveSheet.Unprotect

rngUnlocked.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True

ActiveSheet.Protect

End Sub

--
HTH...

Jim Thomlinson


"Dorci" wrote:

Sorry, I was rushed -- I misspelled Excel and didn't post an actual question.
Is there a way to specify "only unlocked cells" in the following spell check
code?

ActiveSheet.Unprotect

Cells.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True

ActiveSheet.Protect





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel should spell-check all the cells together. Bart Excel Worksheet Functions 1 November 24th 08 02:20 PM
Can you spell check protected but unlocked cells in excel2000? MarkB Excel Discussion (Misc queries) 1 October 3rd 06 05:36 PM
Cells become unlocked and visible using a check box Luke Excel Discussion (Misc queries) 0 October 11th 05 06:13 PM
Spell Check an Unlocked Cell in a Protected Spreadsheet Jill E Excel Worksheet Functions 0 August 22nd 05 07:57 PM
Protecting certain cells only & Spell check Gus Excel Discussion (Misc queries) 3 March 18th 05 11:57 PM


All times are GMT +1. The time now is 07:13 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement