Posts by _HG_

    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


    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


    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

    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