Macro

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

msmigraine

Thread Starter
Joined
Feb 10, 2003
Messages
3
:confused:
I have a file (more than happy to forward upon request) which contains multitudes of cells containing bar codes - I need a Macro (I'm assuming) which will enable me to add an asterk (*) to the beginning and to the end of each bar code within each cell - I'm presently hairless after several failed attempts - very new to Excel - can anyone help me?
 
Joined
Apr 4, 2002
Messages
11
Hi,

Open Sheet1 object code module in VBE and copy&paste this code. It will work for A1 cell in Sheet1 so please try writting a value in sheet1 A1 cell. Then you can modify it for any cell you need.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" And Left(Target, 1) <> "*" And Right(Target, 1) <> "*" Then
        Target = "*" & Target & "*"
    End If
End Sub
I hope this helps.

Regards
 

msmigraine

Thread Starter
Joined
Feb 10, 2003
Messages
3
:confused: How do I go about applying the same to a button which will allow me to select multiple cells and then press the button to apply the "asterisk" formatting"? How to make it generic so to speak? Please - and thank you for your assistance!!! My primary problem here is that I have about 1100 cells to modify.
 
Joined
Apr 4, 2002
Messages
11
Hi,

Paste this code into a module and then select all those cells on worksheet (you can use multiply selection) then run this macro by Alt+F8.

Code:
Sub ApplyAll()
Dim mycell
    For Each mycell In Selection.Cells
        If Not mycell.Value = "" Then
            mycell.Value = IIf(Left(mycell.Value, 1) = "*", "", "*") & mycell.Value & IIf(Right(mycell.Value, 1) = "*", "", "*")
        End If
    Next mycell
End Sub
It will skip blank cells.

I hope this helps.
 

msmigraine

Thread Starter
Joined
Feb 10, 2003
Messages
3
Thank you - thank you - thank you! It works great, assigned it to a button and boom instant updates to my data - I envy your abilities.:p
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top