Create shapes with formatting using macro buttons

Hello all

I would like a very simple way to create shapes within multiple sheets with formatting.

Does anyone know of a way that i can very easily insert shapes into a cell on multiple sheets (around 50) - Sheets named 1 - 50.

For Example .. We have a list of buttons with Macro's assigned .. Button 1 creates Oval shapes, button 2 creates squares or rectangles etc

We would then have a second set of buttons which would then apply formatting / styling to those shapes EG: button 1 applies a Red fill to the shapes with a black border, button 2 applies a blue fill to the shapes with a black border and so on.

Would this be possible or does anyone know of a simpler way to achieve this?

Thanks
Dave

buttons may be a bit clunky, I would use a user form

try the one in the attached sheet. When you run the macro, it will show a user form where you can choose the type of shape, its colour, where in the sheet to put it, and which sheets to put it in.

Thanks NickyC

That does exactly what i need it too.

Only 1 or two slight issues .. I have another macro that runs previous too this one which unhides the number of sheets that i want (https://www.excelforum.com/excel-pro...ml#post4603907).

When i run this macro straight after the i get a debug error would this be because i have say around another 40 hidden sheets or so which are inactive and not needed in this instance?

Also i will always want the first sheet in the workbook to be ignored as this is used as the input sheet which the other 50 sheets grab the info from with vlookups and this first page contains the buttons to control the macro's / forms.

But when i run selecting the range say for example create shapes in 2 - 10 a shape is still created on the 1st page (input / control page).

Thanks
Dave

Hi
this version should fix those glitches. It should only put shapes on visible sheets.

Hi NickyC

That fixed the glitch perfectly.

What i've noticed is say when i tell the macro to put the shape in Cell K1 it doesn't seem to do this it gets thrown into X1.

Would you have any ideas what is causing this to happen .. All sheets are on "Page Break Preview" view if this makes a difference.

Thanks
Dave

that's odd
I have run the macro in page layout view and it works fine
the only way I could get that to happen is if columns K to W are hidden

a couple of questions:

• have you modified the code in any way?
• is it only if you select K1, or for other cells too?
• and if for other cells, is there a pattern (eg always 13 columns to the right of the one you enter)?