Results 1 to 7 of 7

Thread: Macro Buttons change colour when pressed
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2016
    Location
    Perth WA
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro Buttons change colour when pressed

    Hi,
    Looking for a solution to my Macro button (shape) problem.
    I have the below code which will colour a depressed button.
    Problem is this will only work for 1 button and i have 12 that i want it to work for.
    The idea would be that the button stays depressed until another button is selected.

    Private Sub Worksheet_Change(ByVal Target As Range)
    'Updateby Extendoffice 20160704
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) Then
    If Target.Value = 1 Then
    ActiveSheet.Shapes("Rounded Rectangle 4").Fill.ForeColor.RGB = vbRed
    ElseIf Target.Value < 1 And Target.Value > 1 Then
    ActiveSheet.Shapes("Rounded Rectangle 4").Fill.ForeColor.RGB = vbYellow
    Else
    ActiveSheet.Shapes("Rounded Rectangle 4").Fill.ForeColor.RGB = vbBlue
    End If
    End If
    End Sub

  2. #2
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Macro Buttons change colour when pressed

    Try code based on below:-
    Add code for with extra Shapes as required
    NB:-To add code Right click shape and select "Assign Macro"

    Code:
    Sub colshp()
    Dim shp As Shape
     For Each shp In ActiveSheet.Shapes
      If shp.Type = 1 Then 'msoShapeRoundedRectangle Then
        If Application.Caller = shp.Name Then
            shp.Fill.ForeColor.SchemeColor = 3
         Else
            shp.Fill.ForeColor.SchemeColor = 4
        End If
     End If
    Next
    End Sub
    
    Sub RoundedRectangle1_Click()
    colshp
    End Sub
    
    Sub RoundedRectangle2_Click()
    colshp
    End Sub
    
    Sub RoundedRectangle3_Click()
    colshp
    End Sub
    
    Sub RoundedRectangle4_Click()
    colshp
    End Sub
    Regards Mick
    Last edited by MickG; Aug 29th, 2019 at 09:35 AM.

  3. #3
    New Member
    Join Date
    Nov 2016
    Location
    Perth WA
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro Buttons change colour when pressed

    Works perfect, THANYOU

  4. #4
    New Member
    Join Date
    Nov 2016
    Location
    Perth WA
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro Buttons change colour when pressed

    So although the above code works perfectly, is there a way of making it work for sets or groups of buttons?
    eg i have 3 seperate groups of buttons that will have 1 button in each group highlighted which will
    identify the data that is shown.

  5. #5
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Macro Buttons change colour when pressed

    Try something based on below:-
    NB:- The code show 2 sets of 3 shapes !!
    NB:- I found it is better to do a "Assign Macro" for each shape before you right Click and "Group" them.
    Sometimes with these shapes you get a "Cannot find "Macro" or similar problems.
    Code:
    Sub RoundedRectangle9_Click()
    GetCol
    End Sub
    
    Sub RoundedRectangle10_Click()
    GetCol
    End Sub
    
    Sub RoundedRectangle8_Click()
    GetCol
    End Sub
    
    Sub RoundedRectangle25_Click()
    GetCol
    End Sub
    
    Sub RoundedRectangle26_Click()
    GetCol
    End Sub
    
    Sub RoundedRectangle27_Click()
    GetCol
    End Sub
    
    Sub GetCol()
    Dim Shp As Object
    Dim pShp As Object
    Set pShp = ActiveSheet.Shapes(Application.Caller).ParentGroup
     For Each Shp In pShp.GroupItems
      If Shp.Type = 1 Then
        If Application.Caller = Shp.Name Then
            Shp.Fill.ForeColor.SchemeColor = 3
         Else
            Shp.Fill.ForeColor.SchemeColor = 4
        End If
     End If
    Next
    End Sub
    Regards Mick
    Last edited by MickG; Aug 30th, 2019 at 09:14 AM.

  6. #6
    New Member
    Join Date
    Nov 2016
    Location
    Perth WA
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro Buttons change colour when pressed

    Yep that works awesome!, Thanks Mick

  7. #7
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Macro Buttons change colour when pressed

    You're welcome

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •