Spell Checking on a protected sheet

Hi Folks,

I have a sheet that is protected, and have the following code on a 
control_click event to allow spell checking. It unprotects the sheet, runs 
the spelling check function, then re-protects the sheet with the original 
password. It also allows row height to be formatted when the sheet is 
protected.

  Private Sub SpellCheck_Click()

    ActiveSheet.Unprotect Password:="Password"
      Cells.CheckSpelling _
      CustomDictionary:="CUSTOM.DIC", _
       IgnoreUppercase:=False, _
       AlwaysSuggest:=True
    ActiveSheet.Protect Password:="Password"
' Allow rows to be formatted (autofit) on a protected worksheet.
    If ActiveSheet.Protection.AllowFormattingRows = False Then
        ActiveSheet.Protect AllowFormattingRows:=True
    End If
End Sub

This works fine if there is at least one spelling error. However, if there 
are zero errors on the sheet, then this code re-protects the sheet without a 
password. Any user can run the spell checker to remove the password, then 
just unprotect the sheet. 

Any suggestions on how to fix this? I'd like to allow users to run spell 
checking and adjust row height, but I want it to stay password protected too!

Thanks!
0
Utf
3/4/2010 8:59:02 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
776 Views

Similar Articles

[PageSpeed] 4

Thats simply because you have the PROTECT code within the IF statement,
put it after the END IF and all should be fine.
 


mooresk257;661544 Wrote: 
> 
Hi Folks,
 
I have a sheet that is protected, and have the following code on a
control_click event to allow spell checking. It unprotects the sheet,
runs
the spelling check function, then re-protects the sheet with the
original
password. It also allows row height to be formatted when the sheet is
protected.
 
Private Sub SpellCheck_Click()
 
ActiveSheet.Unprotect Password:="Password"
Cells.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True
ActiveSheet.Protect Password:="Password"
' Allow rows to be formatted (autofit) on a protected worksheet.
If ActiveSheet.Protection.AllowFormattingRows = False Then
ActiveSheet.Protect AllowFormattingRows:=True
End If
End Sub
 
This works fine if there is at least one spelling error. However, if
there
are zero errors on the sheet, then this code re-protects the sheet
without a
password. Any user can run the spell checker to remove the password,
then
just unprotect the sheet.
 
Any suggestions on how to fix this? I'd like to allow users to run
spell
checking and adjust row height, but I want it to stay password
protected too!
 
Thanks!


-- 
Simon Lloyd

Regards,
Simon Lloyd
'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=184741

[url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url]

0
Simon
3/4/2010 9:10:53 PM
You also have this:


VBA Code:
--------------------
  

  
If ActiveSheet.Protection.AllowFormattingRows = False Then
ActiveSheet.Protect AllowFormattingRows:=True
End If

--------------------


Which is protecting without a password, it should read
 


VBA Code:
--------------------
  

  
If ActiveSheet.Protection.AllowFormattingRows = False Then
ActiveSheet.Protect Password:="password", AllowFormattingRows:=True
End If

--------------------



 
 


mooresk257;661544 Wrote: 
> 
Hi Folks,
 
I have a sheet that is protected, and have the following code on a
control_click event to allow spell checking. It unprotects the sheet,
runs
the spelling check function, then re-protects the sheet with the
original
password. It also allows row height to be formatted when the sheet is
protected.
 
Private Sub SpellCheck_Click()
 
ActiveSheet.Unprotect Password:="Password"
Cells.CheckSpelling _
CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, _
AlwaysSuggest:=True
ActiveSheet.Protect Password:="Password"
' Allow rows to be formatted (autofit) on a protected worksheet.
If ActiveSheet.Protection.AllowFormattingRows = False Then
ActiveSheet.Protect AllowFormattingRows:=True
End If
End Sub
 
This works fine if there is at least one spelling error. However, if
there
are zero errors on the sheet, then this code re-protects the sheet
without a
password. Any user can run the spell checker to remove the password,
then
just unprotect the sheet.
 
Any suggestions on how to fix this? I'd like to allow users to run
spell
checking and adjust row height, but I want it to stay password
protected too!
 
Thanks!


-- 
Simon Lloyd

Regards,
Simon Lloyd
'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=184741

[url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url]

0
Simon
3/4/2010 9:15:07 PM
Thanks for your help - problem solved!

This was how I re-wrote the code with your suggestions:

Private Sub SpellCheck_Click()

    ActiveSheet.Unprotect Password:="0000"
      Cells.CheckSpelling _
      CustomDictionary:="CUSTOM.DIC", _
       IgnoreUppercase:=False, _
       AlwaysSuggest:=True
' Allow rows to be formatted (autofit) on a protected worksheet.
   If ActiveSheet.Protection.AllowFormattingRows = False Then
       ActiveSheet.Protect Password:="0000", AllowFormattingRows:=True
   End If
    ActiveSheet.Protect Password:="0000"

End Sub

"Simon Lloyd" wrote:

> 
> You also have this:
> 
> 

> VBA Code:
> --------------------
>   
> 
  
> If ActiveSheet.Protection.AllowFormattingRows = False Then
> ActiveSheet.Protect AllowFormattingRows:=True
> End If
> 

> --------------------
> 
> 
> Which is protecting without a password, it should read
>  
> 
> 

> VBA Code:
> --------------------
>   
> 
  
> If ActiveSheet.Protection.AllowFormattingRows = False Then
> ActiveSheet.Protect Password:="password", AllowFormattingRows:=True
> End If
> 

> --------------------
> 
> 
> 
>  
>  
> 
> 
mooresk257;661544 Wrote: 
> > 
> Hi Folks,
>  
> I have a sheet that is protected, and have the following code on a
> control_click event to allow spell checking. It unprotects the sheet,
> runs
> the spelling check function, then re-protects the sheet with the
> original
> password. It also allows row height to be formatted when the sheet is
> protected.
>  
> Private Sub SpellCheck_Click()
>  
> ActiveSheet.Unprotect Password:="Password"
> Cells.CheckSpelling _
> CustomDictionary:="CUSTOM.DIC", _
> IgnoreUppercase:=False, _
> AlwaysSuggest:=True
> ActiveSheet.Protect Password:="Password"
> ' Allow rows to be formatted (autofit) on a protected worksheet.
> If ActiveSheet.Protection.AllowFormattingRows = False Then
> ActiveSheet.Protect AllowFormattingRows:=True
> End If
> End Sub
>  
> This works fine if there is at least one spelling error. However, if
> there
> are zero errors on the sheet, then this code re-protects the sheet
> without a
> password. Any user can run the spell checker to remove the password,
> then
> just unprotect the sheet.
>  
> Any suggestions on how to fix this? I'd like to allow users to run
> spell
> checking and adjust row height, but I want it to stay password
> protected too!
>  
> Thanks!
> 
> 
> -- 
> Simon Lloyd
> 
> Regards,
> Simon Lloyd
> 'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)
> ------------------------------------------------------------------------
> Simon Lloyd's Profile: 1
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=184741
> 
> [url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url]
> 
> .
> 
0
Utf
3/4/2010 10:58:01 PM
Reply:

Similar Artilces:

Opening Excel Opens Too Many Sheets
Don't know what I did recently but when I open Excel, FINANCIAL MANAGER is added to the main menu at the top and several sheets/files are opened that I do not want to open. FINANCIAL MANAGER wasn't there before and I can find no way to take it off the menu. I find no "startup" folder that lists these files that I could delete the shortcuts to - I've looked in the various folders relating to Excel but not found anything that might stop this activity whenever I open Excel. Opening the shortcut I'd been using to get to Excel, it's like a macro starts off and I fin...

Checking for duplication on rows
Hello, In a particular spreadsheet, I would like to verify if the same name is listed on multiple rows. The name is entered in multiple columns on the same row, that is acceptable, but I would like to know if the name is entered on multiple rows regardless of the column. How can I check this? THANKS, karmen -- Karmen ------------------------------------------------------------------------ Karmen's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30972 View this thread: http://www.excelforum.com/showthread.php?threadid=509495 Presume you're checking some...

sheet protection #7
why can't i protect some of my excel sheets? Is the workbook shared? Have you grouped multiple worksheets? jaci wrote: > > why can't i protect some of my excel sheets? -- Dave Peterson ...

How to check continuous form fields
I have a tabbed form, when I switch from one tab to another, it checks to make sure that none of the mandatory fields are missing. However the code that checks the records on tab #1 (which is the code below after the elseif), the code only checks the first record. I can not figure out how to modify the code to get it to look at other records on the form that may be present. Any suggestions on what modification I would need to make here would be helpful. thanks! If TabCtl4 = 1 Then Dim CkCtls As New Collection CkCtls.Add "DRNo" ...

I can't select cells on Excel using the touch pad, and neither the sheet not the workbook is protected
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) SOS folks, <br><br>I am in a pickle! So, as I was working on Excel, something happened because now it doesn't let me select cells with the touchpad, but only using the arrow keys. It's very strange. I went to tools to make sure that neither the sheet nor the book is protected...What can it possibly be? <br> I would be fantastic if somebody could cast some light on this technical glitch. <br><br>All the best, <br><br>Joseph Hail mates, I solved the problem by updating the software...

