AJP Excel InformationAJP Excel Information
ChartsVBAFun stuffForumsTipsLinksBooksWhat's newContactAboutSearchRSS Feed

 
 
 

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
 
 
   

Last updated 28th April 2007

 
 
  Home | Charts | VBA Code | Fun Stuff
Forum Examples | Tips | Links | What's New | Book List
Contact | About
Microsoft® and Microsoft® Excel are registered trademarks of the Microsoft Corporation.
andypope.info is not associated with Microsoft. Copyright ©2007-2012 Andy Pope