Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Spell check - protected worksheet (2003)

    Loungers,

    I have a spreadsheet that has some cell locked, the unlocked cells are to input text, however I note that when the sheet is protected the spell checker is turned off.

    Is there anyway to have locked cells and also have spell checker active for unlocked cells?

  2. #2
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Spell check - protected worksheet (2003)

    Hi Dean:

    Since the Spell Checker is disabled when a sheet is protected, the only solution I can see would be to create a 'SpellCheck' procedure in VBA that unprotects, checks and then re-protects the W/Sheet.

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Spell check - protected worksheet (2003)

    Hi Dean

    As Tony says, how about unprotecting first, spell checking, protecting and closing. This code in the ThisWorkbook part will do the job.

    <pre>Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cells.Select
    ActiveSheet.Unprotect
    Selection.CheckSpelling SpellLang:=2057
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End Sub
    </pre>


    I protected my wb with a password, it asks me before it unprotects. HTH
    Jerry

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Spell check - protected worksheet (2003)

    Tony and Jerry - thanks for your suggestions - will give them a go

  5. #5
    Lounger
    Join Date
    Jun 2002
    Location
    Placerville, California, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Spell check - protected worksheet (2003)

    Thanks for this...I have been trying to accomplish this (and I'm not very good with macros). But I have another question. I assigned this macro to a toolbar that I created. Is there a way to make this toolbar always appear? I have done this with my macros for spelling forms in Word but the code that I used does not work (Application.CommandBars("SpellCheckForm").Visible = True where "SpellCheckForm" is the name of the toolbar. Any suggestions?

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Spell check - protected worksheet (2003)

    Hi there

    Welcome to Woody's Lounge

    Have you tried

    Application.CommandBars("SpellCheckForm").Enabled = True
    Jerry

  7. #7
    Lounger
    Join Date
    Jun 2002
    Location
    Placerville, California, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Spell check - protected worksheet (2003)

    I think that worked; however, the close button is still on the toolbar so a user could accidentally close the toolbar. Is there a way to lock it like in Word?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Spell check - protected worksheet (2003)

    You can use this line of code to "lock" the toolbar:

    CommandBars("SpellCheckForm").Protection = msoBarNoChangeVisible

    If you ever want to undo this:

    CommandBars("SpellCheckForm").Protection = msoBarNoProtection

  9. #9
    Lounger
    Join Date
    Jun 2002
    Location
    Placerville, California, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Spell check - protected worksheet (2003)

    I think I need one more line of code...or something [img]/forums/images/smilies/smile.gif[/img]

    Now the toolbar no longer has the close button, but even though I saved the macro to the current workbook, the toolbar is available with all excel files, including when I close excel and reopen...it's there.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Spell check - protected worksheet (2003)

    You need to do two things:

    1) Attach the toolbar to the workbook.
    - Open the workbook (if necessary).
    - Select Tools | Customize...
    - Activate the Toolbars tab.
    - Click Attach...
    - Select SpellCheckForm in the list of custom toolbars in the list on the left.
    - Click Copy>>.
    - Click OK.
    - Close the Customize dialog.
    - Save the workbook.
    The toolbar has now been stored in the workbook.

    2) Create code to delete the toolbar when the workbook is closed.
    - Activate the Visual Basic Editor.
    - Double-click ThisWorkbook in the Project Explorer.
    - Add the following procedure:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.CommandBars("SpellCheckForm").Delete
    End Sub

    - Switch to Excel and save the workbook.

  11. #11
    Lounger
    Join Date
    Jun 2002
    Location
    Placerville, California, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Spell check - protected worksheet (2003)

    Thanks so much! that turns off the toolbar after the macro is run and the toolbar is no longer seen in other workbooks...now the however...the macro does not turn on automatically when I open the workbook. I tried to add this code at the beginnign of the macro, but it didn't work. Any more ideas?

    Application.CommandBars("Spell_Check").Visible = True

    The following is my macro as it is currently:

    Sub Workbook_BeforeClose()

    Application.CommandBars("Spell_Check").Visible = True
    Application.CommandBars("Spell_Check").Enabled = True
    CommandBars("Spell_Check").Protection = msoBarNoChangeVisible

    Cells.Select
    ActiveSheet.Unprotect
    Selection.CheckSpelling SpellLang:=2057
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

    Application.Goto Reference:="R1C1"

    Application.CommandBars("Spell_Check").Delete


    End Sub

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Spell check - protected worksheet (2003)

    If you have attached the toolbar to the workbook, it should automatically be displayed when the workbook is opened. If it doesn't, perhaps an add-in is interfering, you might take a look at Jan Karel Pieterse's <!post=Systematic Approach to Behavioral Problems in XL,290455>Systematic Approach to Behavioral Problems in XL<!/post>.

    The Workbook_BeforeClose event procedure runs, as the name indicates, just before the workbook is closed; there is no point in making the toolbar visible there. You could create a Workbook_Open event procedure in the same module:
    <code>
    Private Sub Workbook_Open()
    With Application.CommandBars("Spell_Check")
    .Visible = True
    .Enabled = True
    .Protection = msoBarNoChangeVisible
    End With
    End Sub
    </code>
    This will be run automatically when the workbook is opened.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •