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!
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]