ScreenUpdating

[Guest]

I have used ScreenUpdating = False in certain instances. My issue is that in
a certain file I am trying to get to a certain cell in the worksheet and have
a certain screen setup after the macro(s) run. (In this instance it is that
the cursor be on the selected row based on criteria and be the 3rd row that
shows under the Windows/FreezeFrame rows). The only way I have been able to
resolve my issue is that at certain points in the Macro(s) I have to set the
ScreenUpdating back to True and then back to False. I can get the desired
result doing this but when I do this I also have some flickers occuring on in
the screen. The flickering is not that bad but I would prefer a totally
smooth transition. Is there any way that I can stop these flickers and
totally freeze the screen until the macro(s) have completed and get my
desired results of the desired screen postioning.

Thank you for your help.

Steven

 

[Guest]

what commands that your are using require that you set screenupdating to true?

 

[Guest]

Tom,

Thank you for responding. Maybe I need to approach the explanation a
different way. I have rows 1 - 3 on Windows/FreezeFrame so they always show.
There are no columns Frozen. The end result I am looking for, this is after
a lot of calculations in the macro, is for a particular cell to be the 3rd
row under the frozen rows and be the second column over. For example: lets
say after the the macro has done all its calclations it has determined that
cell $C$39 should be the cell that is the 3rd row showing under the frozen
rows and be the 2nd colum ie Col A is not showing (it is not hidden, it is
just positioned off the screen). I am doing a lot of
ActiveCell.Offsets......... to get the postions to where I want it. If I
dont set the ScreenUpdating to True at certain places then when the macro
finishes the cursor postioned on C39 may not even show on the screen.

Maybe there is a more direct way to say I want a particually address to be
the certain postion Row,Col showing on the screen???

Thank you,

Steven

 

[Guest]

Tom,

Thank you for responding. Maybe I need to approach the explanation a
different way. I have rows 1 - 3 on Windows/FreezeFrame so they always show.
There are no columns Frozen. The end result I am looking for, this is after
a lot of calculations in the macro, is for a particular cell to be the 3rd
row under the frozen rows and be the second column over. For example: lets
say after the the macro has done all its calclations it has determined that
cell $C$39 should be the cell that is the 3rd row showing under the frozen
rows and be the 2nd colum ie Col A is not showing (it is not hidden, it is
just positioned off the screen). I am doing a lot of
ActiveCell.Offsets......... to get the postions to where I want it. If I
dont set the ScreenUpdating to True at certain places then when the macro
finishes the cursor postioned on C39 may not even show on the screen.

Maybe there is a more direct way to say I want a particually address to be
the certain postion Row,Col showing on the screen???

Thank you,

Steven

 

[Guest]

If 3 rows are frozen, then C29 can be the 4th row, but it can't be the third
unless you have the first row not visible. So I will assume that is the
case. This opens up the display with C29 as the third row, second column
after randomly selection 100 cells throughout the spreadsheet.

Sub positionItems()
Dim rng as Range, ii as Long, jj as Long
Dim i as Long, j as Long
Application.ScreenUpdating = False
ActiveWindow.FreezePanes = False
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollColumn = 1
Rows(4).Select
ActiveWindow.FreezePanes = True
For i = 1 To 10
For j = 1 To 10
ii = Int(Rnd() * 1000 + 1)
jj = Int(Rnd() * 256 + 1)
Cells(ii, jj).Select
Next
Next
Set rng = Range("C29")
ActiveWindow.ScrollColumn = rng.Column - 1
ActiveWindow.ScrollRow = rng.Row
Application.ScreenUpdating = True
End Sub

 

[Guest]

Thank you very much. It was the 3 lines after the 2nd Next that did it.