freezing on one screen as macro proceeds (excel 2000)

Topic

New Reply

I currently have a macro that copies information from one workbook to another from several different spots on the original sheet. As the macro proceeds there is quite a lot of panel fluctuation and I was wondering if there was a way to “freeze” the screen until the macro finished?

Reply | Quote

Replies

Include the following line at the start of your code :

   Application.ScreenUpdating = False

and return things to normal when the prcessing is finished with

   Application.ScreenUpdating = True

Andrew C

Reply | Quote

Andrew,
That simple!!!!! wow!!! Sorry to bother you. I should have been able to figure that out by myself. As you probably could tell I am new to this programming macro thing and I am using a book by that is suppose to teach me how to do this stuff in just 24 hours…. so far it is a little slow coming, however, it is a BLAST to keep the old grey matter churning. Thank you for the quick response… this is just a great board…. I know I will learn a GREAT deal.

Reply | Quote

Two things:

1- If you don’t select the cells and sheets where you are copying, then there is no reason for the display to change. The macro will also run much faster. There are very few times when it is actually necessary to change the selection in a macro.

2- You can include the following lines of code before and after where you want the screen frozen:

    Application.ScreenUpdating = False
    ' Do your copying
    Application.ScreenUpdating = True

Reply | Quote

Legare
You said “If you don’t select the cells and sheets where you are copying, then there is no reason for the display to change. The macro will also run much faster. There are very few times when it is actually necessary to change the selection in a macro.”
I am fairly new to this visual basic thing and I don’t quite understand what you mean. I don’t mean to be dense however it comes naturally sometimes java script:returnTag(‘ ‘) I thought I needed to “select”/ copy the cells from one workbook sheet and have them pasted to a completely different workbook sheet.

If you would be so kind to explain a little more I would greatly appreciate it . If you prefer to send me an separate e-mail instead of taking up space on this great board feel free to do so.
tia
Bill

Reply | Quote

No, I don’t want to send a private message since then no one else could benefit from my answer. That would defeat the purpose of this forum.

If you wanted to copy the range A1:C2 on Sheet1 and paste it into Sheet2 starting at cell A1, then you could use code like the following which selects the different ranges and activates the two sheets:

    Worksheets("Sheet1").Activate
    Worksheets("Sheet1").Range("A1:C2").Select
    Selection.Copy
    Worksheets("Sheet2").Activate
    Worksheets("Sheet2").Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

This will cause the screen to flash a lot. You will get code similar to the above if you record a macro to do this.

The following code does the same thing, but it never changes the selected range or the active worksheet, therefore the screen will not flash. In addition, it will run much faster.

    Worksheets("Sheet1").Range("A1:C2").Copy
    Worksheets("Sheet2").Paste Destination:=Worksheets("Sheet2").Range("A1")
    Application.CutCopyMode = False

Reply | Quote

Actually, since the Copy method allows a destination as a parameter, you can shorten Legare’s code to a one liner:

    Worksheets("Sheet1").Range("A1:C2").Copy _
        Worksheets("Sheet2").Range("A1")

Usually you can get started writing better VBA code by recording a macro, then “collapsing” the code. For example, if you record a macro to enter the word, Test, in cell A1, you get something like:

    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Test"

You can collapse this to

    Range("A1").FormulaR1C1 = "Test"

or even

    Range("A1")  = "Test"

but to be on the safe side

    Sheets("Sheet1").Range("A1") = "Test"

is best because Excel can’t mess you up with it’s defaults. HTH –Sam

Reply | Quote

Legare, Sammy
Thank you both for the information. I will continue to sit on the side and read the material I have in my book and I will certainly post any questions that come along. I do have a great deal to learn, however, with the help of this board and the book who knows I may stop….

As I said, “This is a GREAT board”,
tia
Bill

Reply | Quote

Hey, Excel is easy: at least when you record a macro, you can usually follow what the code is doing! Wait until you try to do Word, PowerPoint or Access VBA — they are all serious . What book are you using? The one I refer to is Writing Excel Macros by Steven Roman. It’s thin, cheap, and usually has what I need. –Sam

Reply | Quote

Especially Access – you can’t record macros! And there’s DAO vs. ADO…..

Reply | Quote

Yeah, But Access is FUN,!! (As long as you keep taking the tablets )

Reply | Quote

For me, with Access, there’s only DAQ’s.

Reply | Quote

Snap!

Reply | Quote

Yes, probably best you didn’t!

Reply | Quote

Babbling in public again?

Reply | Quote

Guilty!

Reply | Quote

SammyB ,
Sorry for the time frame delay in reply, I have been on the road for the last couple of days and no access to the net. Yes, I was going through withdrawal…. anyway I am back to the computer and all is well. As for the book I am using it is Step by Step Excel 97 Visual Basic by Reed Jacobson. It is so far a very good study guide with some small areas that could be spelled out a little more, however, that is when I will turn to this board and all of you for help.
Again, you have been a great help along with all the others and I will check out the book that you have listed and I can’t wait to look into the other vba macros. As the man once said “I’ll be back”
tia,
Bill

Reply | Quote

Bill:

If you are looking for a good book on Excel and VBA you might check out John Walkenbach’s “Excel 2000: Power Programming with VBA” I have five or six Excel reference books, but that is the one I use most often. The discussion of topics is thorough and complete and the topic sequence is logical. There is some introductory user-oriented stuff at the start and after that it assumes a pretty good level of expertise, but is quite accessible for anyone who has been ‘hands-on’ with Excel for a while.

At the moment, I just wish John had written a similar book on Access!

Reply | Quote

Dean,
Thanks for the tip. I will check this one out also.
later,
Bill

Reply | Quote