How To Generate A Bullet Listing In Excel With VBA

If part of your occupation involves making written stories from Excel info, the incapacity to automate bullet details might be a stress. Generally, you require to insert a textual content box and then manually generate the bullets.

This report points out how you can automate your possess bullet-factors in Excel with VBA.

Creating A Bullet Position From A Info Set

We will use an case in point of a quarterly sales summary damaged down into merchandise and number of sales. excel vba training london may possibly seem like this comma-delimited checklist.

Product, Sales

Widget1,a hundred


Widget3,a hundred and fifty

and so forth
We require to switch the information into the adhering to structure:

Widget1: a hundred
Widget2: one hundred thirty
Widget3: 150
We'll convert the information by inserting the character for a bullet point ahead of each worth and incorporate the whole from the adjacent mobile. Initial, we will choose the assortment.

dim rng as selection

established rng=selection("a1").currentRegion.columns(1)

for x=2 to rng.rows.count
For each and every cell we'll add the bullet at the begin and rewrite the new price to the cell. The benefit for a bullet-point is 149.

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

Often, it can make perception to use a textual content box to give a lot more adaptability to the style of a report. You can manually insert a record to the box by merely highlighting the textual content, correct- clicking and picking the acceptable possibilities for a bullet checklist.

Adding A Checklist To A Textual content Box

If you want to automate a list in a textual content box, comparable code is utilized but you want to decide on the box and incorporate the text in a solitary variable making use of the carriage return character "chr(ten)".

for x=two to rng.rows

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

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

ActiveSheet.Shapes.AddTextbox(1,ileft,itop,iwidth,iheight ).Pick
Assortment.People.Text = strg

The treatment over inserts the same bulleted checklist into the text-box which can be modified by environment homes for font sort, colour and dimensions. If your spreadsheets are effectively created you can even start to automate the responses and explanations that go with the information.


The capability to insert a bullet listing means you can create effectively-made reports without using secondary apps like Word or Electricity Level. It signifies automation and VBA can be more productive and insert productivity to your operate.

Leave a Reply

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