Screen Messed Up During Macro Run
Screen Messed Up During Macro Run
(OP)
I recorded a macro to sort a table of data 33 rows by 18 columns on Sheet2. Then I put a button on Sheet1 to execute it since the main calculations are on Sheet1. Simple!
When I run the macro, there is a darkened or highlighted portion of my screen on Sheet1 that corresponds to the data table on Sheet2. If I scroll down and up to refresh the screen it goes away. When I go to Sheet2, the table is selected. I added two lines to the macro to stop and restart screen updating to no avail.
Sub SortM()
Application.ScreenUpdating = False
...
Application.ScreenUpdating = True
End Sub
I am using Excel 2007. Any ideas?
When I run the macro, there is a darkened or highlighted portion of my screen on Sheet1 that corresponds to the data table on Sheet2. If I scroll down and up to refresh the screen it goes away. When I go to Sheet2, the table is selected. I added two lines to the macro to stop and restart screen updating to no avail.
Sub SortM()
Application.ScreenUpdating = False
...
Application.ScreenUpdating = True
End Sub
I am using Excel 2007. Any ideas?





RE: Screen Messed Up During Macro Run
Are you using .SELECT somewhere in your code?
RE: Screen Messed Up During Macro Run
Sub SortM()
'
' SortM Macro
' Sort by reduced Moment capacity
'
'
Application.ScreenUpdating = False
Range("A5:R37").Select
ActiveWorkbook.Worksheets("Properties").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Properties").Sort.SortFields.Add Key:=Range( _
"M5:M37"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Properties").Sort
.SetRange Range("A5:R37")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.ScreenUpdating = True
End Sub
RE: Screen Messed Up During Macro Run
CODE -->
Sub SortM() ' ' SortM Macro ' Sort by reduced Moment capacity ' ' Application.ScreenUpdating = FalseRange("A5:R37").SelectActiveWorkbook.Worksheets("Properties").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Properties").Sort.SortFields.Add Key:=Range( _ "M5:M37"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Properties").Sort .SetRange Range("A5:R37") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Application.ScreenUpdating = True End SubRE: Screen Messed Up During Macro Run
Sheet1 doesn't have any screen issues. Sheet2 has the table selected, but I don't really care too much about that because it is out of sight.
RE: Screen Messed Up During Macro Run
I hate Windowz 8!!!!
RE: Screen Messed Up During Macro Run
CODE -->
Range("A35").Select Selection.Value = 123automates the process of you moving the curson to Cell A35, double clicking to Select it, then typing 123.
That is totally necessary (and slows things down) in VBA because you can just directly change the value.
CODE -->
Range("A35").Value = 123What happened with you original code was:
You clicked a button on sheet1 | This is where the macro starts from, and is thus the Active sheet.
Application.ScreenUpdating = False | Tells the macro to not bother updating the screen
Range("A5:B37").Select | These cells get highlighted, but because screen updating is off this is not displayed. You probably recorded the macro from Sheet2, so excel assumes Sheet2 for the unspecified sheet in this line.
Application.ScreenUpdating = True | Go ahead and refresh the screen.
End Sub | The macro stops. When macros stop Excel wants to go back to the same place it was when the macro started. In this case, Sheet1.
So there was the latent highlighting of the range of cells on Sheet2 that Excel really wanted to show you, but couldn't because ScreenUpdating was off. Then when the macro stopped Sheet1 was the Active Sheet again. So Excel got confused and tried to do both things at once.