"running..." message
 

I have a routine that selects sheet after sheet doing various commands an
each sheet. I would prefer to NOT see the rapid flashing of sheet after
sheet being selected/opened. Can I do this in the background (not
viewable), or at least have a "cover" that hides the flashing of sheet
selections that reads: "running, be patient, please."? thanks

sub manysheets()
sheets("1").select
'copy cell A1 and paste in B1
sheets("2").select
'copy cell A1 and paste in B1
sheets("3").select
'any various functions
end sub

"running..." message
 

This will not only make the process invisible, it will also make the
whole thing faster since the computer doesn't have to spend so much
time displaying the changes. I have a program that used to take 30
seconds that now takes about 3 because of this:

sub manysheets()

Application.ScreenUpdating = False

sheets("1").select
'copy cell A1 and paste in B1
sheets("2").select
'copy cell A1 and paste in B1
sheets("3").select
'any various functions

Application.ScreenUpdating = True

end sub
:cool:


---
Message posted from http://www.ExcelForum.com/

"running..." message
 

Mike, you can do it like this

Application.ScreenUpdating = False
'your code here
Application.ScreenUpdating = True

or a better way would be to do it without selecting the sheets like this
Sheets("1").Range("B1").Value = Sheets("1").Range("A1").Value


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
"mike allen" wrote in message
...
I have a routine that selects sheet after sheet doing various commands an
each sheet. I would prefer to NOT see the rapid flashing of sheet after
sheet being selected/opened. Can I do this in the background (not
viewable), or at least have a "cover" that hides the flashing of sheet
selections that reads: "running, be patient, please."? thanks

sub manysheets()
sheets("1").select
'copy cell A1 and paste in B1
sheets("2").select
'copy cell A1 and paste in B1
sheets("3").select
'any various functions
end sub

"running..." message
 

It would take even less if you avoided the selections:

Public Sub manysheets()
Dim wkSht as worksheet
Application.ScreenUpdating = False
For Each wkSht In _
Worksheets(Array("Sheet1", "Sheet2", "Sheet3"))
With wkSht
.Range("A1").Value = .Range("B1").Value
End With
Next wkSht
Application.ScreenUpdating = True
End Sub


In article ,
pikus wrote:

This will not only make the process invisible, it will also make the
whole thing faster since the computer doesn't have to spend so much
time displaying the changes. I have a program that used to take 30
seconds that now takes about 3 because of this:

sub manysheets()

Application.ScreenUpdating = False

sheets("1").select
'copy cell A1 and paste in B1
sheets("2").select
'copy cell A1 and paste in B1
sheets("3").select
'any various functions

Application.ScreenUpdating = True

end sub
:cool:

"running..." message
 

An excellent point. Maybe next time I should actually slow down enoug
to look at what I'm doing. Damn ADD. - Pikus:

--
Message posted from http://www.ExcelForum.com

"running..." message
 

great info. thanks. I use this type of code often:

sheets("1").range("a1").Copy 'this is a formula
sheets("2").range("c3").PasteSpecial Paste:=xlValues 'this needs to be hard
number

i tried your way of:
sheets("2").range("c3").value=sheets("1").range("a 1").value
as a replacement. I assume this is exactly the same thing?

by not using ...Select or ...Copy makes program faster, but using:
sheets("2").protect
still flips to sheet2 (from sheet1 for instance), then back to sheet1.
i know i can use:
Application.ScreenUpdating = False
'code
Application.ScreenUpdating = True
but it seems the entire program would be more efficient if i didn't need
this at all, i.e. going to sheet2 would not occur upon: sheets("2").protect
any ideas? thanks

"Paul B" wrote in message
...
Mike, you can do it like this

Application.ScreenUpdating = False
'your code here
Application.ScreenUpdating = True

or a better way would be to do it without selecting the sheets like this
Sheets("1").Range("B1").Value = Sheets("1").Range("A1").Value


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
"mike allen" wrote in message
...
I have a routine that selects sheet after sheet doing various commands
an
each sheet. I would prefer to NOT see the rapid flashing of sheet after
sheet being selected/opened. Can I do this in the background (not
viewable), or at least have a "cover" that hides the flashing of sheet
selections that reads: "running, be patient, please."? thanks

sub manysheets()
sheets("1").select
'copy cell A1 and paste in B1
sheets("2").select
'copy cell A1 and paste in B1
sheets("3").select
'any various functions
end sub

"running..." message
 

