[ad_1]

If part of your job involves creating written reports from Excel data, the inability to automate bullet points may be a frustration. Typically, you need to insert a text box and then manually create the bullets.

This article explains how you can automate your own bullet-points in Excel with VBA.

Creating A Bullet Point From A Data Set

We’ll use an example of a quarterly sales summary broken down into products and number of sales. The data might look like this comma-delimited list.

Product, Sales

Widget1,100

Widget2,130

Widget3,150

etc

We need to turn the data into the following format:

• Widget1: 100
• Widget2: 130
• Widget3: 150

We’ll convert the data by inserting the character for a bullet point before each value and add the total from the adjacent cell. First, we’ll select the range.

dim rng as range

set rng=range("a1").currentRegion.columns(1)

for x=2 to rng.rows.count

For each cell we’ll add the bullet at the start and rewrite the new value to the cell. The value for a bullet-point is 149.

 rng.Rows(x) = Chr(149) & " " & rng.Rows(x) & ": " & rng.row(x).offset(0,1)
Next

Sometimes, it makes sense to use a text box to give more flexibility to the design of a report. You can manually add a list to the box by simply highlighting the text, right- clicking and selecting the appropriate options for a bullet list.

Adding A List To A Text Box

If you need to automate a list in a text box, similar code is used but you need to select the box and add the text in a single variable using the carriage return character “chr(10)”.

for x=2 to rng.rows

myStr=myStr & Chr(149) & " " & rng.Rows(x) & chr(10)
Next

The code can now insert the string as the text value into the text box.

 ActiveSheet.Shapes.AddTextbox(1,ileft,itop,iwidth,iheight ).Select
Selection.Characters.Text = strg

The procedure above inserts the same bulleted list into the text-box which can be modified by setting properties for font type, color and size. If your spreadsheets are well designed you can even begin to automate the comments and explanations that go with the data.

Summary

The ability to insert a bullet list means you can create well-designed reports without using secondary applications like Word or Power Point. It means automation and VBA can be more efficient and add productivity to your work.

[ad_2]

Source: http://ezinearticles.com/?How-To-Create-A-Bullet-List-In-Excel-With-VBA&id=7463956