0

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!

Improve this question
2
  • you need to either Define WorksheetName as global, by decalring it in the module level as Public WorksheetName as String, this way all Subs in the Module will recognize it. Or, you can pass it to the Sub ResetColorTemplate() Commented Dec 1, 2016 at 15:14
  • sorry I did define the variables. Forgot to pasted here. Thanks for reminding. Commented Dec 1, 2016 at 15:22

2 Answers 2

Reset to default
3

The problem is that Excel cannot Select a shape when the Worksheet the shape is on is not active. There are two ways around this. Both involve modifying ResetColorTemplate.

Way One - Select the Sheet

    Sub ResetColorTemplate()

    ThisWorkbook.Worksheets(WorksheetName).Select
  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

Way Two - Work Directly With Objects - Preferred Method

Sub ResetColorTemplate()

Dim ws as Worksheet
Set ws = ThisWorkbook.Worksheets(WorksheetName)

With ws.Shapes.Range(ButtonNumberArray()).ShapeRange
   .ShapeStyle = msoShapeStylePreset22
    With .TextFrame2.TextRange.Font.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 0, 0)
        .Transparency = 0
        .Solid
    End With
End With

End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Scott Holtzman has the right answer.

I just wanted to point out that you should only call ResetColorTemplate once per ButtonNumberArray.

Here you call it once For Each element in ButtonNumberArray

For Each var In ButtonNumberArray WorksheetName = "User Interface_UserSupervised" Call ResetColorTemplate Next var

Also you avoid Global Variables by pass the WorksheetName and ButtonNumberArray as parameters

Sub ResetColorTemplate(WorksheetName As String, ButtonNumberArray As Variant)

Personally, I would also create a function to build the control array.

Private Sub Workbook_BeforeClose()
    ResetColorTemplate "User Interface_OneStep", getControlArray("Isosceles Triangle ", 95, 92, 98, 104, 105, 106, 103, 96, 114, 89, 120, 123, 128, 122, 137)
    ResetColorTemplate "User Interface_UserSupervised", getControlArray("Isosceles Triangle ", 84, 89, 2, 12, 88, 13, 14, 15, 40, 16, 81, 17, 57, 86, 62, 65, 67, 64, 74)

End Sub

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

Function getControlArray(BaseName As String, ParamArray CTRLNumbers())
    Dim x As Long
    For x = 0 To UBound(CTRLNumbers)
        CTRLNumbers(x) = BaseName & CTRLNumbers(x)
    Next
    getControlArray = CTRLNumbers
End Function
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.