Categories: News

How To Create A Bullet List In Excel With VBA

[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

techfeatured

Recent Posts

The Power of Documentation: How Recording Pain Strengthens Your Case

When you’re dealing with a workplace injury, the physical pain is only part of the…

7 days ago

“DeFi Is the New Global Economy”: An In-Depth Interview with Super CEO, Alexey Salashny

In the world of decentralized finance, there are many loud names — but very few…

2 weeks ago

Best Tips to Remove Dirt and Stains from Ceramic Coated Cars

Key Takeaways Ceramic coatings offer superior protection but require proper maintenance to keep them effective.…

3 months ago

Thriving in the Electrical Industry: Innovations and Best Practices for Modern Contractors

Table of Contents: Introduction to Modern Electrical Contracting Innovations Transforming the Industry Best Practices for…

3 months ago

Why Document Shredding is Essential for Businesses

Key Takeaways Understand the importance of document shredding for data protection. Explore cost-effective and compliant…

7 months ago

Innovative Strategies for Basement Waterproofing Success

Key Takeaways Basement waterproofing involves multiple strategies for long-term success. Proper drainage and sealing are…

7 months ago