How To Generate A Bullet Listing In Excel With VBA

If part of your work includes producing prepared stories from Excel information, the incapacity to automate bullet factors could be a disappointment. Normally, you want to insert a text box and then manually create the bullets.

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

Creating A Bullet Stage From A Info Established

We'll use an illustration of a quarterly revenue summary broken down into merchandise and variety of income. The info might look like this comma-delimited checklist.

Product, Income


Widget2,a hundred thirty


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

Widget1: a hundred
Widget2: 130
Widget3: a hundred and fifty
We will change the data by inserting the character for a bullet stage ahead of every value and add the whole from the adjacent cell. 1st, we will select the assortment.

dim rng as selection

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

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

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

Occasionally, it makes feeling to use a text box to give much more flexibility to the design of a report. You can manually insert a record to the box by just highlighting the textual content, proper- clicking and selecting the acceptable alternatives for a bullet list.

Adding A List To A Text Box

If you need to automate a list in a textual content box, comparable code is used but you require to choose the box and incorporate 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)

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

ActiveSheet.Styles.AddTextbox(1,ileft,itop,iwidth,iheight ).Choose
Selection.Figures.Textual content = strg

The treatment earlier mentioned inserts the very same bulleted listing into the textual content-box which can be modified by environment qualities for font sort, coloration and dimensions. If your spreadsheets are nicely created you can even begin to automate the feedback and explanations that go with the data.

excel vba training london to insert a bullet list implies you can develop effectively-developed stories with out using secondary programs like Word or Electricity Point. It indicates automation and VBA can be far more efficient and insert efficiency to your function.

Leave a Reply

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