Spellcheck function in Protected Sheet

[gwinder]

I have created an employee review form that contains code which enables text
wrap in merged cells. I have protected the sheet but this disables the
spellcheck functionality. I've seen some threads where you can insert code
to allow spellcheck to work but where in the code would this go? At the end
of my exisiting code? At the beginning?

Thanks,

Gary

 

[Gord Dibben]

Gary

Sub Spell_Check()
ActiveSheet.Unprotect Password:="justme"
Cells.CheckSpelling SpellLang:=1033
ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub

Where in your code would it go?

Can't see your code so have no idea.

I assume the word wrap code is event code. Right?

You could run it by calling from your current code.

Just insert the line Spell_Check at a point you feel is appropriate.

Place the Spell_Check macro in a general module.


Gord Dibben Excel MVP

 

[gwinder]

Thanks Gord.

Yes...my code was stolen for the forum for the wrap text with merged cells.
I'll try this. Is "justme" the password?

Gary

 

[gwinder]

Gord....here's the code I have which is under Microsoft Excel Object, not a
module

So where would I put the spellcheck code? and how does a user activate the
spell check?

Thanks,

Gary

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
On Error Resume Next
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
On Error GoTo 0
Application.ScreenUpdating = True
End If
End With
End Sub

 

[Gord Dibben]

Yes, "justme" is a password.

You can change it in the code to one of your liking or have no password at all.

If you do change the PW make sure it is in quotes like "newpassword"
If none at all change to

ActiveSheet.Unprotect
ActiveSheet.Protect


Gord

 

[Gord Dibben]

That is worksheet event code and I presume you have it in a sheet module.

It fires whenever you enter text into merged cells.

I misspoke in my first post.

You don't want to run spellcheck every time the event code fires so I would just
assign the Spell_Check macro to a button and run it whenever you choose.


Gord

 

[gwinder]

Gord...could you explain exactly how to assign the Spell_Check macro to a
button please?

Thanks,\Gary

 

[Gord Dibben]

View>Toolbars>Forms.

Click on the "Button" icon and draw a butoon on your worksheet.

Right-click and "assign macro".

Select the Spell_Check macro and OK.

Alternate method for putting a button on an existing Toolbar.

Tools>Customize>Commands.

Scroll down to "Macros" and select.

Drag the smiley-face button to your toolbar and right-click and "assign macro"

You can also "rename" or "change button image" or "edit button image" if you
don't like the smiley or the word "Custom"


Gord

 

[gwinder]

Gord...you're brilliant...thank you. Works great but I don't get prompted
for the password?

Gary

 

[Gord Dibben]

You wouldn't because you have used the password in the code at

ActiveSheet.Unprotect Password:="justme"

'do the spellcheck

ActiveSheet.Protect Password:="justme"

If you don't want others to see your code protect the VBAProject "lock project
for viewing" with another password.

Gord

 

[gwinder]

Thanks again...I protected the code under VBA Properties with a password but
I can still see the code without entering a password?

I went to View Code/Tools/VBA Project Properties/Protection

Clicked on Lock Project for Viewing and entered a password

In the General tab, am I suppoed to change the name of the project? Right
now it says VBAProject


Gary

 

[Gord Dibben]

Apologies for incomplete instructions.

After you lock the project for viewing you must save, close and reopen the
workbook before the "lock" takes place.

No, you don't have to change the name.


Gord