Forum Discussion

nic-nicol's avatar
nic-nicol
Copper Contributor
Jan 17, 2021
Solved

Trying to toggle colour of shapes on mouse click

I'm putting shapes over a picture of a human body so you can click an area, say upper left leg, and it's highlighted, click again returns to transparent so you just see the picture below.

 

I've got some VBA code that works for the first shape, but not after that.

I'm inserting the shape, Oval 3 > going to visual basic > insert module > pasting code > changing name of sub to changefilloval3() > changing the object in the code to "Oval 3" > debug > run > back to excel > right click shape > assign macro >  changefilloval3

 

Then it works fine, until I add the next shape and module!!

 

I'm inserting the shape, Oval 4 > going to visual basic > insert module > pasting code > changing name of sub to changefilloval4() > changing the object in the code to "Oval 4" > debug > run > back to excel > right click shape > assign macro >  changefilloval4

 

then neither of them work and it's virtually impossible to click on them!

also when it does work, the size of the shape you can click is tiny, is there any way to make the 'click area' the same size as the shape?

 

am I going about this the wrong way?

should it be one macro for all, not one for each shape? they need to act independently.

am I just getting the coding wrong?

any help greatly appreciated.

here's the code:

 

 

  1. Sub changefill()
  2.  
  3.  
  4. ActiveSheet.Shapes("Oval 1").Select 'change oval 1 to whatever your shape is called
  5. If Selection.ShapeRange.fill.Visible = msoFalse Then
  6. Selection.ShapeRange.fill.Visible = msoTrue
  7. Selection.ShapeRange.fill.Solid
  8. Selection.ShapeRange.fill.ForeColor.SchemeColor = 8
  9. Else
  10.  
  11. Selection.ShapeRange.fill.Visible = msoFalse
  12.  
  13.  
  14.  
  15.  
  16. End If
  17. End Sub

 

 

excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jan 17, 2021

    nic-nicol 

    You need only a single macro that you assign to all the shapes:

     

    Sub ChangeFill()
    With ActiveSheet.Shapes(Application.Caller).Fill
        .Visible = Not .Visible
    End With
End Sub

     

    Please note that when the interior of a shape is transparent, you'll have to click on its edge to run the macro; clicking inside the shape won't work (precisely because it is transparent).

12 Replies

  • Dr_Woodson1964's avatar
    Dr_Woodson1964
    Copper Contributor
    Jan 17, 2021
    I am doing a behaviour data chart and there are 3 behaviours listed in the data section. However, when the graph section appears to show the legend, the legend only shows two of the behaviours.
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 17, 2021

    nic-nicol 

    You need only a single macro that you assign to all the shapes:

     

    Sub ChangeFill()
    With ActiveSheet.Shapes(Application.Caller).Fill
        .Visible = Not .Visible
    End With
End Sub

     

    Please note that when the interior of a shape is transparent, you'll have to click on its edge to run the macro; clicking inside the shape won't work (precisely because it is transparent).

    • nic-nicol's avatar
      nic-nicol
      Copper Contributor
      Jan 17, 2021

      HansVogelaar 

      Wow! Thanks! I'm new to all this (just learning Python as first language and this is my first attempt at VB) and knew there'd be a simple, more elegant solution than my clumsy, long winded one!

      Just one problem: I'm getting a run time error on this line: item with specified name not found 

      With ActiveSheet.Shapes(Application.Caller).Fill

      Presumably I need to insert the sheet or workspace in there somewhere? Tell it where to look?

       

      Pity the 'click area' can't be modified, that might be problematic given the small size of some of the shapes, I'm thinking add an extra shape/button off to the side as an undo/clear all feature? More research!

       

      Thanks again, you've been a great help so far 🙂

       

Resources