Check to see if a formula is correct
I want to do something like this Sub Macro1() If Range("A1") =(=B1 + B2) Then ' This is a formula Exit Sub Else Range("C1").Select 'Has correct formula Selection.Copy Range("A1").Select Selection.PasteSpecial Paste:=xlFormulas End If End Sub How do I do this? Hi Try Sub Macro1() If Range("A1").HasFormula Then Exit Sub Else Range("C1").Copy Range("A1").PasteSpecial Paste:=xlFormulas Application.CutCopyMode = False ...

Problem With Deferral Check Links
Hi.. When i Do Deferral Check Links in File Maintenance. I am getting an error message "A remove range operation on table 'PP_File_Maintenance_Error_Log' cannot find the table. On clicking the more Info button. These messages are displayed " [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'TEST.dbo.zDP_PP400004L_1'. [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'TEST.dbo.PP400004'. " In the database these two objects are not present. Can You Pl. tell me what to do about it. Thanks These do not...

Unable to password-protect "VeryHidden" worksheet
Hello I'm using Excel 2007. In VBE I tried to password-protect a "VeryHidden" worksheet by going to VBAProject Properties, Protection, check the box on "Lock project for viewing" and set the password in the boxes as provided. However, each time I re-open the file (after saving and closing the file), the worksheet was not protected. When I check the VBAProject Properties, the check box of "Lock project for viewing" and the "Password to view project properties" are all back to its original state of unchecked/empty. Please advise what steps I...

How do I update links from .xls sheets to .xlms sheets
I have thousands of links to many consolidated spreadsheets created in Excel 2003. Now that I use Excel 2007, how do I convert the links without breaking them. On Apr 21, 2:06=A0pm, June <J...@discussions.microsoft.com> wrote: > I have thousands of links to many consolidated spreadsheets created in Ex= cel > 2003. =A0Now that I use Excel 2007, how do I convert the links without br= eaking > them. After making a copy of your workbook, try to search and replace ".xls" with ".xlms" Hope this helps, Chris M. It's not clear what you'v...

Time sheet issues
There are three questions I am preparing a time shhet. To strart with I have to prepare a blank sheet and give one sheet for each emlpoyee Here are the blank walls I am hitting 1. I want to format a cell (E2 in my time sheet) in such a way that if enter "1" it should display "January" and so on. How to do it? 2. The First cell of last row (A 34 in my of the time sheet) should have 28, 29, 30 or 31 depending the conditions of E2 and H2 which has month and year respectively. 3. The cells B4 to B34 should have days cooresponding to dates (number) in A4 to B34 They sho...

Sheets not getting displayed
When an excel file is opened the sheets are not getting displayed. Using windows XP. Pls help how to overcome this situation Are the sheets hidden? - - try Format, Sheets, Unhide, and unhide any required sheets listed there. Saj Francis Wrote: > When an excel file is opened the sheets are not getting displayed. > Using > windows XP. Pls help how to overcome this situation -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059 View this thread:...

last sheet or go back
I have been trying to figure out how to go back to the last sheet that I was working on. I often navigate my spreadsheets with hyperlinks, but I haven't figured out how to go back to the sheet I was last on. Any help would be great. By the way, what is up with all of these weird posts that we have been inundated with lately? Regards, Shane > By the way, what is up with all of these weird posts that we have been > inundated with lately? Some nutter sends them out now and then - probably trying to get into the top-10 posters lists !! Pete How about a navigation bar from Debr...

Check out http://hackityourself.com
Guys, *Check out http://hackityourself.com* They hack email and myspace accounts for like $50, i used um before. Looks like their price even went down a few bucks. ...

Error occurred while loading sheet 3011111111111111111111111111111
I get this message on one of my XL workbooks - fairly sure it's converted from XL 97. I've read all the related posts which all say that the answer is to open the file in XL 97 and do the fix. All well and good but we no longer have XL 97 - is there any other way to fix the issue and open the file? Thanks. Hi FRM If you can't open the file in 2000-2003 then send me the file and i will change the codename for you. Send the file to me private -- Regards Ron de Bruin http://www.rondebruin.nl "FMR" <fionaross99@hotmail.com> wrote in message news:c5c9f293.04080...

transporting repetitive data from another sheet
How do I transport data from another sheet in a repetitive order? For example: In Column A I would like to transport data from sheet 3, however the data in sheet 3 skips every 4th line (row 4, 8, 12, 16, etc.). I have the formula ='Sheet3'!$E4 The next row should have ='Sheet3'!$E8 etc. When I highlight a series of these cells (all typed correctly and skipping by 4's) and drag the fill tool down (cross hair at bottom right of cell) it does not fill properly. Try =INDIRECT("Sheet3!E"&ROW(A1)*4) Do not alter the A1 - this just gets serial numbers 1,2...

Spell Check in Outlook Express
Running a nDell Dimension 2400. When I spell check e- mail in OE I get,"An error occurred whiole the spelling was being checked.". When I set the option to always spell check before send, I get the message, "The spell check on the document was halted. Do you want to send anyway?" Can someone solve this for me. I will be grateful. This newsgroup is for support of Outlook 97, 98, 2000 & 2002 from the Office family for Windows PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x m...

P/R adjustments after Calculate Checks
Hi, After Transactions>>Payroll>>Calculate Checks, is there an "easy" way to change/adjust the deduction amounts (ie: Federal W/H) prior to printing the checks. We are trying to do a parallel (catch-up) P/R run and changing the amounts prior to posting. Or.. should we post then use the Payroll Manual Check - Adjustment, to change it after posting. Thanks There is no way to update the information after you calculate paychecks unless you remove the build; make the adjustments; and rebuild/calculate. If you needed to determine a garnishment amount, for example, th...

Checking on two values
To count the numbers of rows that include the number "16" in column "I", I use the following formula: ANTALL.HVIS(I:I;"16") (I think this is called COUNT.IF in english) I want to count the numbers of rows that include both this and the letter "A" in column "B". (Meaning not counting rows which just fulfill one of these requirements.) How do I write this formula? (Please feel free to write the english formula, I will find out how to write it in norwegian.) Regards Johannes Hei Johannes COUNTIF takes only one criteria. You can do this wit...

Protecting certain cells
I am using formulas to get averages , standard deviation and other figurers... I want to be able to lock the cell so others cannot change my formula but be able to change certain values to get averages and other information .... I was trying to use the protection under tools to no hope... any help would be appreciated -- In Excel, cells are either locked or unlocked; the default is locked. If a worksheet is not protected, there is no difference in behavior, but if you protect the worksheet, the locked cells are protected, but the unlocked cells can be edited. What you need to d...

How do I unprotect a protected worksheet that has passwrod protect
I need to unprotect a protected sheet but it has password protection on it and I do not have the password, can anybody help? record a macro and paste the text below into it:- Sub PasswordBreaker() Dim i As Integer, j As Integer, k As Integer Dim l As Integer, m As Integer, n As Integer Dim i1 As Integer, i2 As Integer, i3 As Integer Dim i4 As Integer, i5 As Integer, i6 As Integer On Error Resume Next For i = 65 To 66: For j = 65 To 66: For k = 65 To 66 For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66 For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66 For i5 = 65 To 66: F...

Converting a PDF of an Excel sheet back to Excel?
Hi Everyone, I have just been asked by my boss if it is possible to convert a PDF of an excel worksheet back into excel!I have no idea if it is possible, can anyone please help in this regard? Cheers Ash You should be able to copy and paste the values, but you definitely will lose the formulas. On Thu, 23 Sep 2004 17:31:48 -0700, "Ashley" <anonymous@discussions.microsoft.com> wrote: >Hi Everyone, > >I have just been asked by my boss if it is possible to >convert a PDF of an excel worksheet back into excel!I have >no idea if it is possible, can anyone pl...

How can I password protect a folder?
File--> Save As Tools--> General Options "NSharp" <NSharp@discussions.microsoft.com> wrote in message news:F35CB4CC-727D-4E00-B312-5620CE8D2C6F@microsoft.com... > Windows XP, like other sophisticated operating systems, does not provide permissions this way. Permissions are based on users and user groups. In XP Pro, make a new user group and call it something useful ("Sekrit") and assign users to that group. Don't forget to assign yourself! Then set permissions on the resource (folder) only allow members of the Sekrit group read/write or whate...

check register template #2
at the top of the template a range of cells says stationary and if you look at it in print preview it stays on top of all other applications......does anyone know how this works???? how about you JulieD Hi Michele you mean how does row 4 & 5 get repeated on each page that you print out? if so, file / page setup - sheet tab, rows to repeat at top. Cheers JulieD "Michele" <Michele@discussions.microsoft.com> wrote in message news:BAB47D9F-6674-4395-B629-CCCBD067E89D@microsoft.com... > at the top of the template a range of cells says stationary and if you > loo...

I need a four sided program to print on one sheet
I need to make a four sided program, front & back to print on 8.5 x 11 sheet Okay - so what's your question? -- JoAnn Paules MVP Microsoft [Publisher] "debbie hughes" <debbie hughes@discussions.microsoft.com> wrote in message news:B9FA1ECD-6405-4690-BCA3-5AD2FE716683@microsoft.com... >I need to make a four sided program, front & back to print on 8.5 x 11 >sheet debbie hughes wrote: > I need to make a four sided program, > front & back to print on 8.5 x 11 sheet ============================== Choose one of the pre-formatted greeting ca...

Check in vba if html document is already open.
I have a vba macro that opens an html document in IE. I want to be able have the macro check if the document is already open or not; if it is then I want to switch the focus to it rather than open another instance How can I achieve this? It is not sufficient to check if IE is running as the user may have other websites open at the time. TIA Chris ...