Changing rectangle shape colour using VBA.

[Phil Payne]

Hello,
i have a worksheet with 6 Rectangles that launch macros.

I am tryng to set them up so the user can tell which rectangle was last activated, i.e. change the activated rectangle button colour to say yellow and the others remain or return to a 'default' colour - grey.

II achieved this when using Command Buttons but as they were calling 'Custom Views' the buttons would disappear for a while along with much of the data. All would be restored to normal but it took some time.

Part of the command button code:-

Private Sub CommandButton1_click()

ActiveWorkbook.CustomViews("FY23_24").Show

ActiveSheet.CommandButton1.BackColor = RGB(255, 255, 0) 'YELLOW
ActiveSheet.CommandButton2.BackColor = &HC0C0C0 'grey
ActiveSheet.CommandButton3.BackColor = &HC0C0C0 'grey
ActiveSheet.CommandButton4.BackColor = &HC0C0C0 'grey
ActiveSheet.CommandButton5.BackColor = &HC0C0C0 'grey
ActiveSheet.CommandButton6.BackColor = &HC0C0C0 'grey
Range("A1").Select
End Sub

The next Rectangles Custom View VBA (FY24_25) would have line 2 Yellow and line one grey.

I have the code written presenting the views but I'm stuck with changing the colours. I'm trying to get this Rectangle to change to yellow but I keep getting syntax errors.

Sub FY23_24()
' FY23_24 Macro
ActiveSheet.Shapes.Range(Array("Rectangle 1")).Select
Selection.OnAction = "FY23_24"
ActiveWorkbook.CustomViews("FY23_24").Show
[B][U]ActiveSheet.Rectangle 1.Color = RGB(255, 255,000)[/U][/B] 'yellow
End Sub

I hope someone can help.

Thanks.

 

[DanteAmor]

If the shape already has the macro assigned, it is not necessary to assign it again every time you click on the shape.

Try the following in each shape.

Add at the end the colorall code.

Sub FY23_24()
' FY23_24 Macro
  ActiveWorkbook.CustomViews("FY23_24").Show
  Call colorall
  ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor.RGB = RGB(255, 255, 0)  'yellow
End Sub

Sub FY23_25()
' FY23_25 Macro
  'here your code
  Call colorall
  ActiveSheet.Shapes("Rectangle 2").Fill.ForeColor.RGB = RGB(255, 255, 0)  'yellow
End Sub

Sub FY23_26()
' FY23_26 Macro
  'here your code
  Call colorall
  ActiveSheet.Shapes("Rectangle 3").Fill.ForeColor.RGB = RGB(255, 255, 0)  'yellow
End Sub

Sub colorall()
  ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor.RGB = RGB(192, 192, 192)  'grey
  ActiveSheet.Shapes("Rectangle 2").Fill.ForeColor.RGB = RGB(192, 192, 192)  'grey
  ActiveSheet.Shapes("Rectangle 3").Fill.ForeColor.RGB = RGB(192, 192, 192)  'grey
  'other shapes
End Sub

 

[Phil Payne]

Thank you very much Dante this helped me to resolve the issue.