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
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
Set shpTemp = m_objDrawing.Box(10, 10, 40, 30)
If Not shpTemp Is Nothing Then
shpTemp.Fill.ForeColor.SchemeColor = 12
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.
The other version of the
demonstration requires the UFDraw.xla to be loaded in order for it to
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
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 self contained demonstration
Download the XLA demonstration