How To Produce A Bullet Record In Excel With VBA

If portion of your occupation involves making prepared stories from Excel information, the inability to automate bullet factors might be a aggravation. Generally, you need to have to insert a text box and then manually develop the bullets.

excel vba training london write-up points out how you can automate your possess bullet-factors in Excel with VBA.

Creating A Bullet Level From A Knowledge Established

We will use an case in point of a quarterly income summary broken down into merchandise and amount of revenue. The info may well seem like this comma-delimited checklist.

Item, Revenue

Widget1,100

Widget2,130

Widget3,150

and so on
We want to flip the info into the subsequent format:

Widget1: a hundred
Widget2: 130
Widget3: a hundred and fifty
We'll change the info by inserting the character for a bullet position ahead of each price and incorporate the whole from the adjacent cell. 1st, we will decide on the assortment.

dim rng as range

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

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

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

Often, it helps make sense to use a textual content box to give more versatility to the design and style of a report. You can manually include a listing to the box by simply highlighting the text, proper- clicking and deciding on the suitable options for a bullet record.

Adding A Listing To A Text Box

If you require to automate a checklist in a textual content box, similar code is employed but you need to select the box and add the textual content in a one variable using the carriage return character "chr(ten)".

for x=two to rng.rows

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

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

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

The method above inserts the exact same bulleted list into the textual content-box which can be modified by environment houses for font variety, shade and dimensions. If your spreadsheets are nicely created you can even start to automate the feedback and explanations that go with the information.

Summary

The potential to insert a bullet listing means you can generate properly-made stories with out employing secondary apps like Word or Power Point. It signifies automation and VBA can be far more successful and insert productiveness to your work.

Leave a Reply

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