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

_HG_ · Feb 19th 2014

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).

Code

Private Sub Worksheet_Change(ByVal Target As Range)


        
    If Worksheets("MED styrning").Range("Q8") > 200 Then
            CommandButton5.Visible = False
            
    Else
            CommandButton5.Visible = True
    
    End If
        
    
    If Worksheets("MED styrning").Range("D5").Value = ("TCLE") Then
            CommandButton12.Visible = False
                        
            Worksheets("MED styrning").Range("F17").Interior.Color = RGB(255, 255, 255)
            
    Else
            CommandButton12.Visible = True
            
            Worksheets("MED styrning").Range("F17").Interior.Color = RGB(223, 223, 223)
    
    End If
    


    If Worksheets("MED styrning").Range("D19") = ("") Then
            CommandButton1.Visible = False
            
            Worksheets("MED styrning").Range("D19").Interior.Color = RGB(255, 255, 255)
            
    Else
            CommandButton1.Visible = True
            
            Worksheets("MED styrning").Range("D19").Interior.Color = RGB(223, 223, 223)
    
    End If
    
    
End Sub

Display More

It works perfectly well, until I protect it with:

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


With Worksheets("MED styrning")
    .EnableSelection = xlUnlockedCells
    .Protect Contents:=True, UserInterfaceOnly:=True
End With


ActiveSheet.Protect UserInterfaceOnly:=True




End Sub

Display More

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

Code

Worksheets("MED styrning").Range("F17").Interior.Color = RGB(255, 255, 255)
            
            Worksheets("MED styrning").Range("F17").Interior.Color = RGB(223, 223, 223)
            
            Worksheets("MED styrning").Range("D19").Interior.Color = RGB(255, 255, 255)
              
            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

Me.Unprotect Password:="ThePassword"

in the beginning of the code section.

And then:

Code

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

ACARROTADAY · Feb 19th 2014

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

Hi HG

Cold you try and change it to

Code

Private Sub Workbook_open()
    
    With Worksheets("MED styrning")
        .EnableSelection = xlUnlockedCells
        .Protect Contents:=True, UserInterfaceOnly:=True
    End With
     
    ActiveSheet.Protect UserInterfaceOnly:=True
     
     
End Sub

Display More

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

Code

ActiveSheet.Protect UserInterfaceOnly:=True

code line.

_HG_ · Feb 19th 2014

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

Worksheets("MED styrning").Range("F17").Interior.Color = RGB(255, 255, 255) 
 
Worksheets("MED styrning").Range("F17").Interior.Color = RGB(223, 223, 223) 
 
Worksheets("MED styrning").Range("D19").Interior.Color = RGB(255, 255, 255) 
 
Worksheets("MED styrning").Range("D19").Interior.Color = RGB(223, 223, 223)

/ Hans Gatu

**royUK** · Feb 19th 2014

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

Attach your workbook.

Try setting the user options to allow formatting before you protect the sheet

_HG_ · Feb 19th 2014

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

ACARROTADAY · Feb 19th 2014

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:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
     
     
    If Worksheets("MED styrning").Range("Q8") > 200 Then
        CommandButton5.Visible = False
         
    Else
        CommandButton5.Visible = True
         
    End If
     
     
    If Worksheets("MED styrning").Range("D5").Value = ("TCLE") Then
        CommandButton12.Visible = False
         
        Worksheets("MED styrning").Range("F17").Interior.Color = RGB(255, 255, 255)
         
    Else
        CommandButton12.Visible = True
         
        Worksheets("MED styrning").Range("F17").Interior.Color = RGB(223, 223, 223)
         
    End If
     
     
    If Worksheets("MED styrning").Range("D19") = ("") Then
        CommandButton1.Visible = False
         
        Worksheets("MED styrning").Range("D19").Interior.Color = RGB(255, 255, 255)
         
    Else
        CommandButton1.Visible = True
         
        Worksheets("MED styrning").Range("D19").Interior.Color = RGB(223, 223, 223)
         
    End If
 Worksheets ("MED styrning")
        .EnableSelection = xlUnlockedCells
        .Protect Contents:=True, UserInterfaceOnly:=True
    
     
    ActiveSheet.Protect UserInterfaceOnly:=True
     
End Sub

Display More

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.

ACARROTADAY · Feb 19th 2014

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

Good shout roy

Code

AllowFormattingCells:=True

This link might help also.

_HG_ · Feb 19th 2014

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

Now I get another error.
I have attached a screen dump.
It is in Swedish, but if I translate it, I would say that the message is:

Compile error:
Unauthorized use of property.

/ Hans Gatu

_HG_ · Feb 19th 2014

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

**royUK** · Feb 19th 2014

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.

_HG_ · Feb 19th 2014

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

The forum engine reduced the image size, so I placed it at ImageShack.
The error is highlighted.

/ Hans Gatu

**royUK** · Feb 19th 2014

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

Your code should be

Code

With Worksheets("MED styrning")
.EnableSelection 'etc
End With

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

ACARROTADAY · Feb 19th 2014

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.

_HG_ · Feb 19th 2014

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

Quote from royUK;702991
Your code should be
Code
With Worksheets("MED styrning")
.EnableSelection 'etc
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:

Code

Private Sub Workbook_Open()
Application.WindowState = xlMaximized
Worksheets("MED styrning").Activate


With Worksheets("MED styrning")
    .EnableSelection 'etc
End With




End Sub

Display More

But then no CommandButtons worked.

/ Hans Gatu

**royUK** · Feb 19th 2014

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

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

Insteasd of images attach the workbook

_HG_ · Feb 19th 2014

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

_HG_ · Feb 19th 2014

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

**royUK** · Feb 19th 2014

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.

Code

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    With Me
        .EnableSelection = xlUnlockedCells
        .Protect Contents:=True, UserInterfaceOnly:=True
        .Protect Password:="should be here", UserInterfaceOnly:=True
    End With


End Sub

Display More

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

_HG_ · Feb 21st 2014

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

**royUK** · Feb 21st 2014

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

See this example how to use protection with UserInterFace

Example

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

Participate now!

Share