Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change in Excel 97 does not run first time
I am using the following code to run a Macro when there is a change in cell A1. This works well on excel 2003, however it does not run automatically on excel 97. If the user changes the value in A1 for a second time in 97 then the code will work. If you could provide advice on what code would work on the first change in 97 and later versions it would be appreciated. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("a1")) Is Nothing Then Exit Sub Else Application.Run "HideRows" End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change in Excel 97 does not run first time
Unfortunately that does not solve the problem. The same isues apply. I paste data into the spreadsheet however the macro does not run. If I then manually delete the contents of cell A1 then the macro will run. Issue only applies to users on 97. "Jacob Skaria" wrote: Try the below. Right click the sheet tabViewCode and paste the below Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1")) Is Nothing _ Then Application.Run "HideRows" Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Monk" wrote: I am using the following code to run a Macro when there is a change in cell A1. This works well on excel 2003, however it does not run automatically on excel 97. If the user changes the value in A1 for a second time in 97 then the code will work. If you could provide advice on what code would work on the first change in 97 and later versions it would be appreciated. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("a1")) Is Nothing Then Exit Sub Else Application.Run "HideRows" End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change in Excel 97 does not run first time
The change event works fine first time in Excel 97. Maybe there's something
else going on you haven't told us about on that leads to giving the impression the event doesn't work first time. Regards, Peter T "Monk" wrote in message ... I am using the following code to run a Macro when there is a change in cell A1. This works well on excel 2003, however it does not run automatically on excel 97. If the user changes the value in A1 for a second time in 97 then the code will work. If you could provide advice on what code would work on the first change in 97 and later versions it would be appreciated. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("a1")) Is Nothing Then Exit Sub Else Application.Run "HideRows" End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change in Excel 97 does not run first time
Perhaps the issue is with the macro it is attempting to run. I have attached
the code below. Thanks for your assistance. Sub HideRows() ' Application.ScreenUpdating = False Application.DisplayAlerts = False Application.Calculation = xlCalculationManual Sheets("AET Allocations").Select Range("G6:G705").Select Selection.Rows.AutoFit Dim c As Range Dim r As Range For Each c In Range("A6:A800") If c.Value = "" Then If r Is Nothing Then Set r = c Else Set r = Union(r, c) End If End If Next c r.EntireRow.Hidden = True Range("b1").Select Sheets("AET Client List").Select Range("A1").Select Selection.ClearContents Range("a1").Select Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub "Peter T" wrote: The change event works fine first time in Excel 97. Maybe there's something else going on you haven't told us about on that leads to giving the impression the event doesn't work first time. Regards, Peter T "Monk" wrote in message ... I am using the following code to run a Macro when there is a change in cell A1. This works well on excel 2003, however it does not run automatically on excel 97. If the user changes the value in A1 for a second time in 97 then the code will work. If you could provide advice on what code would work on the first change in 97 and later versions it would be appreciated. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("a1")) Is Nothing Then Exit Sub Else Application.Run "HideRows" End If End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change in Excel 97 does not run first time
I can't see anything in your macro that would prevent the event from running
"first time". In any case the macro is called *within* then event so it wouldn't be relevant anyway. Not sure why you are using Application.run unless the macro is in another workbook, if not just call it directly. FWIW here's another version of your macro - Sub HideRows2() Dim c As Range Dim r As Range Dim ws As Worksheet On Error GoTo errExit With Application .ScreenUpdating = False .DisplayAlerts = False .Calculation = xlCalculationManual .EnableEvents = False End With Set ws = Worksheets("AET Allocations") ws.Range("G6:G705").Rows.AutoFit On Error Resume Next Set r = ws.Range("a1:a800").SpecialCells(xlCellTypeBlanks) On Error GoTo errExit If Not r Is Nothing Then r.EntireRow.Hidden = False End If Set ws = Worksheets("AET Client List") ws.Range("A1").ClearContents errExit: With Application .DisplayAlerts = True .Calculation = xlCalculationAutomatic .ScreenUpdating = True .EnableEvents = True End With End Sub "Monk" wrote in message ... Perhaps the issue is with the macro it is attempting to run. I have attached the code below. Thanks for your assistance. Sub HideRows() ' Application.ScreenUpdating = False Application.DisplayAlerts = False Application.Calculation = xlCalculationManual Sheets("AET Allocations").Select Range("G6:G705").Select Selection.Rows.AutoFit Dim c As Range Dim r As Range For Each c In Range("A6:A800") If c.Value = "" Then If r Is Nothing Then Set r = c Else Set r = Union(r, c) End If End If Next c r.EntireRow.Hidden = True Range("b1").Select Sheets("AET Client List").Select Range("A1").Select Selection.ClearContents Range("a1").Select Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub "Peter T" wrote: The change event works fine first time in Excel 97. Maybe there's something else going on you haven't told us about on that leads to giving the impression the event doesn't work first time. Regards, Peter T "Monk" wrote in message ... I am using the following code to run a Macro when there is a change in cell A1. This works well on excel 2003, however it does not run automatically on excel 97. If the user changes the value in A1 for a second time in 97 then the code will work. If you could provide advice on what code would work on the first change in 97 and later versions it would be appreciated. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("a1")) Is Nothing Then Exit Sub Else Application.Run "HideRows" End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i get the date and time if a change is done in a worksheet | Excel Discussion (Misc queries) | |||
Worksheet Change in Excel 97 does not run first time | Excel Programming | |||
Worksheet change time format | Excel Programming | |||
Time Format on Worksheet Change | Excel Programming | |||
Time format via Worksheet Change | Excel Programming |