including macro in protected worksheet

[Sandi Bredahl]

I have an excel 2007 spreadsheet that is protected. I found the
following macro that will allow other people to run spell check:

Sub Macro1()
Dim WS As Worksheet
Application.ScreenUpdating = False
For Each WS In ActiveWorkbook.Worksheets
WS.Select
ActiveSheet.Unprotect Password:="password"
Cells.CheckSpelling SpellLang:=1033
ActiveSheet.Protect Password:="password"
Next WS
Application.ScreenUpdating = True
End Sub

It unlocks file, runs spell check and locks file no problem. I save
the file as an Excel-Enabled worksheet - and expected the macro to
magically be included with the spreadsheet to whoever's computer and
work just as flawlessly.

I thought that when the "other" person opens the file, they would get
a security message that would give them the option of running the
macro. BUT IT DOESN'T! And the macro isn't with the file anymore.

Please help. What did I do wrong. This is driving me nuts.

 

[GS]

Where in the workbook did you put the macro?
How do users access the macro to run it?

Since all workbooks with macros must be saved as either XLSM or XLSA, I
assume you meant you saved it as a Macro-Enabled workbook (XLSM)!

In order to use the macro on other machines, Macro Security must be set
at least to 'Medium' level with notification option for users to
allow/disallow macros when the file is opened.

If the following macro is stored in a standard module then it can be
run via the Macros dialog...

Sub RunSpellCheck()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
With wks
.Unprotect Password:= "" '//enter your password
.Cells.CheckSpelling SpellLang:=1033
.Protect Password:= "" '//enter your password
End With 'wks
Next 'wks
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Gord Dibben]

Your code does not unlock any file...........it unprotects each
worksheet in a workbook and runs a spellcheck on that worksheet then
re-protects.

In the original workbook where did you store the macro?

In a general module?

You sure you saved as a macro-enabled workbook and that workbook is
available to others?

What are the macro security settings on the "others" computers?

Should be set to "disable with notification".


Gord