VBA code in excel seems to be deactivated..... 4

[tcolan]

Hi - It seems that all of the vba code in an excel workbook is not being called when the events occur in the spreadsheet. It's almost like the code has been deactivated (it was working fine before) I've played around with all of the security settings but still seem lost at figuring out why this is referencing correctly. Any help would be appreciated.

Thanks,
Tom

 

[tcolan]

Oh and the code is stored within the specific workbook. When I select the tab and coose to view code, I see all of the code. What's even more interesting is that I've copied over a sheet from another workbook that is working fine and once I copy it into this specific workbook, the code stops being called as well (almost seems deactivated). There must be a setting or something that I've done to inactivate the VBA? hmmm....

 

[PHV]

Does the project compiles properly ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tcolan]

Yes - it compiles with no errors.

 

[SkipVought]

You inserted a sheet from another workbook.

Did the code work BEFORE you did this?

What event(s) are not working?

Check how Sheets & ranges are referenced in your code. Activesheet is probably not a good idea or NO SHEET REFERENCES either.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tcolan]

Yes, I copied the sheet from another workbook just to test it out to see if it would work. The code works fine in the 'copy from' workbook, but seems deactivated when pasted into the the destination workbook (it compiles with no errors but when the event happens in the spreadsheet it doesn't seem like the code is even starting up and running)

The destination workbook did not run the code even before I pasted in the the test sheet.

Unfortunately, most of the code I'm looking to run are Worksheet_changes and refreshing pivot tables based on the active sheet. Hmmmmmm.... I guess I'm stumped because earlier versions of this spreadsheet worked and now all of the VBA seems 'deactivated'

 

[SkipVought]

"...but when the event happens ..."

Again, WHAT EVENT?

Please post the code that is not working.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tcolan]

Sure thing. The code below works in an earlier version of the workbook. I have not done anything to the spreadsheet that would change any of the ranges or cells referred to. The event that happens is selecting "Open", "Closed" or "Issue" from a drop down in column C and then having another status column populated with the current date.

I'm pretty much stumped and would hate to think I need to rebuild from an earlier version where this is working properly but I don't understand why the excel event does not seem to call the vba....




Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Dim r1 As Range
Set rng1 = Application.Intersect(Target, Range("B:B"))
If Not rng1 Is Nothing Then

Application.EnableEvents = False

For Each r1 In rng1
With r1
If Cells(.Row, "B").Value = "Closed" Then
Cells(.Row, "V").Value = Now()
End If
End With
Next r1

Application.EnableEvents = True

End If

Dim rng2 As Range
Dim r2 As Range
Set rng2 = Application.Intersect(Target, Range("B:B"))
If Not rng2 Is Nothing Then

Application.EnableEvents = False

For Each r2 In rng2
With r2
If Cells(.Row, "B").Value = "Open" Then
Cells(.Row, "U").Value = Now()
End If
End With
Next r2

Application.EnableEvents = True

End If

Dim rng3 As Range
Dim r3 As Range
Set rng3 = Application.Intersect(Target, Range("B:B"))
If Not rng3 Is Nothing Then

Application.EnableEvents = False

For Each r3 In rng3
With r3
If Cells(.Row, "B").Value = "Issue" Then
Cells(.Row, "U").Value = Now()
End If
End With
Next r3

Application.EnableEvents = True

End If

If Target.Column = 2 Then
If Target.Text = "Open" And Target.Offset(, -1) = "" Then
Target.Offset(, -1) = Application.WorksheetFunction.Max(Range("A:A")) + 1
End If
End If


End Sub

 

[SkipVought]

"The event that happens is selecting "Open", "Closed" or "Issue" from a drop down in column C ..."

    Set rng1 = Application.Intersect(Target, [b]Range("B:B")[/b])
    If Not rng1 Is Nothing Then

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tcolan]

oops - I mistyped, I meant to say column B - in other words I meant to say "The event that happens is selecting "Open", "Closed" or "Issue" from a drop down in column B ..."

