CODEWITHSUNDEEP
vbaexcel
BuZz
BuZz

Reputation: 17485

Is it possible to freeze Excel while I process something?

Is it possible to freeze Excel while I process something ? I am using 2007. I know already that freezing the update of the sheets from VBA is possible, but I'm looking for a little freeze of the whole thing (with a wait cursor and maybe something nice, like darkening the window or something) .

Has anyone ever tried such thing from VBA ?

Upvotes: 2

Views: 12288

Answers (2)

Jon Crowell
Jon Crowell

Reputation: 22348

To freeze Excel during processing, set screenupdating to false. Make sure to set it back to true when you're finished or the user won't be able to interact with Excel. In addition to not scaring your users with a bunch of movement and flashing on the screen, this dramatically speeds up processing.

Sub Sample()
    Application.ScreenUpdating = False

    '~~> Rest of the code

    Application.ScreenUpdating= True
End Sub

I agree with JMax that a progress bar is a great option. You can find several different Excel VBA progress bars here: http://spreadsheetpage.com/index.php/blog/progress_bars_the_movie/

BTW, you can skip the movie and go straight to the links below. You may find the movie funny, but all it says is "progress bars = good, spinning stuff = bad".

Upvotes: 2

Siddharth Rout
Siddharth Rout

Reputation: 149315

Has anyone ever tried such thing from VBA ?

Yes. Depending on the kind of processing that I am doing either I hide the entire Excel Application or show a userform with a progress update to avoid any user interference

Example 1 (Hiding The Application)

Option Explicit

Sub Sample()
    Application.Visible = False

    '~~> Rest of the code

    Application.Visible = True
End Sub

Example 2 (Using a Userform which has the 'X' disabled)

Create a Userform and place this code there

Option Explicit

Private Declare Function RemoveMenu Lib "user32" (ByVal hMenu As Long, ByVal nPosition As Long, _
ByVal wFlags As Long) As Long

Private Declare Function GetSystemMenu Lib "user32" (ByVal hwnd As Long, ByVal bRevert As Long) As Long

Private Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Const MF_BYPOSITION = &H400&

Private Sub UserForm_Initialize()
    Dim lHwnd As Long

    lHwnd = FindWindow("ThunderDFrame", "Please Wait...")

    Do While lHwnd = 0
        lHwnd = FindWindow("ThunderDFrame", "Please Wait...")
        DoEvents
    Loop

    RemoveMenu GetSystemMenu(lHwnd, 0), 6, MF_BYPOSITION
End Sub

'~~> The below code will not be there
'~~> This is just there so that if you try the above code then you can exit the form
Private Sub UserForm_Click()
    Unload Me
End Sub

And the usage will be like this

Option Explicit

Sub Sample()
    UserForm1.Show vbModeless

    '~~> Rest of the code

    Unload UserForm1
End Sub

Upvotes: 3

Related Questions