Excel

Progressbar XP Style

Greatest place to be

Thanks for visiting my site lucky visitor:

This page was last updated on: March 16, 2008

Need to create an XP style progressbar ? or some other XP control ?

If you're not using Windows XP, go no further, otherwise "This is how you can do it."

To use this technique you need ComCtl32.dll version 6. This control is not redistributable. If you want Excel to use ComCtl32.dll version 6, you must add an application manifest that indicates that version 6 should be used if it is available.

This control IS available on Windows XP. MANIFEST files work only under Windows XP

This technique will allow you to create XP style controls that are available in the

comctl32.ocx i.e.

ListView

Slider

Progressbar

TreeView

TabStrip

Toolbar

Statusbar

For buttons you could use the Toolbar to simulate a button as I did in the example file.

Step 1: Create a new project. Add your controls, just like you would normally. Add your code.

Step 2: If you open Excel it will have the "classic" visual style. That's because Excel is using the old Windows Common controls. How do we change this? With a MANIFEST file.

You can add one without ill effects under other Windows operating systems, but you won't get the new controls if you don't have Windows XP.

What is a MANIFEST file? A MANIFEST file is a text file with the same name as your EXE but with .MANIFEST on the end. For example, the MANIFEST file for "Excel.exe" would be "Excel.exe.MANIFEST". Although for VB it is recommended that you use upper case for the manifest extension I have no issues with it being lowercase.

Step 3: Create a new text file with the same name as your EXE, except with ".MANIFEST" on the end. ".MANIFEST" must be in all caps. For example, for "Excel.exe", you would make a text file named "Excel.exe.MANIFEST".

Step 4: Add the following text to your MANIFEST file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

</dependentAssembly>

</dependency>

</assembly>

Step 5: Add the following code to your project in the startup form:

Private Type InitCommonControlsExType

dwSize As Long '// size of this structure

dwICC As Long '// flags indicating which classes to be initialized

End Type

Private Declare Function InitCommonControlsEx _

Lib "comctl32" ( _

init As InitCommonControlsExType) _

As Boolean

Private Const ICC_PROGRESS_CLASS As Long = &H20

Step 6: Add the following code to the UserForm procedure:

Dim InitCC As InitCommonControlsExType ' identifies the control to register

Dim lret As Long ' generic return value

lRet = InitCommonControlsEx(InitCC)

That's it! You can add controls to your project just like a normal VBA project!

For further info goto the source! see here http://msdn.microsoft.com

Progress bars are useful for displaying the progress of an operation. Microsoft Excel provides a progress bar control. Whilst this is useful, there are times when you need to display it where an ActiveX control cannot be shown eg the Status bar area of the Microsoft Excel application. John Walkenbach has a routine here to do this thanks to David Wiseman, BUT placing this in some other area may take a bit of work. Here is a routine that takes advantage of the comctl32.dll and it's controls that you can access if you know it's class name and how to use it. I have used this for many things eg ImageToXl . Use this in any Window you like, as long as you can get it's handle. Latter on (given time to write it) I will show how to use the SysAnimate32 control to display animation such as AVI files on a userform. I have these files but it is not ready for posting yet.

The comctl32.ocx has these controls that I will show latter, in the mean time here is how to get a progress bar into your status bar area.

The example below places a progress bar on your Userform, the status bar area, your worksheet and the formulabar.

Note: All the code is included to create the Manifest file so you can skip the steps above, the workbook will handle this.

4th Nov 2005: Updated version to Delete the MANIFEST file from your applications path.

Thanks to Brent Boterhoek who informed me of a problem concerning the pallett control on Excel displaying white space. This was due to the XP Tabstrip control taking effect.

Thanks Brent, I suspected as much but didn't look further, Brent did and reported back.

Why Donate?

Download example workbook.................

Created 3 march 2005

Can Do

Ivan F Moala

Visits here

If you're using Windows XP, you know how important it is to have all the necessary laptop parts to run the programs. If you don't have enough memory, you may find that your laptop fan is running constantly or your laptop battery isn't holding up well. Make sure you have all the parts you need to keep XP running.