sorry about that.... although I wish that oversight was the answer.... uggg....

 

[SkipVought]

NOTE: If you had to END your code execution while Application.EnableEvents = False, then you must execute Application.EnableEvents = True, before you run this event again.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng1 As Range
    Dim r1 As Range
    
    'do you really want this to be then entire column???
    Set rng1 = Application.Intersect(Target, Range("B:B"))
    
    'would this work?
    With Target.Parent
        Set rng1 = Application.Intersect(Target, Range(.Cells(.Row, "B"), .Cells(.Cells.Rows.Count, "B").End(xlUp)))
    End With
    
    If Not rng1 Is Nothing Then
 
        Application.EnableEvents = False
    
        For Each r1 In rng1
             With r1
                Select Case .Value
                    Case "Closed"
                        Cells(.Row, "V").Value = Now()
                    Case "Open"
                        Cells(.Row, "U").Value = Now()
                        With Target.Offset(, -1)
                            If .Value = "" Then
                                .Value = Application.WorksheetFunction.Max(Range("A:A")) + 1
                            End If
                        End With
                    Case "Issue"
                        Cells(.Row, "U").Value = Now()
                End Select
                  If Cells(.Row, "B").Value = "Closed" Then
                     Cells(.Row, "V").Value = Now()
                  End If
             End With
         Next r1
    
         Application.EnableEvents = True

    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tcolan]

Thanks Skip. I went and replaced my code with your's which is much more efficiently written - so thanks!

Unfortunately I'm getting the same result in which nothing happens. It appears that the code is not be called by excel. I don't get it because when I select 'view code' for the sheet, it's all there. Like I said, I'm at loss and I've double checked all of my security settings for vba, macros, etc.

I'm trying to figure out if this is an excel 2007 problem or a problem with the spreadsheet itself. Like I said, when I open an earlier version of the spreadsheet the code performs like it should. Now in this most current version, it's like excel doesn't even call the code when a selection is made.

What's even weirder is that if I have both spreadsheets open (new one and old version) even the old version will stop working. Once I exit out of excel and open the old version back up, everything works again. This makes me wonder if the code in the new version isn't being referenced to the associated sheet correctly??? I dunno if that's even possible, guess I'm grasping at straws here....

 

[SkipVought]

What happens if you save, close every instance of EXCEL and open?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tcolan]

The old version works but the new version does not....

 

[SkipVought]

And the difference between the two versions is.....

what?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

To get at what Skip is asking, one thing you can try checking for is this:

[ol][li]Open each workbook in different instances of Excel (if you don't know what I mean, ask).[/li]
[li]In each workbook, then, open the VBA window.[/li]
[li]In each VBA Window, go to Tools -> References, and make sure they are identical - if they aren't, that may be your problem.[/li]
[/ol]

The next thing to check is to see which location your code is saved with each workbook. Assuming you still have the VBA windows open:
[ol][li]Look to see WHERE your code is located...[ol][li]Is it in your PERSONAL macro workbook, or within something like 'Worksheet1' or 'MyWorksheetName'? - This will show in the "Projects Explorer" - default is in left pane - if not sure if it is there, select it from the View menu.[/li][/ol][/li][/ol]

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

Search the whole project of your non working workbook for EnableEvents

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xlbo]

Thinki PHV has it - one of your workbooks is setting or has set the ENABLEEVENTS property of the application to be false

Set to true in all workbooks and I'll bet the code starts running again

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[tcolan]

Thanks to all of you for your help.

I figured out that for some reason the Now() function in my code above was calling the VLOOKUPNEXT() function that I had put into a module for some other functionality in the spreadsheet. I'm not really sure why that is or how that happened, but once I renamed it to VLOOKUPNEXT1 and then changed all of the formula references, everything seemed to work fine.

Again, I'm at a loss and will do some more investigation but right now it seems to be working correctly.

Thanks for all of your help - I appreciate it!!!
Tom

 

[kjv1611]

Glad it's working again.

--

"If to err is human, then I must be some kind of human!" -Me