SOLVED

Trying to toggle colour of shapes on mouse click

%3CLINGO-SUB%20id%3D%22lingo-sub-2067905%22%20slang%3D%22en-US%22%3ETrying%20to%20toggle%20colour%20of%20shapes%20on%20mouse%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2067905%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20putting%20shapes%20over%20a%20picture%20of%20a%20human%20body%20so%20you%20can%20click%20an%20area%2C%20say%20upper%20left%20leg%2C%20and%20it's%20highlighted%2C%20click%20again%20returns%20to%20transparent%20so%20you%20just%20see%20the%20picture%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20got%20some%20VBA%20code%20that%20works%20for%20the%20first%20shape%2C%20but%20not%20after%20that.%3C%2FP%3E%3CP%3EI'm%20inserting%20the%20shape%2C%20Oval%203%20%26gt%3B%20going%20to%20visual%20basic%20%26gt%3B%20insert%20module%20%26gt%3B%20pasting%20code%20%26gt%3B%20changing%20name%20of%20sub%20to%26nbsp%3B%3CSPAN%3Echangefilloval3()%20%26gt%3B%20changing%20the%20object%20in%20the%20code%20to%20%22Oval%203%22%20%26gt%3B%20debug%20%26gt%3B%20run%20%26gt%3B%20back%20to%20excel%20%26gt%3B%20right%20click%20shape%20%26gt%3B%20assign%20macro%20%26gt%3B%26nbsp%3B%26nbsp%3Bchangefilloval3%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThen%20it%20works%20fine%2C%20until%20I%20add%20the%20next%20shape%20and%20module!!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI'm%20inserting%20the%20shape%2C%20Oval%204%20%26gt%3B%20going%20to%20visual%20basic%20%26gt%3B%20insert%20module%20%26gt%3B%20pasting%20code%20%26gt%3B%20changing%20name%20of%20sub%20to%26nbsp%3Bchangefilloval4()%20%26gt%3B%20changing%20the%20object%20in%20the%20code%20to%20%22Oval%204%22%20%26gt%3B%20debug%20%26gt%3B%20run%20%26gt%3B%20back%20to%20excel%20%26gt%3B%20right%20click%20shape%20%26gt%3B%20assign%20macro%20%26gt%3B%26nbsp%3B%26nbsp%3Bchangefilloval4%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Ethen%20neither%20of%20them%20work%20and%20it's%20virtually%20impossible%20to%20click%20on%20them!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Ealso%20when%20it%20does%20work%2C%20the%20size%20of%20the%20shape%20you%20can%20click%20is%20tiny%2C%20is%20there%20any%20way%20to%20make%20the%20'click%20area'%20the%20same%20size%20as%20the%20shape%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Eam%20I%20going%20about%20this%20the%20wrong%20way%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eshould%20it%20be%20one%20macro%20for%20all%2C%20not%20one%20for%20each%20shape%3F%20they%20need%20to%20act%20independently.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eam%20I%20just%20getting%20the%20coding%20wrong%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eany%20help%20greatly%20appreciated.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Ehere's%20the%20code%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3E%3CSPAN%3ESub%20changefill()%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%26nbsp%3B%3C%2FLI%3E%3CLI%3E%26nbsp%3B%3C%2FLI%3E%3CLI%3E%3CSPAN%3EActiveSheet.Shapes(%22Oval%201%22).Select%20'change%20oval%201%20to%20whatever%20your%20shape%20is%20called%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EIf%20Selection.ShapeRange.fill.Visible%20%3D%20msoFalse%20Then%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3ESelection.ShapeRange.fill.Visible%20%3D%20msoTrue%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3ESelection.ShapeRange.fill.Solid%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3ESelection.ShapeRange.fill.ForeColor.SchemeColor%20%3D%208%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EElse%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%26nbsp%3B%3C%2FLI%3E%3CLI%3E%3CSPAN%3ESelection.ShapeRange.fill.Visible%20%3D%20msoFalse%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%26nbsp%3B%3C%2FLI%3E%3CLI%3E%26nbsp%3B%3C%2FLI%3E%3CLI%3E%26nbsp%3B%3C%2FLI%3E%3CLI%3E%26nbsp%3B%3C%2FLI%3E%3CLI%3E%3CSPAN%3EEnd%20If%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EEnd%20Sub%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2067905%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2068348%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20toggle%20colour%20of%20shapes%20on%20mouse%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2068348%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F929301%22%20target%3D%22_blank%22%3E%40nic-nicol%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20need%20only%20a%20single%20macro%20that%20you%20assign%20to%20all%20the%20shapes%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20ChangeFill()%0A%20%20%20%20With%20ActiveSheet.Shapes(Application.Caller).Fill%0A%20%20%20%20%20%20%20%20.Visible%20%3D%20Not%20.Visible%0A%20%20%20%20End%20With%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20note%20that%20when%20the%20interior%20of%20a%20shape%20is%20transparent%2C%20you'll%20have%20to%20click%20on%20its%20edge%20to%20run%20the%20macro%3B%20clicking%20inside%20the%20shape%20won't%20work%20(precisely%20because%20it%20is%20transparent).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2068571%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20toggle%20colour%20of%20shapes%20on%20mouse%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2068571%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F929301%22%20target%3D%22_blank%22%3E%40nic-nicol%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMake%20sure%20that%20each%20of%20your%20shapes%20has%20a%20unique%20name.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2068576%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20toggle%20colour%20of%20shapes%20on%20mouse%20click%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2068576%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Earen't%20they%20assigned%20a%20unique%20name%20when%20created%3F%20Oval%203%2C%20Oval%204%2C%20Trapeziod%205%2C%20etc%3F%3C%2FP%3E%3CP%3EI've%20gone%20into%20formulas%20%26gt%3B%20define%20name%20and%20assigned%20names%20but%20that's%20not%20doing%20anything.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

 

 

