Add logic to control brightness of shape/button objects?

[ajjava]

Hello!
See my notated image for a clear look at what I'm working with.
For the shape/buttons noted, each time one is clicked, I'd like the button's brightness to fade to 50%, to indicate that it's the associated ROW FIELD currently in use, in the associated pivot table.
I'd also like all of the buttons NOT in use to return to 100% brightness.

31185

Most of the code in use is from Excel Campus, as noted in the code


Sub Add_Row_Field()
'Author: Jon Acampora, Excel Campus
'Swaps out Row Fields, via a button that has the macro assigned to it
'Remove all Row fields and add the Row field to the pivot table.
'The field is determined by the button text that calls the macro (i.e. name the button with the EXACT name of the Field that it's assigned to)
Dim pt As PivotTable
Dim pf As PivotField
Dim sField As String
For Each pt In ActiveSheet.PivotTables
'Set variables
sField = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text
'Remove existing fields
For Each pf In pt.RowFields
If pf.Name <> "Structure Level 02" Then
' This is the line that makes the Level 2 Row Field stay in place
End If
Next pf
'Add field that button was clicked for
pt.PivotFields(sField).Orientation = xlRowField
pt.PivotFields(sField).Position = 1
Next pt
End Sub



Mr. Acampora also included some logic (below) to achieve what I'm trying to do, but it was in a different procedure.
And I'm just inexperienced enough to not be able to work my way through the required if statements and where they need to go, in order to get this to work.
Each attempt resulted in the button brightness changing in some way...just not the right way.


shp.Fill.ForeColor.Brightness = 0.5
shp.Fill.ForeColor.Brightness = 0


Any help will be much appreciated :)

[Aflatoon]

Try something like this - amend the array of button names to match your actual sheet:


Sub Add_Row_Field()'Author: Jon Acampora, Excel Campus
'Swaps out Row Fields, via a button that has the macro assigned to it
'Remove all Row fields and add the Row field to the pivot table.
'The field is determined by the button text that calls the macro (i.e. name the button with the EXACT name of the Field that it's assigned to)
Dim pt As PivotTable
Dim pf As PivotField
Dim sField As String
Dim buttonNames
buttonNames = Array("Button 1", "Button 2", "Button 3") ' <-- Change these names to match your actual buttons
Dim thisButton As String
thisButton = Application.Caller
For Each pt In ActiveSheet.PivotTables
'Set variables
sField = ActiveSheet.Shapes(thisButton).TextFrame.Characters.Text
'Remove existing fields
For Each pf In pt.RowFields
If pf.Name <> "Structure Level 02" Then
' This is the line that makes the Level 2 Row Field stay in place
End If
Next pf
'Add field that button was clicked for
pt.PivotFields(sField).Orientation = xlRowField
pt.PivotFields(sField).Position = 1
Next pt
' change button brightness
Dim nm
For Each nm In buttonNames
With ActiveSheet.Shapes(Application.Caller)
If nm = thisButton Then
.Fill.ForeColor.Brightness = 0.5
Else
.Fill.ForeColor.Brightness = 0
End If
End With
Next nm
End Sub

[ajjava]

Thank you very much for the assist.

As it turns out, the modification you provided didn't quite get the desired result. I'm unable to explain why, though I really wanted to dissect the issue, for my own education. The sticking point seemed to be getting the UNCLICKED buttons to return back to "normal" brightness.

In any event, I now have the complete code to accomplish what I wanted and I'll share it here, in case it helps others in the future:


Sub Add_Row_Field()
'Author: Jon Acampora, Excel Campus
'Swaps out Row Fields, via a button that has the macro assigned to it
'Remove all Row fields and add the Row field to the pivot table.
'The field is determined by the button text that calls the macro (i.e. name the button with the EXACT name of the Field that it's assigned to)
Dim pt As PivotTable
Dim pf As PivotField
Dim sField As String
Call SetColor(Application.Caller)
For Each pt In ActiveSheet.PivotTables
'Set variables
sField = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text
'Remove existing fields
For Each pf In pt.RowFields
If pf.Name <> "Structure Level 02" Then ' This is the line that makes the Level 2 Row Field stay in place
pf.Orientation = xlHidden
End If
Next pf
'Add field that button was clicked for
pt.PivotFields(sField).Orientation = xlRowField
pt.PivotFields(sField).Position = 1
Next pt
End Sub



Sub SetColor(v)
Dim cell As String
cell = ActiveCell.Address
ActiveSheet.Shapes.Range(Array("Rectangle 23", "Rectangle 22", "Rectangle 21", "Rectangle 13", "Rectangle 10", "Rectangle 1")).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(44, 44, 44)
ActiveSheet.Shapes(v).Fill.ForeColor.RGB = RGB(224, 255, 124)
Range(cell).Activate
End Sub

[namdosan1409]

I add the will-change: transform declaration so that this animation can be hardware-accelerated.