Jan 16 2021 06:04 PM
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:
Jan 17 2021 03:21 AM
SolutionYou 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).
Jan 17 2021 06:16 AM
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 :)
Jan 17 2021 06:59 AM
Make sure that each of your shapes has a unique name.
Jan 17 2021 07:19 AM
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.
Jan 17 2021 07:42 AM
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!
Jan 17 2021 11:47 AM
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?
Jan 17 2021 11:52 AM
Jan 17 2021 12:18 PM
The clickable area of a shape is the visible part:
So what you see is what you get...
Jan 17 2021 12:20 PM
That appears to be an entirely different question. Please start a new question (topic/thread).
Jan 17 2021 12:22 PM
Jan 17 2021 12:27 PM - edited Jan 17 2021 12:27 PM
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!
Jan 17 2021 03:42 PM
Gotcha!! Solved it by increasing the outline of the shape to make it easier to hit.
Thanks again :)
Jan 17 2021 03:21 AM
SolutionYou 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).