Mike, yes it does the same thing but you don't have to copy and paste.
If you are unprotect the sheet to run your macro and then protecting it
after the code you could use user interface only to protect the sheet, then
your macros would run with the sheet protected like this,
Worksheets("2").Protect , userInterfaceOnly:=True
excel does not "remember" this when you close the workbook, so you could
call it on workbook open to set user interface to true



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
"mike allen" wrote in message
...
great info. thanks. I use this type of code often:

sheets("1").range("a1").Copy 'this is a formula
sheets("2").range("c3").PasteSpecial Paste:=xlValues 'this needs to be
hard
number

i tried your way of:
sheets("2").range("c3").value=sheets("1").range("a 1").value
as a replacement. I assume this is exactly the same thing?

by not using ...Select or ...Copy makes program faster, but using:
sheets("2").protect
still flips to sheet2 (from sheet1 for instance), then back to sheet1.
i know i can use:
Application.ScreenUpdating = False
'code
Application.ScreenUpdating = True
but it seems the entire program would be more efficient if i didn't need
this at all, i.e. going to sheet2 would not occur upon:
sheets("2").protect
any ideas? thanks

"Paul B" wrote in message
...
Mike, you can do it like this

Application.ScreenUpdating = False
'your code here
Application.ScreenUpdating = True

or a better way would be to do it without selecting the sheets like this
Sheets("1").Range("B1").Value = Sheets("1").Range("A1").Value


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
"mike allen" wrote in message
...
I have a routine that selects sheet after sheet doing various
commands
an
each sheet. I would prefer to NOT see the rapid flashing of sheet
after
sheet being selected/opened. Can I do this in the background (not
viewable), or at least have a "cover" that hides the flashing of sheet
selections that reads: "running, be patient, please."? thanks

sub manysheets()
sheets("1").select
'copy cell A1 and paste in B1
sheets("2").select
'copy cell A1 and paste in B1
sheets("3").select
'any various functions
end sub

"running..." message
 

paul, pikus, j.e., thanks so much for the valuable info.
can you tell me a bit more about: "userInterfaceOnly" option?
i assume the purpose of this (set on true) is to allow work to be done on a
protected
sheet via code only while still disallowing manually changing a protected
cell on protected sheet?
what if i wanted to set this for entire workbook, like a general setting, in
the open routine?
something like:
sub auto_open()
ActiveWorkBook.userinterfaceOnly:=True 'this, of course, doesn't work
end sub
thanks

"Paul B" wrote in message
...
Mike, yes it does the same thing but you don't have to copy and paste.
If you are unprotect the sheet to run your macro and then protecting it
after the code you could use user interface only to protect the sheet,
then
your macros would run with the sheet protected like this,
Worksheets("2").Protect , userInterfaceOnly:=True
excel does not "remember" this when you close the workbook, so you could
call it on workbook open to set user interface to true



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
"mike allen" wrote in message
...
great info. thanks. I use this type of code often:

sheets("1").range("a1").Copy 'this is a formula
sheets("2").range("c3").PasteSpecial Paste:=xlValues 'this needs to be
hard
number

i tried your way of:
sheets("2").range("c3").value=sheets("1").range("a 1").value
as a replacement. I assume this is exactly the same thing?

by not using ...Select or ...Copy makes program faster, but using:
sheets("2").protect
still flips to sheet2 (from sheet1 for instance), then back to sheet1.
i know i can use:
Application.ScreenUpdating = False
'code
Application.ScreenUpdating = True
but it seems the entire program would be more efficient if i didn't need
this at all, i.e. going to sheet2 would not occur upon:
sheets("2").protect
any ideas? thanks

"Paul B" wrote in message
...
Mike, you can do it like this

Application.ScreenUpdating = False
'your code here
Application.ScreenUpdating = True

or a better way would be to do it without selecting the sheets like
this
Sheets("1").Range("B1").Value = Sheets("1").Range("A1").Value


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from
it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
"mike allen" wrote in message
...
I have a routine that selects sheet after sheet doing various
commands
an
each sheet. I would prefer to NOT see the rapid flashing of sheet
after
sheet being selected/opened. Can I do this in the background (not
viewable), or at least have a "cover" that hides the flashing of
sheet
selections that reads: "running, be patient, please."? thanks

sub manysheets()
sheets("1").select
'copy cell A1 and paste in B1
sheets("2").select
'copy cell A1 and paste in B1
sheets("3").select
'any various functions
end sub

"running..." message
 

