AJP Excel Information AJP Excel Information

Drawing on userform

(Excel 2000 and above)

The question of drawing lines and shapes on a userform comes up quite often. 
The normal approach for straight horizontal or vertical lines is to use the border of a label control and set its height or width to 1. 
When it comes to anything more fancy then the approach is to create the drawing on the worksheet using the built-in auto-shapes and then at design time manual copy and paste it to an image control's picture property. 
Or for the brave you could go down the API route.
I have taken the auto-shape route. The main differences being that shapes can be added at run time and multiple shapes can be handled.
Shapes can be created and formatted as required before they are painted to the userform. 
The example above is a very gaudy example of what is possible.
Here is the syntax used to create the blue box
Set shpTemp = m_objDrawing.Box(10, 10, 40, 30)
If Not shpTemp Is Nothing Then 
    shpTemp.Fill.ForeColor.SchemeColor = 12
End If
As you can see it is very similar to normal VBA code used when adding and formatting auto-shapes.
I have included two versions of the same demonstration for down loading. 
One version contains all the modules and class code within a project. It makes seeing the demonstration easier but requires extra work sharing the code if used on other projects. 
Download the Self contained demonstration 
The other version of the demonstration requires the UFDraw.xla to be loaded in order for it to work. 
If you are using Excel 2000 then the code will automatically try and locate and load the file. Failing that it will give you the choice to locate the file manually.
If you are using versions xl2002 or xl2003 then you will need to adjust your security setting to allow the code to automatically set vbproject references.

This message box will be displayed if you have not enabled 'Trust access to Visual Basic Projects'

The is the dialog needed to change the setting.

If you load the xla manually then you can run the Demo macro rather than the DemoUserformDrawing macro, which is linked to the button on the worksheet.
Download the XLA demonstration

Download the self contained demonstration

Download the XLA demonstration

Created August 2004
Last updated 5th August 2014 

Return to main page Chart Section VBA section Fun and games section Forum files Tips section Links section Book section Site information Site Search RSS feed Top of page

Microsoft® and Microsoft® Excel are registered trademarks of the Microsoft Corporation.
andypope.info is not associated with Microsoft. Copyright ©2007-2016 Andy Pope