Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    freezing on one screen as macro proceeds (excel 2000)

    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?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: freezing on one screen as macro proceeds (excel 2000)

    Include the following line at the start of your code :<pre> Application.ScreenUpdating = False</pre>

    and return things to normal when the prcessing is finished with<pre> Application.ScreenUpdating = True</pre>


    Andrew C

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: freezing on one screen as macro proceeds (excel 2000)

    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:

    <pre> Application.ScreenUpdating = False
    ' Do your copying
    Application.ScreenUpdating = True
    </pre>

    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: freezing on one screen as macro proceeds (excel 2000)

    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(' <img src=/S/bash.gif border=0 alt=bash width=35 height=39>') 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

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: freezing on one screen as macro proceeds (excel 2000)

    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.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: freezing on one screen as macro proceeds (excel 2000)

    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:

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


    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.

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

    Legare Coleman

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: freezing on one screen as macro proceeds (excel 2000)

    Actually, since the Copy method allows a destination as a parameter, you can shorten Legare's code to a one liner:
    <pre> Worksheets("Sheet1").Range("A1:C2").Copy _
    Worksheets("Sheet2").Range("A1")</pre>


    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:
    <pre> Range("A1").Select
    ActiveCell.FormulaR1C1 = "Test"</pre>

    You can collapse this to
    <pre> Range("A1").FormulaR1C1 = "Test"</pre>

    or even
    <pre> Range("A1") = "Test"</pre>

    but to be on the safe side
    <pre> Sheets("Sheet1").Range("A1") = "Test"</pre>

    is best because Excel can't mess you up with it's defaults. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: freezing on one screen as macro proceeds (excel 2000)

    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....
    <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    As I said, "This is a GREAT board",
    tia
    Bill

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: freezing on one screen as macro proceeds (excel 2000)

    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 <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>. 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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: freezing on one screen as macro proceeds (excel 2000)

    Especially Access - you can't record macros! And there's DAO vs. ADO.....
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: freezing on one screen as macro proceeds (excel 2000)

    Yeah, But Access is FUN,!! (As long as you keep taking the tablets <img src=/S/grin.gif border=0 alt=grin width=15 height=15> )

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: freezing on one screen as macro proceeds (excel 2000)

    For me, with Access, there's only DAQ's. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: freezing on one screen as macro proceeds (excel 2000)

    Snap!

  14. #14
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: freezing on one screen as macro proceeds (excel 2000)

    Yes, probably best you didn't!

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: freezing on one screen as macro proceeds (excel 2000)

    Babbling in public again? <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •