Visible Screen
 

Hi all, I have a workbook with an auto_open macro which cycles through
all worksheets, selecting "A1" so that each of the 30+ sheets displays
in the top left position when opened. Works perfectly except for the
whirring display to the user, as I cannot use
"Application.ScreenUpdating = False" to suppress the display. Any help
appreciated, Thanks, Dave

Visible Screen
 

Why can't you use Application.ScreenUpdating = False

--
Regards,
Tom Ogilvy


"AussieDave" wrote:

Hi all, I have a workbook with an auto_open macro which cycles through
all worksheets, selecting "A1" so that each of the 30+ sheets displays
in the top left position when opened. Works perfectly except for the
whirring display to the user, as I cannot use
"Application.ScreenUpdating = False" to suppress the display. Any help
appreciated, Thanks, Dave

Visible Screen
 

Tom Ogilvy wrote:
Why can't you use Application.ScreenUpdating = False

--
Regards,
Tom Ogilvy


"AussieDave" wrote:

Hi all, I have a workbook with an auto_open macro which cycles through
all worksheets, selecting "A1" so that each of the 30+ sheets displays
in the top left position when opened. Works perfectly except for the
whirring display to the user, as I cannot use
"Application.ScreenUpdating = False" to suppress the display. Any help
appreciated, Thanks, Dave


Hi Tom, I tried it and, although the cursor was moved to A1, the
displayed part of the sheet remained as it was positioned prior to
moving the cursor.

Visible Screen
 

AussieDave wrote:
Tom Ogilvy wrote:
Why can't you use Application.ScreenUpdating = False

--
Regards,
Tom Ogilvy


"AussieDave" wrote:

Hi all, I have a workbook with an auto_open macro which cycles through
all worksheets, selecting "A1" so that each of the 30+ sheets displays
in the top left position when opened. Works perfectly except for the
whirring display to the user, as I cannot use
"Application.ScreenUpdating = False" to suppress the display. Any help
appreciated, Thanks, Dave


Hi Tom, I tried it and, although the cursor was moved to A1, the
displayed part of the sheet remained as it was positioned prior to
moving the cursor.
BTW, I'm using Range("A1").Select to move the cursor - what would be
the preferred option here?

Visible Screen
 

Private Sub Workbook_Open()
Dim sh as Worksheet, sh1 as Worksheet
Application.ScreenUpdating = False
set sh = Activesheet
for each sh1 in thisworkbook.Worksheets
sh1.Activate
sh1.Range("A1").Select
Next
sh.Activate
Application.ScreenUpdating = True
End Sub

I would expect to work.

--
Regards,
Tom Ogilvy


"AussieDave" wrote in message
ups.com...

AussieDave wrote:
Tom Ogilvy wrote:
Why can't you use Application.ScreenUpdating = False

--
Regards,
Tom Ogilvy


"AussieDave" wrote:

Hi all, I have a workbook with an auto_open macro which cycles
through
all worksheets, selecting "A1" so that each of the 30+ sheets
displays
in the top left position when opened. Works perfectly except for the
whirring display to the user, as I cannot use
"Application.ScreenUpdating = False" to suppress the display. Any
help
appreciated, Thanks, Dave


Hi Tom, I tried it and, although the cursor was moved to A1, the
displayed part of the sheet remained as it was positioned prior to
moving the cursor.
BTW, I'm using Range("A1").Select to move the cursor - what would be
the preferred option here?

Visible Screen
 

If a one or two second delay between iterations is what you are looking for
try this.

Sub WaitTime()
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
sitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait sitTime
End Sub

Add the sub to your code module that runs the opening macro and insert
"WaitTime" without the quote marks into the loop after the "Select" command.
You can adjust the amount of time you want the delay interval by changing the
+ 1 on newSecond to another whole integer in one second increments up to 59.
Same with minutes and hours.

I use this in several Do ... Loop routines and it works great for visual
effect.

"AussieDave" wrote:


AussieDave wrote:
Tom Ogilvy wrote:
Why can't you use Application.ScreenUpdating = False

--
Regards,
Tom Ogilvy


"AussieDave" wrote:

Hi all, I have a workbook with an auto_open macro which cycles through
all worksheets, selecting "A1" so that each of the 30+ sheets displays
in the top left position when opened. Works perfectly except for the
whirring display to the user, as I cannot use
"Application.ScreenUpdating = False" to suppress the display. Any help
appreciated, Thanks, Dave


Hi Tom, I tried it and, although the cursor was moved to A1, the
displayed part of the sheet remained as it was positioned prior to
moving the cursor.
BTW, I'm using Range("A1").Select to move the cursor - what would be
the preferred option here?

Visible Screen
 

JLGWhiz wrote:
If a one or two second delay between iterations is what you are looking for
try this.

Sub WaitTime()
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
sitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait sitTime
End Sub

Add the sub to your code module that runs the opening macro and insert
"WaitTime" without the quote marks into the loop after the "Select" command.
You can adjust the amount of time you want the delay interval by changing the
+ 1 on newSecond to another whole integer in one second increments up to 59.
Same with minutes and hours.

I use this in several Do ... Loop routines and it works great for visual
effect.

"AussieDave" wrote:


AussieDave wrote:
Tom Ogilvy wrote:
Why can't you use Application.ScreenUpdating = False

--
Regards,
Tom Ogilvy


"AussieDave" wrote:

