[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 ).SelectSelection.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]
