ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Code won't execute... (https://www.excelbanter.com/excel-programming/337803-re-code-wont-execute.html)

Jim Cone

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


Dolemite[_4_]

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



All times are GMT +1. The time now is 06:53 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com