i think what happened to you is that the line was never reached, so it's not responding to the worksheet_changed event. i rewrote the code a little to handle a couple of exceptions that will be raised:

put the following code
right above the line
and it will delete the values to the left of the a column automatically when you delete a value in the A column.

one last thing. You'll notice below the declarations there's a new sub called "enabel". if you have any problems with the code not responding, place your cursor in that sub and then press play on the toolbar. This will turn event handling back on.

Thx! how similar is vba code to vb code in visual basic? if you know one, do you know the other?

ok, here's the code:


Option Explicit

Dim stText As String
Dim iLength As Integer
Dim iCurrPosition As Integer
Dim stCurrChr As String
Dim lSum As Long
Dim stNums As String
Const a As Integer = 1
Const b As Integer = 2
Const c As Integer = 3
Const d As Integer = 4
Const e As Integer = 5
Const f As Integer = 6
Const g As Integer = 7
Const h As Integer = 8
Const i As Integer = 9
Const j As Integer = 10
Const k As Integer = 11
Const l As Integer = 12
Const m As Integer = 13
Const n As Integer = 14
Const o As Integer = 15
Const p As Integer = 16
Const q As Integer = 17
Const r As Integer = 18
Const s As Integer = 19
Const t As Integer = 20
Const u As Integer = 21
Const v As Integer = 22
Const w As Integer = 23
Const x As Integer = 24
Const y As Integer = 25
Const z As Integer = 26

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Application.EnableEvents = False
stText = Target
iLength = Len(stText)
stNums = ""
lSum = 0
For iCurrPosition = 1 To iLength
stCurrChr = Mid(stText, iCurrPosition, 1)
Select Case stCurrChr
Case "a", "A"
stNums = stNums & a & ", "
lSum = lSum + a
Case "b", "B"
stNums = stNums & b & ", "
lSum = lSum + b
Case "c", "C"
stNums = stNums & c & ", "
lSum = lSum + c
Case "d", "D"
stNums = stNums & d & ", "
lSum = lSum + d
Case "e", "E"
stNums = stNums & e & ", "
lSum = lSum + e
Case "f", "F"
stNums = stNums & f & ", "
lSum = lSum + f
Case "g", "G"
stNums = stNums & g & ", "
lSum = lSum + g
Case "h", "H"
stNums = stNums & h & ", "
lSum = lSum + h
Case "i", "I"
stNums = stNums & i & ", "
lSum = lSum + i
Case "j", "J"
stNums = stNums & j & ", "
lSum = lSum + j
Case "k", "K"
stNums = stNums & k & ", "
lSum = lSum + k
Case "l", "L"
stNums = stNums & l & ", "
lSum = lSum + l
Case "m", "M"
stNums = stNums & m & ", "
lSum = lSum + m
Case "n", "N"
stNums = stNums & n & ", "
lSum = lSum + n
Case "o", "O"
stNums = stNums & o & ", "
lSum = lSum + o
Case "p", "P"
stNums = stNums & p & ", "
lSum = lSum + p
Case "q", "Q"
stNums = stNums & q & ", "
lSum = lSum + q
Case "r", "R"
stNums = stNums & r & ", "
lSum = lSum + r
Case "s", "S"
stNums = stNums & s & ", "
lSum = lSum + s
Case "t", "T"
stNums = stNums & t & ", "
lSum = lSum + t
Case "u", "U"
stNums = stNums & u & ", "
lSum = lSum + u
Case "v", "V"
stNums = stNums & v & ", "
lSum = lSum + v
Case "w", "W"
stNums = stNums & w & ", "
lSum = lSum + w
Case "x", "X"
stNums = stNums & x & ", "
lSum = lSum + x
Case "y", "Y"
stNums = stNums & y & ", "
lSum = lSum + y
Case "z", "Z"
stNums = stNums & z & ", "
lSum = lSum + z
End Select
Next
iLength = Len(stNums)
stNums = Left(stNums, iLength - 2)
Target.Offset(0, 1) = stNums
Target.Offset(0, 2) = lSum
Application.EnableEvents = True
End If
End Sub

Visual Basic has been replaced by VB.Net and no, knowing VBA doesn't mean you can easily write code in VB.Net If you're going to learn programming, I'd recommend C#, it's not as wordy as VB.Net so the code's easier to read. Also, in the code you copied into your workbook, use indentation to make your code easier to read, i.e. indent if blocks, select statements, loops, etc. You can copy the newest code that i put up and then copy your extra case blocks into it if you wish, or you can place the three new if statements i have into your code, just make sure you place them EXACTLY where i have them.

This is awesome! I really appreciate it!!

How's the pay for C# programmers? Any good C# books, websites you can recommend?

Thx again!
Mike

you're quite welcome mike, glad i could be of help. as far as good books/websites, i started with learnvisualstudio.net, which a fee-based site and gave me a decent start with some of the concepts i wasn't familiar with having been only familiar with VBA. (Moving from VBA to C# is like stepping out of the stone-age into a Best-Buy. But VBA is the only language that comes with excel, so i still use it whenever working with spreadsheets) The site covers VB.Net, C#, ASP.Net, gives you the basics of SQL and also covers other stuff i haven't yet checked out. It's a video based tutorial system that focuses on the beginner and for me it was a great start. But if you can't afford his site, try doing a search on "free e-books c# 2010." Wrox has a book that i refer to as well: "Beginning_Visual_CSharp_2010" (there's a forum for that book on this site as well). I've found that sometimes i need to read about a topic from more than one source to wrap my head around it fully.

Once you get a toe-hold on the language, then just do a specific search on what you're trying to accomplish, like "call a method in an MDI child window c# 2010", not "work with MDI forms." For the more general, you'll prolly need to hit the books.

what you'll find is that it may not be so much about the site as the person who answered a particular question, so you might want to hunt around a little for an easier solution. i once was looking to get at the individual pixels in a .jpg file, and came across this one post in which the author gave this convoluted, cryptic, like 75 lines of code, example on how to do it. Then at the bottom someone commented: "Are you kidding me? It's bmp.getPixel() and bmp.setPixel()." (Glad i read to the bottom of that page :) ) It can be the same with books too, even from the same publisher. I have like 8 or 10 Wrox books on various topics, some are duds, others fantastic. Overall I do gravitate towards Wrox books though, and shy away from the dummies series as about 40% of their words are used for really bad jokes. i could chop any dummies book down to a pamphlet without any loss of topic.

You can download Visual Studio Express from microsoft for free, and although it's a pared down version, it gives you the ability to do just about anything you want as far as making software. Be aware though, that if you have an older computer, you're looking at like a 5 hour download/install process and that it's a beast on older computers.

as far as pay, i'm an amateur programmer (as of now anyway), so no idea. in fact, don't take my word on recommending c#. there's tons of languages out there, check out this link to get you started on which you might want to learn

http://www.eweek.com/c/a/IT-Manageme...arn-Right-Now/

i've tried java, javascript, python, vb.net. i chose c# because i was having difficulty finding decent documentation with the other languages. maybe i just didn't look in the right places, but it seems to me a problem with open source software is there is no cohesive, professional system to teach new programmers how to use the software. after getting my start with learnvisualstudio.net, i have yet to type in search terms for what i want to accomplish in c# and not find the question already answered. plus, the IDE rocks.

if you have other questions not specific to VBA, prolly a good idea not to clog up wrox's site, so email me directly mtranchi {at} h o t m a i l . c o m (trying to trick the email hunting bots :) )