Userform and screen updates

[kanadaaa]

Hi, I have a question about userforms.
Say, we have a very simple form with just one commandbutton on it with the code below:

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Me.Hide
    Me.Show
    Application.ScreenUpdating = True
End Sub

When the button is hit, the form still "flashes" when it goes invisible and then comes back visible.
It thus seems Application.ScreenUpdating = False doesn't prevent this.
Is there any way to prevent it? Thanks.

 

[Anthony47]

ScreenUpdating refers to the "application" (ie Excel), not the Form.

May I ask you which is your need?

Bye

 

[kanadaaa]

Hi, thanks for your reply.
Say, we add a textbox to a userform by a macro with the Controls.Add method and then specifies its Top and Left properties.
This once generates the textbox in the upper left corner of the form, so we can see the box in that position for a moment before they get relocated.
In a similar vein, say we once hide the form and make it reappear by a macro with the Hide and Show methods.
This of course hides the form for a moment and makes it reappear, so it looks like the form flickers. (I believe this is the easiest way to adjust the position of the form to the centre of the screen, although I'm sure there are other ways to do this.)
I'm wondering if it's possible to make invisible any updates on the form like those just mentioned.
Hope this makes my question clearer.

 

[mikerickson]

WIth the textbox situation, I've never noticed a flicker if I position the text box immediately after creating it. If there needs to be a programatic delay, then you could .Visible of the new textbox to False immedatly after creation and .True once you have it in place.

 

[kanadaaa]

you could .Visible of the new textbox to False immedatly after creation and .True once you have it in place.

Thanks for your reply.
Actually I had this idea, but your comment made me think maybe this is the only way to achieve this, so I tried it again.
As for adjusting the position of the UF to the centre of the screen, I just decided to use a different code.
Just for your reference...

Private Sub CommandButton1_Click()
    Dim h As Single, w As Single 

    h = Me.Height
    w = Me.Width
  
    'Add invisible textbox and make it visible after locating it on the form
    With Me.Controls.Add("Forms.TextBox.1", "TextBox1", False)
        .Top = 20
        .Left = 20
        .Visible = True
    End With

    '==============================================================
        'Some action that changes the height or width of the form
    '==============================================================         
  
    'Always show the UF in the middle of the screen (Prerequisite: the height and width never become less taller or wider)
    If Me.Height <> h Then
        Me.Top = Me.Top - ((Me.Height - h) / 2)
    End If
    If Me.Width <> w Then
        Me.Left = Me.Left - ((Me.Width - w) / 2)
    End If
  
End Sub

But still, if anyone has an idea on how to prevent a flicker caused by Me.Hide then Me.Show, I'd appreciate it if they could share it with me. Thanks.

 

[kanadaaa]

I forgot to mention the most important thing, a code like the one in #5 prevents flickers the way I want it to.

 

[Jaafar Tribak]

You can add a custom Property to the userform to toggle the form's ScreenUpdating :

In the UserForm Module:

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function SendMessage Lib "user32.dll" Alias "SendMessageA" (ByVal Hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, ByRef lParam As Any) As LongPtr
    Private Declare PtrSafe Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal Hwnd As LongPtr) As Long
#Else
    Private Declare Function SendMessage Lib "user32.dll" Alias "SendMessageA" (ByVal Hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByRef lParam As Any) As Long
    Private Declare Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal Hwnd As Long) As Long
#End If


Property Let ScreenUpdating(ByVal vNewValue As Boolean)
    Const WM_SETREDRAW = &HB
    #If Win64 Then
        Dim Hwnd As LongLong
    #Else
        Dim Hwnd As Long
    #End If
    Call IUnknown_GetWindow(Me, VarPtr(Hwnd))
    Call SendMessage(Hwnd, ByVal WM_SETREDRAW, ByVal CLng(vNewValue), 0)
End Property


'Test
Private Sub CommandButton1_Click()
    On Error GoTo errHandler
        Me.ScreenUpdating = False
            With Me.Controls.Add("Forms.TextBox.1", "TextBox1", False)
                .Top = 20
                .Left = 20
                .Visible = True
            End With
            Me.Hide
            Me.Show
errHandler:
        Me.ScreenUpdating = True
End Sub

 

[kanadaaa]

You can add a custom Property to the userform to toggle the form's ScreenUpdating :

Wow, this is brilliant! Thank you so much

 

[audeser]

Although the post is closed, and I don't want to hijack it, I get an error that raises on the CommandButton1 last line (Me.ScreenUpdating = True) when performing a QueryClose of the userForm. I can't get to understand how on earth the CommandButton runs on that operation . Am I the only one getting this error? (Office 2019 x86). If needed, I can resume on new thread, although is related to this specific code, with no additons. So, no questions, just my workaround, that was like this, replacing that line with:

errHandler:
    Call Refresher
End Sub
Private Sub Refresher()
    Me.ScreenUpdating = True
End Sub

Aside from this, having a ScreenUpdating block option is gorgeous if does work (I use the userform to draw thousands of lines with windows API, and I think this property could come very handy on that kind of projects).

And to finish the inconvenience... Windows Defender barked a warning (Trojan:O97M/Mountsi.D!ml) with the code. . Did close the file but did not deleted the VBA.bin (maybe because I have the folder in exceptions). This funny WindowsDefender .