Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event not firing
Hello Excel users and experts,
For the life of me I cannot get this simple change event to fire. I select A2, make a change in it and hit Enter... nothing! I've tried it in the sheet module, the ThisWorkbook module and a regular module. Still no go. I saved and closed Excel, re-opened still no go. This is fairly simple stuff and I have a few other change event macros in other workbooks that work just fine for me. If I assign LastNameEnter to a button it works fine. I have two other macros I want to be called with this change event, but can't even get one to work. I Google searched and found an example by Bernie D. to do this. His was without the EnableEvents and he used Call in front of the macro name. I've tried that too. I have also tried "If Target = Range("A2") then" ... I'm probably looking right past some dumb oversight. Any ideas??? Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$A$2" Then LastNameEnter End If Application.EnableEvents = True End Sub Sub LastNameEnter() Range("A2").Select Range("LNDest").ClearContents Selection.TextToColumns Destination:= _ ActiveCell.Offset(4, 0), _ DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), _ Array(1, 1), Array(2, 1), _ Array(3, 1), Array(4, 1), Array(5, 1), _ Array(6, 1), Array(7, 1), _ Array(8, 1), Array(9, 1)) Range("LastName", "A2").ClearContents Range("B2").Select End Sub Thanks for any help. Regards, Howard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event not firing
I believe the Application.EnableEvents = False is the culprit.
-- Pops Jackson "L. Howard Kittle" wrote: Hello Excel users and experts, For the life of me I cannot get this simple change event to fire. I select A2, make a change in it and hit Enter... nothing! I've tried it in the sheet module, the ThisWorkbook module and a regular module. Still no go. I saved and closed Excel, re-opened still no go. This is fairly simple stuff and I have a few other change event macros in other workbooks that work just fine for me. If I assign LastNameEnter to a button it works fine. I have two other macros I want to be called with this change event, but can't even get one to work. I Google searched and found an example by Bernie D. to do this. His was without the EnableEvents and he used Call in front of the macro name. I've tried that too. I have also tried "If Target = Range("A2") then" ... I'm probably looking right past some dumb oversight. Any ideas??? Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$A$2" Then LastNameEnter End If Application.EnableEvents = True End Sub Sub LastNameEnter() Range("A2").Select Range("LNDest").ClearContents Selection.TextToColumns Destination:= _ ActiveCell.Offset(4, 0), _ DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), _ Array(1, 1), Array(2, 1), _ Array(3, 1), Array(4, 1), Array(5, 1), _ Array(6, 1), Array(7, 1), _ Array(8, 1), Array(9, 1)) Range("LastName", "A2").ClearContents Range("B2").Select End Sub Thanks for any help. Regards, Howard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event not firing
Are you putting the code behind the worksheet that should have this behavior?
It doesn't belong in ThisWorkbook. Did you enable macros when you opened the workbook? Is your security level set to allow macros--even after a prompt? Did you test this and have it fail--and have .enableevents in the False position? Get to the VBE, hit ctrl-g to see the immediate window and type this application.enableevents = true and hit enter "L. Howard Kittle" wrote: Hello Excel users and experts, For the life of me I cannot get this simple change event to fire. I select A2, make a change in it and hit Enter... nothing! I've tried it in the sheet module, the ThisWorkbook module and a regular module. Still no go. I saved and closed Excel, re-opened still no go. This is fairly simple stuff and I have a few other change event macros in other workbooks that work just fine for me. If I assign LastNameEnter to a button it works fine. I have two other macros I want to be called with this change event, but can't even get one to work. I Google searched and found an example by Bernie D. to do this. His was without the EnableEvents and he used Call in front of the macro name. I've tried that too. I have also tried "If Target = Range("A2") then" ... I'm probably looking right past some dumb oversight. Any ideas??? Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$A$2" Then LastNameEnter End If Application.EnableEvents = True End Sub Sub LastNameEnter() Range("A2").Select Range("LNDest").ClearContents Selection.TextToColumns Destination:= _ ActiveCell.Offset(4, 0), _ DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), _ Array(1, 1), Array(2, 1), _ Array(3, 1), Array(4, 1), Array(5, 1), _ Array(6, 1), Array(7, 1), _ Array(8, 1), Array(9, 1)) Range("LastName", "A2").ClearContents Range("B2").Select End Sub Thanks for any help. Regards, Howard -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event not firing
Hi Dave & Pops,
Did you test this and have it fail--and have .enableevents in the False position? Yes! Get to the VBE, hit ctrl-g to see the immediate window and type this application.enableevents = true and hit enter This was the cure. Thanks for the help. Never had that happen before. I had some goffy code in the macros that were being cqlled and change event would go into a loop. I thought enableevents false would stop the loop. (sure did, but in a bad way) Thanks again, guys. Regards, Howard "Dave Peterson" wrote in message ... Are you putting the code behind the worksheet that should have this behavior? It doesn't belong in ThisWorkbook. Did you enable macros when you opened the workbook? Is your security level set to allow macros--even after a prompt? Did you test this and have it fail--and have .enableevents in the False position? Get to the VBE, hit ctrl-g to see the immediate window and type this application.enableevents = true and hit enter "L. Howard Kittle" wrote: Hello Excel users and experts, For the life of me I cannot get this simple change event to fire. I select A2, make a change in it and hit Enter... nothing! I've tried it in the sheet module, the ThisWorkbook module and a regular module. Still no go. I saved and closed Excel, re-opened still no go. This is fairly simple stuff and I have a few other change event macros in other workbooks that work just fine for me. If I assign LastNameEnter to a button it works fine. I have two other macros I want to be called with this change event, but can't even get one to work. I Google searched and found an example by Bernie D. to do this. His was without the EnableEvents and he used Call in front of the macro name. I've tried that too. I have also tried "If Target = Range("A2") then" ... I'm probably looking right past some dumb oversight. Any ideas??? Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$A$2" Then LastNameEnter End If Application.EnableEvents = True End Sub Sub LastNameEnter() Range("A2").Select Range("LNDest").ClearContents Selection.TextToColumns Destination:= _ ActiveCell.Offset(4, 0), _ DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), _ Array(1, 1), Array(2, 1), _ Array(3, 1), Array(4, 1), Array(5, 1), _ Array(6, 1), Array(7, 1), _ Array(8, 1), Array(9, 1)) Range("LastName", "A2").ClearContents Range("B2").Select End Sub Thanks for any help. Regards, Howard -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Textbox.change event not firing! | Excel Programming | |||
Stoppoing ComboBox Change Event from firing when Multipage changes | Excel Programming | |||
Worksheet change event not firing | Excel Programming | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming | |||
OnTime event not firing in Workbook_Open event procedure | Excel Programming |