Private Sub Worksheet_Change(ByVal Target As Range) issues with Protected Sheet

  • I use Excel 2007 (Swedish version)


    If the value in cell D5 is "TCLE", then some command buttons are supposed to be invisible and their cell color should be (255,255,255).
    If the value in cell D5 is not "TCLE", then some command buttons are supposed to be visible and their cell color should be (223,223,223).






    It works perfectly well, until I protect it with:





    Then I get an error code '1004'
    and it points at one of these lines:


    Code
    1. Worksheets("MED styrning").Range("F17").Interior.Color = RGB(255, 255, 255)
    2. Worksheets("MED styrning").Range("F17").Interior.Color = RGB(223, 223, 223)
    3. Worksheets("MED styrning").Range("D19").Interior.Color = RGB(255, 255, 255)
    4. Worksheets("MED styrning").Range("D19").Interior.Color = RGB(223, 223, 223)



    If I comment out the line, it points at one of the other lines instead.
    If I comment all four lines out, the error messages stop showing up, but then I don't have the desired functionality.




    I have tested to add



    Code
    1. Me.Unprotect Password:="ThePassword"



    in the beginning of the code section.


    And then:



    Code
    1. Me.Protect Password:="ThePassword"



    in the end of the code section.


    It caused the request of filling in the password several times, so it didn't help at all.



    But, why does it complain about the change of color in the cell, just because the sheet is protected ???
    I still have to find a workaround.



    I also have asked the same in the MrExcel.com forum



    / Hans Gatu

  • Re: Private Sub Worksheet_Change(ByVal Target As Range) issues with Protected Sheet


    Hi HG


    Cold you try and change it to




    That way it will protect on opening and the macro will still be able to operate because of your

    Code
    1. ActiveSheet.Protect UserInterfaceOnly:=True

    code line.

  • Re: Private Sub Worksheet_Change(ByVal Target As Range) issues with Protected Sheet


    Hi Acarrotaday !


    I still get the error code '1004'
    and it still points at one of these lines:


    Code
    1. Worksheets("MED styrning").Range("F17").Interior.Color = RGB(255, 255, 255)
    2. Worksheets("MED styrning").Range("F17").Interior.Color = RGB(223, 223, 223)
    3. Worksheets("MED styrning").Range("D19").Interior.Color = RGB(255, 255, 255)
    4. Worksheets("MED styrning").Range("D19").Interior.Color = RGB(223, 223, 223)



    / Hans Gatu

  • Re: Private Sub Worksheet_Change(ByVal Target As Range) issues with Protected Sheet


    Hi royUK !


    Well, the forum rules says that attachements is maximum 205kB.
    My workbook is 491kB.



    / Hans Gatu

  • Re: Private Sub Worksheet_Change(ByVal Target As Range) issues with Protected Sheet


    Could you possibly upload a copy of the workbook as it would make it easier to crack. If you could try making it just one code please:




    I have amended slightly (removed with statement) so please copy and paste it in for me. But if you could upload your workbook it would b appreciated so we can see where it is falling down.

  • Re: Private Sub Worksheet_Change(ByVal Target As Range) issues with Protected Sheet


    Another thing about all this is that the code has worked without error messages.
    I.e. this particular part of the code has worked without error messages, but after editing other parts of the workbook these errors has come up.
    During the years, more functions has been added.
    At first the workbook was made with Excel 2002.
    But it has been working without errors also with Excel 2007.


    / Hans Gatu

  • Re: Private Sub Worksheet_Change(ByVal Target As Range) issues with Protected Sheet


    To figure out which property is erroring can you attach the workbook or post the line of code where the error occurs.

  • Re: Private Sub Worksheet_Change(ByVal Target As Range) issues with Protected Sheet


    Your code should be


    Code
    1. With Worksheets("MED styrning")
    2. .EnableSelection 'etc
    3. End With


    However, you should set up the userinterface protection code when in the WorkBook Open event

  • Re: Private Sub Worksheet_Change(ByVal Target As Range) issues with Protected Sheet


    Removing the With was my suggestion wanted to see the output. I suggested putting as open event, he was still getting the error unfortunately.

  • Re: Private Sub Worksheet_Change(ByVal Target As Range) issues with Protected Sheet


    Quote from royUK;702991

    Your code should be


    Code
    1. With Worksheets("MED styrning")
    2. .EnableSelection 'etc
    3. End With


    However, you should set up the userinterface protection code when in the WorkBook Open event


    I placed the quoted code in the WorkBook Open event:


    But then no CommandButtons worked.


    / Hans Gatu

  • Re: Private Sub Worksheet_Change(ByVal Target As Range) issues with Protected Sheet


    Quote from royUK;702998

    You need to complete the code, I just put 'etc to save my typing


    Insteasd of images attach the workbook


    But the forum rules says that attachements is maximum 205kB.
    My workbook is 491kB.



    / Hans Gatu

  • Re: Private Sub Worksheet_Change(ByVal Target As Range) issues with Protected Sheet


    I have placed the WorkBook on Google Drive.
    Since it is made in the Swedish version of Excel 2007, formulas, etc. differ from the English version.
    The VBA code is in English, though.



    / Hans Gatu

  • Re: Private Sub Worksheet_Change(ByVal Target As Range) issues with Protected Sheet


    1. You have added a workbook open event to the worksheet code module, this cannot be done.


    2.As you are using the worksheet event code you can use Me to refer to the that worksheet


    3.Your worksheet appears to have a password set, you should do that within your code.



    4.You should not need to select or activate the worksheets


    5.You should work through all your code and check it, add Option explicit to your modules.


    6.Set up the protection when the workbook opens usibng the WorkBook_Open event

  • Re: Private Sub Worksheet_Change(ByVal Target As Range) issues with Protected Sheet


    I can not make this work.
    So, I comment those four lines out, as mentioned in #1.
    That is the workaround, for now.


    / Hans