How To Generate A Bullet Record In Excel With VBA

If element of your work includes generating composed stories from Excel data, the lack of ability to automate bullet points could be a stress. Typically, you need to have to insert a text box and then manually produce the bullets.

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

Generating A Bullet Point From A Info Set

We are going to use an illustration of a quarterly revenue summary damaged down into merchandise and number of sales. The information might search like this comma-delimited checklist.

Product, Product sales

Widget1,one hundred

Widget2,one hundred thirty


and so forth
We need to have to flip the information into the subsequent structure:

Widget1: a hundred
Widget2: 130
Widget3: one hundred fifty
We will convert the information by inserting the character for a bullet point before each and every value and include the whole from the adjacent cell. Very first, we'll pick the assortment.

dim rng as assortment

set rng=selection("a1").currentRegion.columns(one)

for x=2 to rng.rows.depend
For each mobile we will add the bullet at the commence and rewrite the new benefit to the cell. The price for a bullet-position is 149.

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

Occasionally, it makes feeling to use a text box to give much more overall flexibility to the style of a report. You can manually include a checklist to the box by merely highlighting the textual content, correct- clicking and choosing the proper alternatives for a bullet checklist.

Adding A List To A Textual content Box

If you need to automate a checklist in a textual content box, comparable code is used but you need to decide on the box and include the text in a one variable utilizing the carriage return character "chr(ten)".

for x=2 to rng.rows

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

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

ActiveSheet.Designs.AddTextbox(one,ileft,itop,iwidth,iheight ).Decide on
Choice.People.Textual content = strg

The process earlier mentioned inserts the very same bulleted checklist into the textual content-box which can be modified by placing qualities for font sort, coloration and measurement. If your spreadsheets are nicely designed you can even begin to automate the remarks and explanations that go with the knowledge.

excel vba training london to insert a bullet listing implies you can generate nicely-designed studies without having employing secondary applications like Word or Electricity Stage. It signifies automation and VBA can be a lot more effective and add productivity to your function.

Leave a Reply

Your email address will not be published. Required fields are marked *