Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code won't execute...
D,
I should have guessed what was going on. Your code runs ok for me after cleaning up the extra characters. It is Event code, in that it runs whenever there is any change to cell content on the worksheet - look at the title of the sub. I think that what you believe is "quitting" is simply the code reacting to the cell change event. In other words, the cell is changed, your code runs and changes the cell which cause the code to run again. The module level variable "inmacro" at the top of the module causes the cycle to abort, but not before the code jumps to the top and tries to start over. You may prefer the following modification which uses a code line to turn off the event trigger. It must be turned back on before exiting the sub or it remains and all events in the entire Excel application will not work Note that "Exit Sub is added just before the error handler. Jim Cone San Francisco, USA '------------------------ Option Explicit Private Sub worksheet_change(ByVal target As Range) On Error GoTo getout Dim unit As Excel.Range Application.EnableEvents = False If target.Column = 3 And target.Row <= 1000 Then Set unit = Range(target.Address) 'first part takes care of items deleted, if a unit is deleted then 'it deletes the rest of the row If unit = "" Then unit.Offset(0, -1) = "" unit.Offset(0, 1) = "" unit.Offset(0, 2) = "" unit.Offset(0, 3) = "" Else If unit.Offset(0, -1) = "" Then unit.Offset(0, -1) = 1 'unit is a remove unit, so strip off "i" and look up the unit If Left(unit, 1) = Worksheets("\remove price\").Cells(2, 4) Then unit.Offset(0, 1) = "long equation" Else 'if unit doesn't have the i then it is assumed to be a install unit 'install unit is preceded with "n" If Left(unit, 1) = Worksheets("install price").Cells(2, 4) Then unit.Offset(0, 1) = "long equation2" Else 'if unit doesn't have an i or an n, then assumed to be an install unit unit.Offset(0, 1) = "long equation3" End If End If End If End If End If Application.EnableEvents = True Set unit = Nothing Exit Sub getout: Beep Application.EnableEvents = True MsgBox "Error " & Err.Number & " " & Err.Description End Sub '-------------------- "Dolemite" wrote in message As far as the \"\" is concerned, I have no idea what that is...I think that is something that has to do with the message board. If you look at my original post everywhere I have "" (2 double quotes), your reply has the \"\"...so I can see how that wouln't compile. The double quotes (with no space between them essentially gives me a blank cell) I know there are other ways to do this..but I stuck with what I knew worked...or thought worked... When I add that code to my my code, all it said was "Error 0" and it had an ok button and that was it. As posted, my code runs without errors, it just doesn't behave like I expected it to. where I added the comment '*******... that is where the code stops executing and I can't figure out why...there is no conditional statement there for it to kick out, I would expect it to just go from line to line, but it doesn't...I don't get it. -- Dolemite |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code won't execute...
You are indeed a lifesaver!!!!! Thanks for the explanation as well, it usually helps when someone can explain what is going on rather than just giving the solution. Once again thanks! -- Dolemite ------------------------------------------------------------------------ Dolemite's Profile: http://www.excelforum.com/member.php...o&userid=26136 View this thread: http://www.excelforum.com/showthread...hreadid=397401 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do you execute single line of code? | Excel Discussion (Misc queries) | |||
Execute code on server | Excel Programming | |||
Why does this code take so long to execute. | Excel Programming | |||
When does Code Execute on a List Box? | Excel Programming | |||
execute code for all 12 months | Excel Programming |