12 Replies
Best Response confirmed by nic-nicol (Occasional Contributor)
Solution

@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).

@Hans Vogelaar 

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

 

@nic-nicol 

Make sure that each of your shapes has a unique name.

@Hans Vogelaar 

aren't they assigned a unique name when created? Oval 3, Oval 4, Trapeziod 5, etc?

I've gone into formulas > define name and assigned names but that's not doing anything.

@Hans Vogelaar 

I've got it now!

made a rookie mistake in my haste: hadn't right clicked each shape and assigned it a macro!

That and naming them in the name manager has solved all my problems!

 

simple but very effective bit of code, you're a genius sir! I'm really grateful!

 

Later I'll look at a reset shape and maybe making it grey not black, but that's the next stage!

 

Thanks again!

Have set it up with nice fill colours, outline etc, all working perfectly.

only thing is, you mentioned I can't alter the size/position of the click area for each shape, is it possible to make the click area visible?

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.

@nic-nicol 

The clickable area of a shape is the visible part:

  • If the border is visible, the border is clickable.
  • If the border is transparent, it is not clickable.
  • If the interior is visible, the interior is clickable.
  • If the interior is transparent, it is not clickable.

So what you see is what you get...

@Dr_Woodson1964 

That appears to be an entirely different question. Please start a new question (topic/thread).

Dr_woodson1964@Hans Vogelaar 

 

that was my question

@Dr_Woodson1964 

You have asked an entirely unrelated question in someone else's thread. That is not how a forum should work - it will become very difficult for users searching for help if different users ask wildly different questions in the same thread.

Please go back to https://techcommunity.microsoft.com/t5/excel/bd-p/ExcelGeneral and click 'Start a New Discussion', then post your question there. Thanks in advance!

@Hans Vogelaar 

 

Gotcha!! Solved it by increasing the outline of the shape to make it easier to hit.

Thanks again