View original (French)

Protecting cells except for VBA code

Solved
lecrol Posted messages 224 Registration date   Status Member Last intervention   -  
Le Pingou Posted messages 12276 Registration date   Status Contributor Last intervention   -
Hello everyone,

My problem is probably "trivial". I've indeed found lots of messages on this same subject about protecting cells with or via VBA. But either it's much more than what I want to do, or it doesn't answer exactly!

A very simple spreadsheet where I want to protect all the cells, except for those in a single column.

That part is super simple: locking the cells and protecting the sheet.

But the problem is that I have some VBA code that displays values or messages on the sheet in various places. So, under VBA, my sheet should not be protected and no cells should be locked.

I've tried using Worksheets(1).Unprotect at the beginning and Worksheets(1).Protect just before the end sub, but I still get a VBA error at the first "Range".

Can anyone shed light on a simple solution without a password?
Thank you very much in advance.
Roland

Configuration: Windows 7 / Chrome 27.0.1453.94

15 answers

  1.
    jiaco79 Posted messages 12 Status Member
     
    Hello Roland,

    I tried something similar to verify the protection and unprotection of sheets during the execution of the main procedure. I created two procedures, one to unprotect and one to protect. I call the first one just before executing the program and then I call the second one after executing the main procedure, all in 3 procedures.

    < Code >

    'Unprotecting from Sheet3 to the second last page of the workbook
    Sub Unprotect_doc()
    Dim I

    Application.ScreenUpdating = False

    On Error Resume Next

    For I = 3 To Sheets.Count - 1

    Sheets(I).Activate
    Range("D10").Select

    ActiveSheet.Unprotect Password:="risks2"

    Next I

    Sheets("Main_Sheet").Select
    Range("A1").Select

    On Error GoTo 0

    Application.ScreenUpdating = True

    End Sub

    < \Code >

    then

    < Code >

    'Protecting from Sheet3 to the second last page of the workbook
    Sub Protect_Doc()
    Dim I

    Application.ScreenUpdating = False

    On Error Resume Next

    For I = 3 To Sheets.Count - 1

    Sheets(I).Activate
    Range("D10").Select

    ActiveSheet.Protect Password:="risks2"

    Next I

    Sheets("Main_Sheet").Select
    Range("A1").Select

    On Error GoTo 0

    Application.ScreenUpdating = True

    End Sub

    < \Code >

    and finally the main procedure

    < Code >

    Sub restriction_opened()

    On Error Resume Next

    Application.ScreenUpdating = False

    Sheets("Main_Sheet").Activate
    Range("BA2:BD23").ClearContents

    Range("BA2").FormulaR1C1 = Application.UserName

    'Lifting the opening restrictions for the names listed below
    Range("BB2").Select
    ActiveCell.Offset(0, 0).Value = "user1"
    ActiveCell.Offset(1, 0).Value = "user2"
    ActiveCell.Offset(2, 0).Value = "user3"
    ActiveCell.Offset(3, 0).Value = "user4"
    ActiveCell.Offset(4, 0).Value = "user5"
    Range("BB2").Activate

    On Error Resume Next

    For Each Cell In Range("name_list")
    If Cell.Value = Application.UserName Then
    Range("BD2").Value = Application.UserName
    End If
    Next

    Application.ScreenUpdating = False

    If Range("BA2").Value = Range("BD2").Value Then

    Sheets("Main_Sheet").Activate
    Range("A1").Select

    Call Unprotect_DocU

    Call CalculateActions_ProcessedNotProcessed '<< Main Procedure !! >>

    Application.ScreenUpdating = True

    Else

    Sheets("Main_Sheet").Activate
    Range("A1").Select

    Call Unprotect_DocU

    Call CalculateActions_ProcessedNotProcessed '<< Main Procedure !! >>

    Call Protect_DocU

    End If

    Sheets("Main_Sheet").Select
    Range("A1").Select

    Application.ScreenUpdating = True

    End Sub

    < \Code >

    Hoping these lines of code have shed some light for you, as far as I'm concerned, this little program manages all entries and applies a read-only status permanently to all users of this file except for 5 users (from 0 to 4).

    Good luck and good courage

    jiaco79
    0
  2. lecrol Posted messages 224 Registration date   Status Member Last intervention   24
     
    Thank you jiaco7ç for addressing my issue

    I read your code and I have several questions

    **** Do I necessarily have to associate a password with protect or unprotect? I was simply writing ActiveSheet.Protect = True (or False)

    After reading your code, another idea comes to mind
    *** Should there be a select before the protect (or unprotect) of the sheet?

    I simply want to protect (or the opposite) the two ranges of my sheet A1:G28 and I1:N28

    But this protection is only to protect my calculation formulas from an unwanted input by the user (my 10-year-old granddaughter)

    I have no other security needs, no password or confidentiality.
    But it's this basic protection that I can't seem to achieve.

    Thank you again
    On my side, I will continue to dig around and try

    Best regards
    Roland
    0
  3. Le Pingou Posted messages 12276 Registration date   Status Contributor Last intervention   1 478
     
    Hello,
    Just passing by, is it possible to see the code of your procedure ...?
    It would be better to understand what you ultimately desire ...!

    --
    Best regards.
    The Penguin
    0
  4. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    you can protect with:
    sheets("Sheet1").Protect UserInterfaceOnly:=True

    Only the user will be blocked, the macros will not need to unprotect the sheet.
    You put it in Workbook_BeforeClose so you are sure not to forget.

    eric

    Never reply to an unsolicited DM...
    Well, that's done.
    0
  6. lecrol
     
    Thank you all,
    Here are the two procedures I have in this folder (yes, I know, I could have made a "for next" loop in the first one; but it's just a calculation game for my little girl.

    The sheet protection is only meant to prevent her from entering data outside the cells H5:H24.

    However, all other cells must be available for VBA and locked for the user. And that's what I can't achieve.

    Thank you again for your enlightened help.
    Sincerely,
    Roland
    ----------------------------------------------------------------------------
    Private Sub CommandButton1_Click()
    Dim img As Shape
    For Each img In Worksheets("Feuil1").Shapes
    If img.Name = "Bonus" Then ActiveSheet.Shapes("Bonus").Delete
    Next img
    '
    For Each img In Worksheets("Feuil1").Shapes
    If img.Name = "Cible" Then ActiveSheet.Shapes("Cible").Delete
    Next img
    '
    Range("F5") = Int((8 * Rnd) + 2)
    Range("F6") = Int((8 * Rnd) + 2)
    Range("F7") = Int((8 * Rnd) + 2)
    Range("F8") = Int((8 * Rnd) + 2)
    Range("F9") = Int((8 * Rnd) + 2)
    Range("F10") = Int((8 * Rnd) + 2)
    Range("F11") = Int((8 * Rnd) + 2)
    Range("F12") = Int((8 * Rnd) + 2)
    Range("F13") = Int((8 * Rnd) + 2)
    Range("F14") = Int((8 * Rnd) + 2)
    Range("F15") = Int((8 * Rnd) + 2)
    Range("F16") = Int((8 * Rnd) + 2)
    Range("F17") = Int((8 * Rnd) + 2)
    Range("F18") = Int((8 * Rnd) + 2)
    Range("F19") = Int((8 * Rnd) + 2)
    Range("F20") = Int((8 * Rnd) + 2)
    Range("F21") = Int((8 * Rnd) + 2)
    Range("F22") = Int((8 * Rnd) + 2)
    Range("F23") = Int((8 * Rnd) + 2)
    Range("F24") = Int((8 * Rnd) + 2)
    '
    Range("g5") = Int((8 * Rnd) + 2)
    Range("g6") = Int((8 * Rnd) + 2)
    Range("g7") = Int((8 * Rnd) + 2)
    Range("g8") = Int((8 * Rnd) + 2)
    Range("g9") = Int((8 * Rnd) + 2)
    Range("g10") = Int((8 * Rnd) + 2)
    Range("g11") = Int((8 * Rnd) + 2)
    Range("g12") = Int((8 * Rnd) + 2)
    Range("g13") = Int((8 * Rnd) + 2)
    Range("g14") = Int((8 * Rnd) + 2)
    Range("g15") = Int((8 * Rnd) + 2)
    Range("g16") = Int((8 * Rnd) + 2)
    Range("g17") = Int((8 * Rnd) + 2)
    Range("g18") = Int((8 * Rnd) + 2)
    Range("g19") = Int((8 * Rnd) + 2)
    Range("g20") = Int((8 * Rnd) + 2)
    Range("g21") = Int((8 * Rnd) + 2)
    Range("g22") = Int((8 * Rnd) + 2)
    Range("g23") = Int((8 * Rnd) + 2)
    Range("g24") = Int((8 * Rnd) + 2)
    '
    Range("h5:h24") = ""
    Range("E5:E24") = ""
    Range("L5:M24") = "" '
    Var = Time() '
    Range("K4") = Var
    End Sub
    ---------------------------------------------------------
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    '
    Dim L As Integer
    Dim img As Shape
    Dim repi As String
    Dim repc As String
    Dim EL As String
    Dim JL As String
    Dim KL As String
    Dim HL As String
    Dim Hlsuit As String
    Dim Ll As String
    Dim ML As String
    '
    '
    For L = 5 To 24
    repi = "c:\Jeu calcul\Im" & CStr(L - 4) & ".jpg"
    repc = "c:\Jeu calcul\Ct" & CStr(L - 4) & ".jpg"
    EL = "E" & CStr(L)
    HL = "H" & CStr(L)
    Hlsuit = "H" & CStr(L + 1)
    KL = "L" & CStr(L)
    JL = "K" & CStr(L)
    Ll = "L" & CStr(L)
    ML = "M" & CStr(L)
    '
    If Range("H5") = "" Then GoTo Fin ' if not played yet
    '
    If Target.Column = 8 And Target.Row = L Then
    If Range(JL) = 0 Then
    For Each img In Worksheets("Feuil1").Shapes
    If img.Name = "cible" Then ActiveSheet.Shapes("Cible").Delete
    Next img
    Range(EL) = Range(HL) 'Reserve the entered number
    Range("N5").Select
    ActiveSheet.Pictures.Insert(repi).Select
    Selection.Name = "Cible"
    If L < 24 Then Range(Hlsuit).Select
    '
    ElseIf Range(JL) = 1 Then
    For Each img In Worksheets("Feuil1").Shapes
    If img.Name = "Bonus" Then ActiveSheet.Shapes("Bonus").Delete
    Next img
    If Range(EL) = "" Then GoTo Exam 'if no previous calculation
    Range(Ll) = "Too late!"
    Range(ML) = 1
    GoTo Suit
    Exam:
    Range(EL) = Range(HL) 'Reserve the entered number
    Range("B5").Select
    ActiveSheet.Pictures.Insert(repc).Select
    Selection.Name = "Bonus"
    If L < 24 Then Range(Hlsuit).Select
    End If
    End If
    '
    Suit:
    Next L
    '
    Fin:
    If Range("H27") = 20 Then
    Range("P20").Select
    ActiveSheet.Pictures.Insert("c:\Jeu calcul\Champion Medaille.jpg").Select
    Selection.Name = "Bonus"
    Range("B20").Select
    ActiveSheet.Pictures.Insert("c:\Jeu calcul\Champion Vainqueur.jpg").Select
    Selection.Name = "Bonus"
    GoTo Sortie
    End If
    '
    If Range("H24") = "" Then GoTo R1
    If Range("H27") > 14 Then
    Range("P20").Select
    ActiveSheet.Pictures.Insert("c:\Jeu calcul\Champion_Coupe.jpg").Select
    Selection.Name = "Bonus"
    GoTo Sortie
    End If
    '
    R1:
    If Range("H24") = "" Then GoTo R2
    If Range("H27") > 9 Then
    Range("P20").Select
    ActiveSheet.Pictures.Insert("c:\Jeu calcul\Moyen.jpg").Select
    Selection.Name = "Bonus"
    GoTo Sortie
    End If
    '
    R2:
    If Range("H24") = "" Then GoTo Sortie
    If Range("H27") < 9 And Range("H27") >= 0 Then
    Range("p20").Select
    ActiveSheet.Pictures.Insert("c:\Jeu calcul\Non.jpg").Select
    Selection.Name = "Bonus"
    Range("B20").Select
    ActiveSheet.Pictures.Insert("c:\Jeu calcul\Non_2.jpg").Select
    Selection.Name = "Bonus"
    End If
    Sortie:
    '
    End Sub
    ---------------------------------------------
    0
    1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Have you read my post 4?
      0
    2. lecrol Posted messages 224 Registration date   Status Member Last intervention   24
       
      Hello,

      Yes Eriiic, I read it and I immediately tried it but it gives me a compile error. Here’s what I wrote in the workbook:

      Private Sub Workbook_BeforeClose()
      Sheets("Feuil1").Protect UserInterfaceOnly:=True
      End Sub

      And it gives me a compile error with: "The procedure declaration does not match the description of the event or procedure of the same name".
      Where is my error?

      Thanks to you
      Roland

      PS On my sheet, I lock all the cells except for the only ones where you can enter data H5:H24.
      But I do not activate protection since it’s VBA that does it. Is it correct?
      0
    3. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Hello,

      It's a workbook event, so it should be placed in the thisWorkbook module.
      However, upon reflection, it's better to put it on opening, as it avoids re-saving the workbook if you just did it. 
      Private Sub Workbook_Open() Sheets("Sheet1").Protect UserInterfaceOnly:=True End Sub

      eric
      0
  7. lecrol Posted messages 224 Registration date   Status Member Last intervention   24
     
    Eric, you are very nice to help me.

    I placed this code after removing the other one, and I no longer have a compilation error.

    But don't I need to put a "false" somewhere and a "True" somewhere else?

    I imagined unprotecting at the beginning of the VBA procedure and re-protecting at the end of the procedure.
    With the code as I placed it (at the opening of the folder, the protection is disabled and therefore I don't have the desired result.

    Thank you for your patience.
    Best regards,
    Roland
    0
    1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      But do I need to put a "=false" somewhere and a "=True" somewhere else?
      no
      UserInterfaceOnly: Optional Variant argument. This argument is set to True to protect the user interface but not the macros. If this argument is not specified, protection applies to both macros and the user interface.

      You close your workbook.
      Upon opening, Sheet1 will be protected for the user but not for the macros that write to it.
      Re-test it this way and let me know...

      eric
      0
  8. lecrol Posted messages 224 Registration date   Status Member Last intervention   24
     
    I followed your instructions

    1- On the sheet, I activate the protection
    2- In the Workbooks, I placed the following code!
    Private Sub Workbook_Open()
    Sheets("Feuil1").Protect UserInterfaceOnly:=True
    End Sub

    I close my workbook and re-open it... and I have an error on this line:

    For Each img In Worksheets("Feuil1").Shapes
    If img.Name = "Bonus" Then ActiveSheet.Shapes("Bonus").Delete
    Next img

    with the message: Error 1004 Application defined or object defined error

    As indicated by my code (which I published completely in a previous post), my VBA also places and deletes images on the sheet

    Could there be an "allergy" between these "shapes" operations and the UserInterfaceOnly code I added on Workbooks? Probably, because without this code I have no error (but also no protection)

    Thank you again in advance
    Roland
    0
    1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      It's hard to respond without a file and I don't have the courage or time to build one that tries to resemble yours...
      But we can assume indeed that you did things correctly and that the shapes are insensitive to UserInterfaceOnly:=True
      Just in case, don't protect yourself and let the macro do its thing, but I don't really believe there will be a difference.
      It wouldn't be the first time a feature isn't correct only in part.
      If that's the case, you'll need more lines: removing and reapplying the protection as previously suggested. Sorry in that case to have wasted your time.
      When I have 5 minutes, I will test it because it would be interesting to confirm.

      eric
      0
    2. lecrol
       
      But no, you did not waste my time, Eric!
      So, really don't be sorry! You are really nice, you and all the others on this forum, for sharing your knowledge and expertise like this!

      Thanks again and a big hats off. This site is truly of national importance. I wish I could give even just a small part of what I find here, believe me!

      It is indeed the shapes that are causing me problems by not allowing themselves to be "unprotected" by the VBA "unprotection" code.

      But I thank you again for your kindness and patience.
      Best regards
      Roland
      0
    3. Le Pingou Posted messages 12276 Registration date   Status Contributor Last intervention   1 478
       
      Hello,
      Okay, I'm leaving the position.
      Best wishes.
      Regards.
      The Penguin
      0
    4. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      I just tested it, and indeed the object protection remains.

      So without a password, at the beginning of each sub that needs it:
      Sheets("Sheet1").Unprotect
      restore at the end of each sub:
      Sheets("Sheet1").Protect

      If you have multiple sheets or subs, you can group them into 2 subs that you call wherever necessary.
      If you look at the help on Protect, you'll see that you can increase the user's rights on certain actions.

      eric
      0
  9. Le Pingou Posted messages 12276 Registration date   Status Contributor Last intervention   1 478
     
    Hello everyone,
    I don't know if eriiic (greetings) is looking for a solution, personally without seeing the configuration of the file it's almost impossible to pinpoint the problem.
    Moreover, this instruction is found in both procedures and you do not specify the exact location ..... nothing to do .... !

    Best regards.
    Le Pingou
    0
    1. lecrol
       
      Thank you, the Penguin, for giving me some of your time

      My "file" here is a blank sheet. Simply put
      1- My VBA "sheet" procedure (on command button) fills it with random numbers in columns F5:G25. It also clears the contents of other cells modified by calculation.

      2- The user, a 10-year-old child, enters the product of the numbers in each of the 20 rows in column H5:H25

      3- My other procedure (in "workbooks") captures each entered result. And, depending on the case (1 = correct, 0 = false), displays images named "bonus" or "target".

      And the VBA code clears these images whenever necessary, so they don't stack up on the sheet;

      That's why I wanted:
      For the VBA code to handle everything, images and cells, without protection
      So that, on the contrary, the user can access only the cells H5:H25

      There you go. But if you want, I will gladly send you the complete folder.
      Sincerely,
      Roland
      0
  10. Le Pingou Posted messages 12276 Registration date   Status Contributor Last intervention   1 478
     
    Hello,
    Thank you for the information, it corresponds to what I partially discovered in your code.
    However, as pointed out by eriiic and myself, without the file and these [Shapes] it's impossible to do more, sorry.

    --
    Regards.
    The Penguin
    0
    1. lecrol
       
      Hello,

      But what do you want me to send you as additional details?

      1- These "shapes" are totally ordinary .Jpg images that I name "target" or "bonus" when loading them (as the VBA code indicates to you)
      It is according to the code 0 or 1, "false" or "true," that VBA reads on the Excel sheet

      2- There is no folder
      The sheet ("Sheet1") is a sheet filled initially by VBA, from a command button

      My only problem now is placing any images (or deleting them) on any Excel sheet that is protected for the user.

      Let me know if I can tell you more to better understand my problem.
      Eric kindly helped me with the VBA unprotection for all cell-type operations (Range or Select, for example).
      But apparently, this would be incompatible with instructions of the type shape().delete

      Thank you again
      Best regards
      Roland
      0
  11. Le Pingou Posted messages 12276 Registration date   Status Contributor Last intervention   1 478
     
    Hello,
    That will be all for me as eriiic has proposed a good solution (post 19) that I support because I also tested it on a fictional file and it works.
    Have a good continuation.

    --
    Regards.
    The Penguin
    0
  12. lecrol
     
    And too bad if it doesn't work in Excel 2003 and exactly with the code I published here

    But thanks anyway!
    Roland
    0
    1. Le Pingou Posted messages 12276 Registration date   Status Contributor Last intervention   1 478
       
      Hello,
      Well, with version 2003 I can't test it, so I advise you to try with eriiic's suggestion (post 19), that would be a minimum... No...!
      Regards.
      The Penguin
      0
  13. Le Pingou Posted messages 12276 Registration date   Status Contributor Last intervention   1 478
     
    Hello,
    I just retested a file built according to your code including image introduction (jpg) and it works without any problem and without using eriiic's suggestion (post 19).
    No error message from [Shapes].

    --
    Best regards.
    The Penguin
    0
  14. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    I am reposting my proposal which seems to be too lost in the thread:

    I just tested, indeed the protection of the objects remains.

    So without a password, at the beginning of each sub that needs it:
    Sheets("Feuil1").Unprotect
    restore at the end of each sub:
    Sheets("Feuil1").Protect

    If you have multiple sheets or subs, you can group them into 2 subs that you call wherever necessary.
    If you look at the help on Protect, you will see that you can increase the user's rights on certain actions.

    eric

    --
    Never reply to an unsolicited private message...
    Well, that’s done.
    0
  15. lecrol Posted messages 224 Registration date   Status Member Last intervention   24
     
    Allelujah! Thank you, Eric, for your patience and kindness.

    I just checked that everything was working fine indeed.

    My mistake, very likely, was that I wasn't keeping the previous code:
    Private Sub Workbook_Open()
    Sheets("Feuil1").Protect UserInterfaceOnly:=True
    End Sub

    by using the code Sheets("Feuil1").Unprotect and .Protect.

    I (foolishly) thought that the second one replaced the first, making it unnecessary.

    Now, I placed both codes, the first in workbooks and the second at the beginning and end of my two procedures.

    And everything is working well!

    Once again, thank you. Thanks to you and Le Pingou. I'm sorry for not explaining myself well enough and for thus taking your time.

    But now, I will finally be able to teach my 12-year-old granddaughter her multiplication tables (her exam is still far away).
    Thank you both again and well done for this forum!
    Roland
    0
    1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Hello,

      I thought (foolishly) that the second would take the place of the first, rendering it useless.
      Yes, yes, you understood correctly, it was planned that way.
      You can remove this part, or leave it if you fear forgetting to protect one day.

      eric
      0
  16. Le Pingou Posted messages 12276 Registration date   Status Contributor Last intervention   1 478
     
    Hello lecrol,
    Happy for you.
    Just by the way, your 12-year-old daughter is going to get her diploma much faster than you think if in 2 days she takes 2 years.....(my little girl is 10 years old / post 3)... !
    Have a good week.

    --
    Best regards.
    Le Pingou
    0