VBA Express : Excel - Add/Remove Bullets-Dashes

Excel

Add/Remove Bullets-Dashes

Ease of Use

Easy

Version tested with

2000

Submitted by:

lucas

Description:

Select one or more cells with text and click the button to call the macro and bullets are added to the text.

Discussion:

This is a real quick way to add bullets to your spreadsheet. I use it in my personal.xls and have a button set up to run it. If you click it once on your selection, you will get a bullet, twice will give you a dash and if you click it three times it will remove all except the original text. Credit for the original code goes to Aaron Blood

Code:

instructions for use

			Code For a module: 
 
Option Explicit 
 
Sub AddBullet() 
     
    Dim Bullet          As String 
    Dim Dash            As String 
    Dim Cel             As Range 
    Dim Str             As String 
    Dim i               As Long 
     
    Bullet = "? " 
    Dash = "   - " 
     'select which cells to perform an action on
    For Each Cel In Selection 
        Str = Cel.Value 
         'If there is already a bullet there then put a dash in its place
        If Left(Str, Len(Bullet)) = Bullet Then 
            Str = Right(Str, Len(Str) - 2) 
            Cel.Value = Dash & Str 
        Else 
             'If there is already a dash there then trim back to normal text
            If Left(Str, Len(Dash)) = Dash Then 
                Str = Trim(Cel.Value) 
                i = Len(Str) - 1 
                If i >= 0 Then 
                    Cel.Value = Trim(Right(Str, i)) 
                End If 
            Else 
                 'Otherwise add the bullet
                Cel.Value = Bullet & Str 
            End If 
        End If 
         'Go to the next cell in the selection and do the same thing
    Next Cel 
     
End Sub

How to use:

Open the Visual Basic Editor by clicking Alt F11 or go to tools-Macro-Visual Basic Editor
On the menu at the top of the page, go to insert then click on module.
Paste the code from above into the module and close the Visual Basic Editor by clicking in the upper right corner on the X or going to File-Close.

Test the code:

Type some text or numbers into a cell or two and then select them. Then run the macro by going to tools-macro-Macro's.
Select the Bullet macro from the list and click Run.
Bullets will be added to your selection.
Run the macro again with the same selection and a dash will replace the bullet
Run the macro a third time and everything returns to normal. That is it returns to your text with no bullet or dash.

Sample File:

Add Bullets.zip 8.28KB

Approved by mdmackillop

This entry has been viewed 206 times.