Introduction:
This article gives two methods to fill a multi column listbox on a userform with data from an Excel worksheet.

1. Pulling the worksheet range directly into a listbox
2. Pulling the data into an array first, then placing the array in the listbox

The intention of both of these pieces of code is to be used in a userform. Both are set up in the Userform_Intialize event, although only one or other should be used. An example file with both routines can be downloaded here.

During my course of experimenting with PDFCreator, I ran into a few interesting things. In an effort to help any of you who decide to adapt this to your own use, I thought I'd share those here.

Getting the right download package
All of the code samples provided on the site were tested successfully using PDFCreator 0.9.1, GPLGhostscript.exe download package. Excel versions tested include:

1. Excel 2003
2. Excel 2007

I did, however, encounter some issues when testing using the AFPL version of the download package. Specifically, setting the encryption to 128 bit created a file that was corrupted. Personally, I'd just avoid that version, and stick with the GPL version.

Macro Purpose:
To use PDFCreator to make a PDF that leverages some of their security features. Specifically, we'll create a file that uses:

1. A "File open" password
2. 128 bit encryption
3. Preventing content copying
4. Preventing modification
5. Preventing printing

These code examples are built for PDFCreator, an open source PDF writer utility. Unlike Adobe Acrobat and CutePDF, which both require pro versions to create PDF's via code, PDFCreator is completely free! Download PDF Creator from Sourceforge here. Please note that this code will NOT work with Adobe Acrobat.

Many users mistakenly place their VBA code in the wrong area, which can lead to their code not working, errors and large amounts of frustration.

This page contains a listing of the different types of objects which can hold code, and what code is intended to go in each object.

Types of objects in Microsoft Excel:

As shown by the screenshot of from the Visual Basic Editor (VBE) Project Explorer, the following types of objects can exist in a Microsoft Excel file:

Object Type

1. Worksheet Objects (Sheet1, Sheet2, Sheet3)
2. ThisWorkbook Object (ThisWorkbook)

Macro Purpose:
Restores default yellow colour for all comment on the worksheet. Can easily be modified to change all comments to a different colour by changing the value of the lDefaultCommentColor variable.

Where to place the code:
This code goes in a standard module.

Code required:
[code]Option Explicit

Private Const lDefaultCommentColor As Long = 14811135

Private Sub RestoreCommentColor(wks As Worksheet)
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: To return all comments to the specified color

Dim cl As Range

On Error Resume Next

Macro Purpose:
This code replaces all external links with their values, changes the colour of the background cell (so that you can see where), and places the original link path in a comment. If a comment already existed, it appends the original comment content to the bottom of the comment, and changes the comment colour as well.

Where to place the code:
This code goes in a standard module.

Code required:
[code]Option Explicit

'Place these lines at the top of the module, right under the Option Explicit statement
Private Const lLinkedCommentColor As Long = 65280

Macro Purpose:

1. Retrieve a recordset from a database and place it in an Excel worksheet, using an ADO connection to pass SQL strings.

Examples of where this function shines:

1. Works well for retrieving data from Access (or other database management system) to Excel.
2. Allows working with data in Excel, and making use of its rich features.
3. Code is robust enough to support varying amounts of columns or rows in the recordset.
4. You can supply your own SQL, allowing you to pull back only the data you need or want to work with.

Introduction:
This article contains code examples to print worksheets to PDF files.

These code examples are built for PDFCreator, an open source PDF writer utility. Unlike Adobe Acrobat and CutePDF, which both require pro versions to create PDF's via code, PDFCreator is completely free! Download PDF Creator from Sourceforge here. Please note that this code will NOT work with Adobe Acrobat.

It should also be noted that each of the examples in this section use an Early Bind. If you are not familiar with the difference between Early and Late Binding, please read our article on Early vs Late binding.

Introduction:
This article contains code examples to print worksheets to PDF files, using PDFCreator.

These code examples are built for PDFCreator, an open source PDF writer utility. Unlike Adobe Acrobat and CutePDF, which both require pro versions to create PDF's via code, PDFCreator is completely free! Download PDF Creator from Sourceforge here. Please note that this code will NOT work with Adobe Acrobat.

It should also be noted that each of the examples in this section use a Late Bind. If you are not familiar with the difference between Early and Late Binding, please read our article on Early vs Late binding.

Macro Purpose:

1. Retrieves data from an Access database, and fills it into a userform listbox.

Examples of where this function shines:

1. The Access database is in an Access 2000 format, but the code can be run from Excel 97-2003 with no issues.

Macro Weakness(es):

1. Does not populate listbox with column header names.
2. There is no error handling in this routine.

Versions Tested:
This function has been tested with Access & Excel 97, and Access & Excel 2003, and should also work with Access and Excel 2000 and 2002 (XP) without any modifications