Hi all, I have a workbook with an auto_open macro which cycles through
all worksheets, selecting "A1" so that each of the 30+ sheets displays
in the top left position when opened. Works perfectly except for the
whirring display to the user, as I cannot use
"Application.ScreenUpdating = False" to suppress the display. Any help
appreciated, Thanks, Dave


Hi Tom, I tried it and, although the cursor was moved to A1, the
displayed part of the sheet remained as it was positioned prior to
moving the cursor.
BTW, I'm using Range("A1").Select to move the cursor - what would be
the preferred option here?


Thanks JGLWhizz,
Nice little routine that I've put into my library of "Useful Subs"!!
However, I'm not trying to delay anything - rather, I'm trying to hide
my "housekeeping". Unfortunately, Tom's suggestion still leaves the
visible screen where it was at the time of opening - I need it
re-positioning so that Cell A1 is displayed at top-left of screen.
Thanks, Dave

Visible Screen
 

If your still having problems, try this

Private Sub Workbook_Open()
Dim sh as Worksheet, sh1 as Worksheet
Application.ScreenUpdating = False
set sh = Activesheet
for each sh1 in thisworkbook.Worksheets
sh1.Activate
Application.Goto sh1.Range("A1"), True
Next
sh.Activate
Application.ScreenUpdating = True
End Sub

I assume you don't have filters applied or freezepanes or something else
that could affect this.

--
Regards,
Tom Ogilvy


"AussieDave" wrote in message
ups.com...
JLGWhiz wrote:
If a one or two second delay between iterations is what you are looking
for
try this.

Sub WaitTime()
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
sitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait sitTime
End Sub

Add the sub to your code module that runs the opening macro and insert
"WaitTime" without the quote marks into the loop after the "Select"
command.
You can adjust the amount of time you want the delay interval by changing
the
+ 1 on newSecond to another whole integer in one second increments up to
59.
Same with minutes and hours.

I use this in several Do ... Loop routines and it works great for visual
effect.

"AussieDave" wrote:


AussieDave wrote:
Tom Ogilvy wrote:
Why can't you use Application.ScreenUpdating = False

--
Regards,
Tom Ogilvy


"AussieDave" wrote:

Hi all, I have a workbook with an auto_open macro which cycles
through
all worksheets, selecting "A1" so that each of the 30+ sheets
displays
in the top left position when opened. Works perfectly except for
the
whirring display to the user, as I cannot use
"Application.ScreenUpdating = False" to suppress the display.
Any help
appreciated, Thanks, Dave


Hi Tom, I tried it and, although the cursor was moved to A1, the
displayed part of the sheet remained as it was positioned prior to
moving the cursor.
BTW, I'm using Range("A1").Select to move the cursor - what would be
the preferred option here?


Thanks JGLWhizz,
Nice little routine that I've put into my library of "Useful Subs"!!
However, I'm not trying to delay anything - rather, I'm trying to hide
my "housekeeping". Unfortunately, Tom's suggestion still leaves the
visible screen where it was at the time of opening - I need it
re-positioning so that Cell A1 is displayed at top-left of screen.
Thanks, Dave

Visible Screen
 

Tom Ogilvy wrote:
If your still having problems, try this

Private Sub Workbook_Open()
Dim sh as Worksheet, sh1 as Worksheet
Application.ScreenUpdating = False
set sh = Activesheet
for each sh1 in thisworkbook.Worksheets
sh1.Activate
Application.Goto sh1.Range("A1"), True
Next
sh.Activate
Application.ScreenUpdating = True
End Sub

I assume you don't have filters applied or freezepanes or something else
that could affect this.

--
Regards,
Tom Ogilvy


"AussieDave" wrote in message
ups.com...
JLGWhiz wrote:
If a one or two second delay between iterations is what you are looking
for
try this.

Sub WaitTime()
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
sitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait sitTime
End Sub

Add the sub to your code module that runs the opening macro and insert
"WaitTime" without the quote marks into the loop after the "Select"
command.
You can adjust the amount of time you want the delay interval by changing
the
+ 1 on newSecond to another whole integer in one second increments up to
59.
Same with minutes and hours.

I use this in several Do ... Loop routines and it works great for visual
effect.

"AussieDave" wrote:


AussieDave wrote:
Tom Ogilvy wrote:
Why can't you use Application.ScreenUpdating = False

--
Regards,
Tom Ogilvy


"AussieDave" wrote:

Hi all, I have a workbook with an auto_open macro which cycles
through
all worksheets, selecting "A1" so that each of the 30+ sheets
displays
in the top left position when opened. Works perfectly except for
the
whirring display to the user, as I cannot use
"Application.ScreenUpdating = False" to suppress the display.
Any help
appreciated, Thanks, Dave


Hi Tom, I tried it and, although the cursor was moved to A1, the
displayed part of the sheet remained as it was positioned prior to
moving the cursor.
BTW, I'm using Range("A1").Select to move the cursor - what would be
the preferred option here?


Thanks JGLWhizz,
Nice little routine that I've put into my library of "Useful Subs"!!
However, I'm not trying to delay anything - rather, I'm trying to hide
my "housekeeping". Unfortunately, Tom's suggestion still leaves the
visible screen where it was at the time of opening - I need it
re-positioning so that Cell A1 is displayed at top-left of screen.
Thanks, Dave

THAT's the one!!!! Thanks a million Tom - I learn so much from the
learned responses on these pages. Dave