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

    stop the flashing (office 2000)

    Hello to all,
    I know that I asked this question once before, however, I could not find the post and did not print the answer the first time sooooooo here it is again.
    What is the code line that I should put in to stop the panels from flashing when I am doing a semi simple sort by macro.
    tia,
    Bill

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: stop the flashing (office 2000)

    Put Application.ScreenUpdating = False before the code, and Application.ScreenUpdating = True after it. It's best to make sure that the latter statement will ALWAYS be executed, even if an error occurs:

    Sub MyMacro
    On Errror GoTo ErrHandler

    ' Show hourglass and turn screen updating off
    Application.Cursor = xlWait
    Application.ScreenUpdating = False

    ' Your code goes here

    ExitHandler:
    ' Clean up, then exit
    Application.ScreenUpdating = True
    Application.Cursor = xlDefault
    Exit Sub

    ErrHandler:
    ' Inform user
    MsgBox Err.Description, vbExclamation
    ' Always clean up
    Resume ExitHandler
    End Sub

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

    Re: stop the flashing (office 2000)

    Hans,
    Thanks for the quick response. That will work just great. Since I have you thinking, I also have another problem that I am working on. I want to save this file with a "Save As: ... " however I am stuck at that point as to how to require the user to save this file that has been updated now with new information as a new name and in the same file folder. (Example) The original file is found in file C: workexcel sheetnov.xls. What I want is once the data is updated and they press the (newly created button called ) "Save this file as:" button, is should just ask for the new name to call it and save in the same file folder. Should I be using a message box for this or is there a cleaner way?
    I hope this is clear and tia for all the help.
    Bill

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: stop the flashing (office 2000)

    A message box is not the tool for this, since you can't use it to prompt the user for a name. You could use InputBox instead, but there are better ways. If you want to show the standard Save As dialog, use

    Application.Dialogs(xlDialogSaveAs).Show

    If you want more control, you can use code like this

    Dim varFileName As Variant
    varFileName = Application.GetSaveAsFilename(ActiveWorkbook.FullN ame)
    If varFileName = False Then
    ' Canceled
    Exit Sub
    End If
    ' Tests go here
    If varFileName = ActiveWorkbook.FullName Then
    MsgBox "Don't save the workbook under the same name.", vbExclamation
    Exit Sub
    End If
    ActiveWorkbook.SaveAs varFileName

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

    Re: stop the flashing (office 2000)

    Hans,
    Again, Thank You so much, I ment to say (type) box and keyed the wrong one. I do like your more controling code better than the Save As box anyway. Can I tap your knowledge one more time:
    I have a macro that is on the sheet and must be run before I do the close. i am trying to incorporate it in with the information that you have given me and I keep coming up with an error. I am listing it as:
    Aplication.Run_
    "'ActiveWorksheet ! run me' "
    I can't put the current name of the work sheet in code because it will change each time they save each month.
    tia
    Bill

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: stop the flashing (office 2000)

    >> I have a macro that is on the sheet

    Do you mean an Excel4 macro sheet? If so, I'm afraid I can't help you, I have no experience with that kind of macro.
    Or do you mean a VBA macro? In that case, it shouldn't be necessary to use Application.Run to start it.

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

    Re: stop the flashing (office 2000)

    Hans,
    No, this is VBA. I did not mean to confuse you. Are you saying that I can just list the name of the macro that should run in the begining of the "close" macro and it will run?
    tia
    Bill

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: stop the flashing (office 2000)

    Yes, to run Macro1 from Macro2, just use its name:

    Sub Macro2()
    ...
    Macro1
    ...
    End Sub

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

    Re: stop the flashing (office 2000)

    Hans,
    Thanks for the patience. I appreciate that you did not just think that I am an idiot. I have learned a lot from posting and just reading. You are GREAT help and this board is the best for help, learning, and feeling at home. Keep this open and you know I will be back with more questions. [img]/forums/images/smilies/smile.gif[/img]
    Thanks Again.
    Bill

Posting Permissions

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