I'm writing a VBA code to change button color before closing the workbook. My code is written under "Microsoft Excel Objects" - "ThisWorkbook". I have two tabs, one is called "User Interface\_OneStep", the other is called "User Interface\_UserSupervised". I want to change button colors on both worksheets when I close the workbook.

The problem I'm having right now is that if I stays in "User Interface\_OnseStep" when I close the workbook, everything works fine. But if I stays in "User Interface\_UserSupervised" worksheet, an error message of "Object doesn't support this property or method" pops up when I try to close the book. My code is below:

```
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ReDim ButtonNumberArray(14) As Variant
ButtonNumberArray(0) = "Rectangle 95"
ButtonNumberArray(1) = "Rectangle 92"
ButtonNumberArray(2) = "Rectangle 98"
ButtonNumberArray(3) = "Rectangle 104"
ButtonNumberArray(4) = "Rectangle 105"
ButtonNumberArray(5) = "Rectangle 106"
ButtonNumberArray(6) = "Rectangle 103"
ButtonNumberArray(7) = "Rectangle 96"
ButtonNumberArray(8) = "Rectangle 114"
ButtonNumberArray(9) = "Rectangle 89"
ButtonNumberArray(10) = "Rectangle 120"
ButtonNumberArray(11) = "Rectangle 123"
ButtonNumberArray(12) = "Rectangle 128"
ButtonNumberArray(13) = "Rectangle 122"
ButtonNumberArray(14) = "Rectangle 137"
For Each var In ButtonNumberArray
    WorksheetName = "User Interface_OneStep"
    Call ResetColorTemplate
Next var
ReDim ButtonNumberArray(18) As Variant
ButtonNumberArray(0) = "Rectangle 84"
ButtonNumberArray(1) = "Rectangle 89"
ButtonNumberArray(2) = "Rectangle 2"
ButtonNumberArray(3) = "Rectangle 12"
ButtonNumberArray(4) = "Rectangle 88"
ButtonNumberArray(5) = "Rectangle 13"
ButtonNumberArray(6) = "Rectangle 14"
ButtonNumberArray(7) = "Rectangle 15"
ButtonNumberArray(8) = "Rectangle 40"
ButtonNumberArray(9) = "Rectangle 16"
ButtonNumberArray(10) = "Rectangle 81"
ButtonNumberArray(11) = "Rectangle 17"
ButtonNumberArray(12) = "Rectangle 57"
ButtonNumberArray(13) = "Rectangle 86"
ButtonNumberArray(14) = "Rectangle 62"
ButtonNumberArray(15) = "Rectangle 65"
ButtonNumberArray(16) = "Rectangle 67"
ButtonNumberArray(17) = "Rectangle 64"
ButtonNumberArray(18) = "Rectangle 74"
For Each var In ButtonNumberArray
    WorksheetName = "User Interface_UserSupervised"
    Call ResetColorTemplate
Next var
End Sub

```

The color template macro is this (written under modules):

```
Option Explicit
Public ButtonNumberArray() As Variant
Public WorksheetName As String

Sub ResetColorTemplate()
ThisWorkbook.Worksheets(WorksheetName).Shapes.Range(ButtonNumberArray()).Select
Selection.ShapeRange.ShapeStyle = msoShapeStylePreset22
With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(0, 0, 0)
    .Transparency = 0
    .Solid
End With
End Sub

```

The error occurs at this line:

```
Selection.ShapeRange.ShapeStyle = msoShapeStylePreset22

```

And when error occurs, the WorksheetName = User Interface\_OneStep. Why when I switch to another tab, Excel can't modify the properties of other tabs?

Thanks in advance!