Tumblelog by Soup.io
Newer posts are loading.
You are at the newest post.
Click here to check if anything new just came in.

How To Develop A Bullet List In Excel With VBA

If part of your job involves creating composed stories from Excel info, the incapability to automate bullet details could be a frustration. Normally, you need to have to insert a textual content box and then manually create the bullets.

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

Generating A Bullet Stage From A Data Established

We'll use an instance of a quarterly revenue summary broken down into products and quantity of income. The information may seem like this comma-delimited record.

Merchandise, Sales


Widget2,one hundred thirty

Widget3,a hundred and fifty

and so forth
We need to have to switch the knowledge into the adhering to structure:

Widget1: 100
Widget2: 130
Widget3: a hundred and fifty
We'll change the data by inserting the character for a bullet level just before each value and include the total from the adjacent mobile. First, we will choose the assortment.

dim rng as range

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

for x=two to rng.rows.count
For every single mobile we will insert the bullet at the start off and rewrite the new worth to the cell. The worth for a bullet-level is 149.

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

Occasionally, it can make sense to use a text box to give far more versatility to the design of a report. You can manually add a list to the box by basically highlighting the text, right- clicking and choosing the appropriate alternatives for a bullet listing.

Adding A Record To A Text Box

If you want to automate a checklist in a text box, similar code is employed but you need to select the box and incorporate the textual content in a solitary variable making use of the carriage return character "chr(10)".

for x=two to rng.rows

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

The code can now insert the string as the textual content benefit into the text box.

ActiveSheet.Designs.AddTextbox(1,ileft,itop,iwidth,iheight ).Select
Assortment.Characters.Textual content = strg

The procedure over inserts the same bulleted checklist into the text-box which can be modified by location homes for font kind, colour and measurement. If your spreadsheets are effectively created you can even commence to automate the responses and explanations that go with the data.


The capability to insert a bullet list indicates you can generate properly-developed studies with no utilizing secondary apps like Word or Energy Position. excel vba training london means automation and VBA can be far more efficient and insert efficiency to your function.
Tags: p

Don't be the product, buy the product!