Mike, below is from VBA help on protect, here is a way to do all the sheets
on open. you could also use auto_open for this, this macro would go in the
thisworkbook code

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect , userInterfaceOnly:=True
Next ws
End Sub


Protect Method


Protects a chart or worksheet (Syntax 1) or a workbook (Syntax 2) so that it
cannot be modified.

Syntax 1

expression.Protect(Password, DrawingObjects, Contents, Scenarios,
UserInterfaceOnly)

Syntax 2

expression.Protect(Password, Structure, Windows)

expression Required. An expression that returns a Chart or Worksheet
object (Syntax 1) or a Workbook object (Syntax 2).

Password Optional Variant. A string that specifies a case-sensitive
password for the sheet or workbook. If this argument is omitted, you can
unprotect the sheet or workbook without using a password. Otherwise, you
must specify the password to unprotect the sheet or workbook. If you forget
the password, you cannot unprotect the sheet or workbook. It's a good idea
to keep a list of your passwords and their corresponding document names in a
safe place.

DrawingObjects Optional Variant. True to protect shapes. The default value
is False.

Contents Optional Variant. True to protect contents. For a chart, this
protects the entire chart. For a worksheet, this protects the individual
cells. The default value is True.

Scenarios Optional Variant. True to protect scenarios. This argument is
valid only for worksheets. The default value is True.

Structure Optional Variant. True to protect the structure of the workbook
(the relative position of the sheets). The default value is False.

UserInterfaceOnly Optional Variant. True to protect the user interface,
but not macros. If this argument is omitted, protection applies both to
macros and to the user interface.

Windows Optional Variant. True to protect the workbook windows. If this
argument is omitted, the windows aren't protected.

Remarks

If you apply the Protect method with the UserInterfaceOnly argument set to
True to a worksheet and then save the workbook, the entire worksheet (not
just the interface) will be fully protected when you reopen the workbook. To
unprotect the worksheet but re-enable user interface protection after the
workbook is opened, you must again apply the Protect method with
UserInterfaceOnly set to True.


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
"mike allen" wrote in message
...
paul, pikus, j.e., thanks so much for the valuable info.
can you tell me a bit more about: "userInterfaceOnly" option?
i assume the purpose of this (set on true) is to allow work to be done on
a
protected
sheet via code only while still disallowing manually changing a protected
cell on protected sheet?
what if i wanted to set this for entire workbook, like a general setting,
in
the open routine?
something like:
sub auto_open()
ActiveWorkBook.userinterfaceOnly:=True 'this, of course, doesn't work
end sub
thanks

"Paul B" wrote in message
...
Mike, yes it does the same thing but you don't have to copy and paste.
If you are unprotect the sheet to run your macro and then protecting it
after the code you could use user interface only to protect the sheet,
then
your macros would run with the sheet protected like this,
Worksheets("2").Protect , userInterfaceOnly:=True
excel does not "remember" this when you close the workbook, so you could
call it on workbook open to set user interface to true



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
"mike allen" wrote in message
...
great info. thanks. I use this type of code often:

sheets("1").range("a1").Copy 'this is a formula
sheets("2").range("c3").PasteSpecial Paste:=xlValues 'this needs to
be
hard
number

i tried your way of:
sheets("2").range("c3").value=sheets("1").range("a 1").value
as a replacement. I assume this is exactly the same thing?

by not using ...Select or ...Copy makes program faster, but using:
sheets("2").protect
still flips to sheet2 (from sheet1 for instance), then back to sheet1.
i know i can use:
Application.ScreenUpdating = False
'code
Application.ScreenUpdating = True
but it seems the entire program would be more efficient if i didn't
need
this at all, i.e. going to sheet2 would not occur upon:
sheets("2").protect
any ideas? thanks

"Paul B" wrote in message
...
Mike, you can do it like this

Application.ScreenUpdating = False
'your code here
Application.ScreenUpdating = True

or a better way would be to do it without selecting the sheets like
this
Sheets("1").Range("B1").Value = Sheets("1").Range("A1").Value


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit
from
it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
"mike allen" wrote in message
...
I have a routine that selects sheet after sheet doing various
commands
an
each sheet. I would prefer to NOT see the rapid flashing of sheet
after
sheet being selected/opened. Can I do this in the background (not
viewable), or at least have a "cover" that hides the flashing of
sheet
selections that reads: "running, be patient, please."? thanks

sub manysheets()
sheets("1").select
'copy cell A1 and paste in B1
sheets("2").select
'copy cell A1 and paste in B1
sheets("3").select
'any various functions
end sub