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

Why Document Shredding is Essential for Businesses

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

1 week ago

Innovative Strategies for Basement Waterproofing Success

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

1 week ago

Revolutionizing Food Display Solutions: Innovative Approaches for the Modern Era

Table of Contents Understanding the Basics of Food Display Latest Trends in Food Display Solutions…

1 week ago

How Technology is Reshaping Healthcare: A Journey Towards Better Patient Care

Key Takeaways Technology is rapidly transforming the healthcare industry. Benefits include improved patient outcomes, increased…

2 weeks ago

Innovative Strategies for Efficient Log Book Management in Trucking

Key Takeaways Understand the importance of efficient log book management for truck drivers. Explore the…

4 weeks ago

Best Crypto to Buy Now: Cryptocurrencies with the Most Potential in 2025

2025 is shaping up to be a monumental year for the cryptocurrency market. With Bitcoin…

4 weeks ago