Runtime 1004 after adding command button to protected sheet

Dachief

New Member
Joined
Oct 2, 2015
Messages
27
Hi there,

I am rather new to excel macro and I am trying to improve an already (quite advanced and proven) excel sheet.
The Excel-sheet has to be protected and should not have any error messages, as it is used by a wide range of users.

I have added a macro that unhides 2+3 columns, then users can play with the data and calculate some changes, and can close and hide the calculation again. The calculations does not affect the "raw" data in any matter. This macro has to work for 6 sheets within the workbook that are all similarly built and draw data from the same pivots.

For 2 of these 6 sheets I receive a "Runtime 1004: Application defined or object defined error" whenever I click on another sheet and back. When I click "end", I can use my macro as desired, but not the macro that has been there (and worked) before.
The bug appears within a code I have not touched, and the cells within that macro are not (directly) affected by the changes of my macro (--> remember, it also works on 4 out of 6 sheets without error).
I assume it has something to do with the sheet protection.


Below you can find the code from one of the sheets. I highlighted the parts I wrote green, and the bug-line red.
In all sheets, the command buttons have the same name.

I hope this helps/describes the problem. let me know if you need more information or if I can improve my posting. Thanks in advance!

Code:
Option Explicit
Private Sub Worksheet_Activate()

'ActiveWindow.DisplayHeadings = False
'Application.DisplayFormulaBar = False

'Trigger BU Selection field
ThisWorkbook.Worksheets("1.3 sBU").Cells(4, 4).Value = ThisWorkbook.Worksheets("1.3 sBU").Cells(4, 4).Value
[COLOR="#008000"]ThisWorkbook.Worksheets("1.3 sBU").Protect UserInterfaceOnly:=True[/COLOR]
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim BU As String
Dim SBU As String
Dim DIV As String
Dim DivAcqRU As String
Dim delimiter As String

Dim x As Integer

    If Target.Address = "$D$4" Then
    
    'Set BU List to all entries
    ThisWorkbook.Worksheets("Pivots").Cells(82, 5).Value = "(All)"
    ThisWorkbook.Worksheets("Pivots").Cells(129, 5).Value = "(All)"
    
    BU = ThisWorkbook.Worksheets("1.3 sBU").Cells(4, 4).Value
    [COLOR="#A52A2A"]ThisWorkbook.Worksheets("1.3 sBU").Cells(8, 4).Value = BU[/COLOR]
    ThisWorkbook.Worksheets("Pivots").Cells(130, 5).Value = BU
    
    delimiter = ", "
    
    'delete possible Div&Acq entries

    ThisWorkbook.Worksheets("1.3 sBU").Cells(33, 6).Value = ""
    
    'Trigger DIV&ACQ field
    ThisWorkbook.Worksheets("1.3 sBU").Cells(6, 4).Value = ThisWorkbook.Worksheets("1.3 sBU").Cells(6, 4).Value
    

    ElseIf Target.Address = "$D$5" Then
       
    SBU = ThisWorkbook.Worksheets("1.3 sBU").Cells(5, 4).Value
    ThisWorkbook.Worksheets("1.3 sBU").Cells(9, 4).Value = SBU
    
    delimiter = ", "
    
    'delete possible Div&Acq entries

    ThisWorkbook.Worksheets("1.3 sBU").Cells(33, 6).Value = ""
    
    'Trigger DIV&ACQ field
    ThisWorkbook.Worksheets("1.3 sBU").Cells(6, 4).Value = ThisWorkbook.Worksheets("1.3 sBU").Cells(6, 4).Value
    
    ElseIf Target.Address = "$D$6" Then
    
     delimiter = ", "
    
    'delete possible Div&Acq entries

    ThisWorkbook.Worksheets("1.3 sBU").Cells(33, 6).Value = ""
    
    DIV = ThisWorkbook.Worksheets("1.3 sBU").Cells(6, 4).Value
    BU = ThisWorkbook.Worksheets("1.3 sBU").Cells(4, 4).Value
    SBU = ThisWorkbook.Worksheets("1.3 sBU").Cells(5, 4).Value
    
        If DIV = "Total Effect" Then
    
        ThisWorkbook.Worksheets("1.3 sBU").Cells(10, 4).Value = "(All)"
    
        ElseIf DIV = "Exclude Div. & Acq." Then
    
        ThisWorkbook.Worksheets("1.3 sBU").Cells(10, 4).Value = DIV
        
        ThisWorkbook.Worksheets("Pivots").Cells(442, 5).Value = "(All)"
        ThisWorkbook.Worksheets("Pivots").Cells(443, 5).Value = BU
        ThisWorkbook.Worksheets("Pivots").Cells(444, 5).Value = SBU
        ThisWorkbook.Worksheets("Pivots").Cells(445, 5).Value = "(All)"
        ThisWorkbook.Worksheets("Pivots").Cells(446, 5).Value = "(All)"
        ThisWorkbook.Worksheets("Pivots").Cells(447, 5).Value = "(All)"
        ThisWorkbook.Worksheets("Pivots").Cells(448, 5).Value = "Div. & Acq."
            
            x = 450
            
            DivAcqRU = ""
            'count div&acq sites
            Do While ThisWorkbook.Worksheets("Pivots").Cells(x, 4).Value <> ""
            
            DivAcqRU = DivAcqRU & ThisWorkbook.Worksheets("Pivots").Cells(x, 4).Value
            
            
            x = x + 1
                If ThisWorkbook.Worksheets("Pivots").Cells(x, 4).Value <> "" Then
                
                DivAcqRU = DivAcqRU & delimiter
                
                End If
                
            Loop
             
            'Show entries
            ThisWorkbook.Worksheets("1.3 sBU").Cells(33, 6).Value = DivAcqRU
            
        ThisWorkbook.Worksheets("Pivots").Cells(442, 5).Value = "(All)"
        ThisWorkbook.Worksheets("Pivots").Cells(443, 5).Value = "(All)"
        ThisWorkbook.Worksheets("Pivots").Cells(444, 5).Value = "(All)"
        ThisWorkbook.Worksheets("Pivots").Cells(445, 5).Value = "(All)"
        ThisWorkbook.Worksheets("Pivots").Cells(446, 5).Value = "(All)"
        ThisWorkbook.Worksheets("Pivots").Cells(447, 5).Value = "(All)"
        ThisWorkbook.Worksheets("Pivots").Cells(448, 5).Value = "Div. & Acq."
        
        
        End If
           
    End If
    
[COLOR="#008000"]End Sub
Private Sub CommandButton21_Click()

ThisWorkbook.Worksheets("1.3 sBU").Unprotect "mypassword"


'To UnHide Column H
Columns("H:I").EntireColumn.Hidden = False
Columns("R:T").EntireColumn.Hidden = False

'Font color grey
Range("E39:g47,J39:Q47,E51:G54,J51:K54").Font.Color = RGB(181, 179, 165)


ThisWorkbook.Worksheets("1.3 sBU").Protect "mypassword"

End Sub

Private Sub CommandButton22_Click()

ThisWorkbook.Worksheets("1.3 sBU").Unprotect "mypassword"

'To Hide Column H
Columns("H:I").EntireColumn.Hidden = True
Columns("R:T").EntireColumn.Hidden = True

'Font color black
Range("E39:g47,J39:Q47,E51:G54,J51:K54").Font.Color = RGB(0, 0, 0)

ThisWorkbook.Worksheets("1.3 sBU").Protect "OEE2015"

End Sub

Private Sub CommandButton23_Click()

Range("H39:H47,H51").Value = 0

End Sub[/COLOR]
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi all,

I have solved the issue with a quite simple solution: Took out all protect/unprotect related codes. Then I protected the sheet with the protect button and checked all checkboxes available. The Macro works perfectly fine.
Maybe anyone has anyways a comment to this, because I cannot quite understand why the issue appeared, and might be helpful in the future.

Restated issue:

I receive a "Runtime 1004: Application defined or object defined error" as soon as I protect the sheets I am working with.
-->The entire code (posted in the thread-opening above) works perfectly fine if I do not protect any sheets.

- The runtime error appears on 3 out of 6 sheets where basically the same code is running. (see code in thread opening)
- The bug appears in a code which was working perfectly fine in a protected version, until I added an additional code to improve the sheets.
- The bug-line refers to a pivot table within the same sheet
- My code intends to unhide a 2+3 columns when clicking a button, and hide the lines again when clicking another button. In order to do this in a protected sheet, I added the commands to password- unprotect and password- protect the sheet again within the code for the buttons (otherwise I get a "runtime 1004: Unable to set hidden property of the range class"-error).
- My code does not directly affect or interfere with the code with the bugline, nor any of the cells (but somehow indirectly).

- Is it possible that Excel is somehow confused with setting the different BU-values in the different sheets, and cannot access those BU values when the other sheet is protected?However, with e.g.
Code:
thisworkbook.worksheets("1.3 sBU)
, it is clearly defined where the macro 'draws its sources' from?!

Thanks for inputs, and hope this helps for others as well
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top