Screen Messed Up During Macro Run 1

[SteveGregory]

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?

 

[MintJulep]

the table is selected

Are you using .SELECT somewhere in your code?

 

[SteveGregory]

Yes. Here it is. It was recorded and I added the first and last lines in an attempt to fix the problem.

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

 

[MintJulep]

Try this. I don't see that the .Select line actually accomplishes anything, and I think it is the cause of your problem.

Sub SortM()
'
' SortM Macro
' Sort by reduced Moment capacity
'

'
Application.ScreenUpdating = False
[COLOR=#CC0000][s]Range("A5:R37").Select[/s][/color]
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

 

[SteveGregory]

Thanks Mint! I commented that line out and it worked great.

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.

 

[BigInch]

If references to cells, or sheets use "Set", such as .SetRange Range("A5:R37"), or use direct referencing as "Cells(1,2).Value = 3" does, the method of selecting a cell or range by moving the cursor to the cell, or capturing a range of cells with the cursor, as the ".Select" method does, is not necessary.

I hate Windowz 8!!!!

 

[MintJulep]

To expand on BigInch's post:

Range("A35").Select
Selection.Value = 123

automates 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.

Range("A35").Value = 123

What 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.