Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Nov 2006
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Assigning Macro to a Shape (Excel VBA 2003)

    I would like to assign a macro to a shape in Excel VBA. If the user clicks on the shape a macro will run to change the colour of that shape. For one shape I can simply use the shapes collection and refer to the shape by name eg

    ActiveSheet.Shapes("Oval 1").Select
    If Selection.ShapeRange.Fill.ForeColor.SchemeColor = 1 Then
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 2
    ElseIf Selection.ShapeRange.Fill.ForeColor.SchemeColor = 2 Then
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 3
    End if


    However, what is the best way of managing a situation where there are say forty similar shapes and where each shape automatically changes colour in if clicked on? I could write forty macros referring to each of the 40 shapes (oval 1 to oval 40). However is there any way of identifying the name of the shape that has been clicked and therefore use one macro rather than 40 ?

    Also,

    The shape does not have a name property in the same way a command button does!
    I could use a command button which would be easier, but is there any way that you can change the shape of a command button say from a rectangle to a circle?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Assigning Macro to a Shape (Excel VBA 2003)

    You can assign a single macro to all shapes, and use Application.Caller to retrieve the name of the shape that has been clicked:

    Sub ShapeClick()
    With ActiveSheet.Shapes(Application.Caller).Fill.ForeCo lor
    Select Case .SchemeColor
    Case 1
    .SchemeColor = 2
    Case 2
    .SchemeColor = 3
    Case Else
    ' Optional
    .SchemeColor = 1
    End Select
    End With
    End Sub

  3. #3
    New Lounger
    Join Date
    Nov 2006
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assigning Macro to a Shape (Excel VBA 2003)

    This is great thanks

Posting Permissions

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