INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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?

RE: Screen Messed Up During Macro Run

Quote:

the table is selected

Are you using .SELECT somewhere in your code?

RE: Screen Messed Up During Macro Run

(OP)
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

RE: Screen Messed Up During Macro Run

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

CODE -->

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

(OP)
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.

RE: Screen Messed Up During Macro Run

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!!!!

RE: Screen Messed Up During Macro Run

To expand on BigInch's post:

CODE -->

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.

CODE -->

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close