How To Generate A Bullet Listing In Excel With VBA

If excel vba training london of your task requires generating prepared reviews from Excel info, the incapability to automate bullet details may possibly be a disappointment. Typically, you require to insert a textual content box and then manually generate the bullets.

This article explains how you can automate your possess bullet-details in Excel with VBA.

Creating A Bullet Level From A Info Set

We will use an example of a quarterly product sales summary damaged down into merchandise and amount of income. The data may look like this comma-delimited record.

Item, Product sales

Widget1,one hundred

Widget2,a hundred thirty

Widget3,a hundred and fifty

We want to turn the information into the pursuing structure:

Widget1: a hundred
Widget2: 130
Widget3: 150
We'll change the data by inserting the character for a bullet point ahead of every worth and add the total from the adjacent cell. 1st, we'll decide on the variety.

dim rng as selection

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

for x=2 to rng.rows.count
For each and every cell we are going to include the bullet at the start and rewrite the new value to the cell. The price 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 sense to use a text box to give much more versatility to the layout of a report. You can manually add a listing to the box by just highlighting the textual content, correct- clicking and selecting the acceptable options for a bullet list.

Adding A Checklist To A Text Box

If you need to automate a record in a text box, comparable code is employed but you require to pick the box and insert the textual content in a single 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 worth into the text box.

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

The procedure above inserts the identical bulleted listing into the textual content-box which can be modified by environment properties for font type, coloration and size. If your spreadsheets are well developed you can even commence to automate the feedback and explanations that go with the information.


The potential to insert a bullet record implies you can develop properly-designed studies with no employing secondary applications like Phrase or Energy Stage. It signifies automation and VBA can be far more effective and include productivity to your function.
26.07.2017 22:31:16

Maecenas aliquet accumsan

Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos hymenaeos. Etiam dictum tincidunt diam. Aliquam id dolor. Suspendisse sagittis ultrices augue. Maecenas fermentum, sem in pharetra pellentesque, velit turpis volutpat ante, in pharetra metus odio a lectus. Maecenas aliquet
Or visit this link or this one