New Excel Forum

This forum has been moved to

Ask all future questions in the New Excel Forum.


Modify Shapes: Top, Left, BackColor, ForeColor

Free Excel Macros

Modify Shapes: Top, Left, BackColor, ForeColor

Postby Sisyphus » Sat Nov 02, 2013 9:34 pm

Modifying Shapes and Form Controls is a tedious job. I have often wanted a tool to help but previous attempts at creating one failed because it is usually faster to work with a make-shift rig that produces the desired effect, albeit without comfort. Now, there is a big job where economies of scale make it worth while producing a proper tool.

The tool in the attached workbook offers the most commonly needed properties for Shapes, Pictures, Form Controls and Active-X Controls for easy modification within a single UserForm: Name, Caption, Top, Left, Height, Width, ForeColor, BackColor and OnAction. You can call up all Shapes, one by one, selecting them from a list, in all worksheets of any loaded workbook instantly, and do an hour's work within five minutes.

The tool in the attached workbook isn't perfect. In fact, it might still contain errors. I shall be grateful if they were pointed out to me when discovered. Please send a Private Message in that case. Note that some ineffective properties are listed, such as a Caption property for ComboBoxes. You can actually set such a property but it will be without effect. Listing such properties enabled me to keep down the effort. An error, in need of correction, would be if any of the items offered for change causes a crash of the program.

For testing please call the procedure 'SetButtonProperties' in the code module 'ShapeMan'. There is a test macro, suitably named 'TestMacro', in the same code sheet which you can use to assign as macro to any of the shapes with an OnAction property.

To install the program drag the userform 'ButtonMan' into your project in VBE's Project Explorer window and copy the procedure 'SetButtonProperties' into any existing standard code module.

Setting of the OnAction property value deserves special mention here because it is a little complex. Basically, the property holds a value consisting of path and macro name. (The macro name may include the module's name, but that isn't required and may be omitted.) Since that is usually quite a rather long string the program only displays the macro name. The existing path name will remain in place unless and until you change the macro name. You can specify the macro including the path or without it. If you omit the path the program will insert the path of the active workbook which you selected in the form itself which is the workbook in which the button resides. If you wish to refer to a macro in another workbook please enter the entire path along with the macro name. To simplify this task you may like to type all of that in a worksheet cell, copy the cell contents to the clipboard and fill the UserForm's textbox by pasting. The display will be a little crowded, but the property will be set correctly.

The attached workbook is all set up for trial. Take a look. This tool will save you a lot of time.
You do not have the required permissions to view the files attached to this post.
Have a great day! :D

I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Former Moderator
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 31 guests