在我的进度条的 excel vba 用户表单上隐藏关闭 [X] 按钮

Hide close [X] button on excel vba userform for my progress bar

提问by forums

I created a userform to show a progress bar when the macro is still importing sheets

我创建了一个用户窗体来在宏仍在导入工作表时显示进度条

The problem is the user can press the red [X]button that will close and interrupt the processing done.

问题是用户可以按下红色的[X]按钮来关闭并中断已完成的处理。

Is there a way to hide this red button of doomso that potential users don't have any confusing buttons to click while it runs.

有没有办法隐藏这个红色的厄运按钮，以便潜在用户在运行时没有任何令人困惑的按钮可以点击。

edit:

编辑：

I have tried this

我试过这个

'Find the userform's Window
Private Declare Function FindWindow Lib "user32" _
        Alias "FindWindowA" ( _
        ByVal lpClassName As String, _
        ByVal lpWindowName As String) As Long

'Get the current window style
Private Declare Function GetWindowLong Lib "user32" _
        Alias "GetWindowLongA" ( _
        ByVal hWnd As Long, _
        ByVal nIndex As Long) As Long

'Set the new window style
Private Declare Function SetWindowLong Lib "user32" _
        Alias "SetWindowLongA" ( _
        ByVal hWnd As Long, _
        ByVal nIndex As Long, _
        ByVal dwNewLong As Long) As Long

Const GWL_STYLE = -16
Const WS_SYSMENU = &H80000

and I used this on userform_initialize

我在 userform_initialize 上使用了这个

   Dim hWnd As Long, lStyle As Long

   'Which type of userform
   If Val(Application.Version) >= 9 Then
      hWnd = FindWindow("ThunderDFrame", Me.Caption)
   Else
      hWnd = FindWindow("ThunderXFrame", Me.Caption)
   End If

   'Get the current window style and turn off the Close button
   lStyle = GetWindowLong(hWnd, GWL_STYLE)
   SetWindowLong hWnd, GWL_STYLE, (lStyle And Not WS_SYSMENU)

I am getting this error message

我收到此错误消息

this code was taken from here. I don't know what I'm doing wrong and I already removed the comments. This is the simplest code that I found so I would like to integrate it to my userform. Any help is appreciated.

此代码取自此处。我不知道我做错了什么，我已经删除了评论。这是我找到的最简单的代码，所以我想将它集成到我的用户表单中。任何帮助表示赞赏。

回答by Peter Albert

Below is a routine that you can call like this:

下面是一个你可以这样调用的例程：

subRemoveCloseButton MyForm

or from within your form:

或从您的表单中：

subRemoveCloseButton Me 

Here's the code you'll need:

这是您需要的代码：

Private Const mcGWL_STYLE = (-16)
Private Const mcWS_SYSMENU = &H80000

'Windows API calls to handle windows
#If VBA7 Then
    Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
#Else
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
#End If

#If VBA7 Then
    Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
#Else
    Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
#End If

#If VBA7 Then
    Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
#Else
    Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
#End If


Public Sub subRemoveCloseButton(frm As Object)
    Dim lngStyle As Long
    Dim lngHWnd As Long

    lngHWnd = FindWindow(vbNullString, frm.Caption)
    lngStyle = GetWindowLong(lngHWnd, mcGWL_STYLE)

    If lngStyle And mcWS_SYSMENU > 0 Then
        SetWindowLong lngHWnd, mcGWL_STYLE, (lngStyle And Not mcWS_SYSMENU)
    End If

End Sub

回答by JustinJDavies

You can work it out from the following snippets:

您可以从以下片段中计算出来：

Select the cmdClosebutton On the Menu bar, choose View | CodeWhere the cursor is flashing, enter the following code:

选择cmdClose按钮 在菜单栏上，选择View | Code光标闪烁处，输入以下代码：

Private Sub cmdClose_Click()
  Unload Me
End Sub

On the Menu bar, choose View | Object, to return to the UserForm.

在菜单栏上，选择View | Object, 以返回到用户窗体。

To allow users to close the form by pressing the Esc key:

允许用户通过按 Esc 键关闭表单：

Select the cmdClose button In the Properties window, change the Cancelproperty to True

选择 cmdClose 按钮​​ 在 Properties 窗口中，将Cancel属性更改为True

To prevent users from closing the form by clicking the X button

防止用户通过单击 X 按钮关闭表单

When the UserFormis opened, there is an Xat the top right. In addition to using the Close Form button, people will be able to close the form by using the X. If you want to prevent that, follow these steps.

当UserForm打开时，有一个X在右上角。除了使用关闭表单按钮之外，人们还可以使用 X 关闭表单。如果您想阻止这种情况，请按照以下步骤操作。

Right-click on an empty part of the UserForm Choose View | CodeFrom the Procedure dropdown, at the top right, choose QueryClose

右键单击用户窗体的空白部分View | Code从过程下拉列表中选择，在右上角，选择 QueryClose

Where the cursor is flashing, paste the highlighted code from the following sample

在光标闪烁的地方，粘贴以下示例中突出显示的代码

Private Sub UserForm_QueryClose(Cancel As Integer, _
  CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the Close Form button!"
  End If
End Sub

On the Menu bar, choose View | Object, to return to the UserForm. Now, if someone clicks the Xin the UserForm, they'll see your message.

在菜单栏上，选择View | Object, 以返回到用户窗体。现在，如果有人单击X用户窗体中的 ，他们将看到您的消息。

from http://www.contextures.com/xlUserForm01.html

来自http://www.contextures.com/xlUserForm01.html

回答by P??

This is an improvement of the above answer of @Peter Albert

这是@Peter Albert的上述答案的改进

• Windows API calls are now Office x64 safe
• FindWindowcall was improved to find Excel UserForms only. The function in the original answer searches every window class (e.g. Explorer windows and other program's windows). Therefore it could happen that the [x] button of other programs or explorer windows have been removed when their name was the same name as the UserForm.

• Windows API 调用现在是 Office x64 安全的
• FindWindow调用已改进为仅查找 Excel 用户窗体。原始答案中的函数搜索每个窗口类（例如资源管理器窗口和其他程序的窗口）。因此，当其他程序或资源管理器窗口的名称与用户窗体名称相同时，它们的 [x] 按钮可能已被删除。

---

---

Private Const mcGWL_STYLE = (-16)
Private Const mcWS_SYSMENU = &H80000

'Windows API calls to handle windows
Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" ( _
    ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr

#If Win64 Then
    Private Declare PtrSafe Function GetWindowLongPtr Lib "user32" Alias "GetWindowLongPtrA" ( _
        ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr
    Private Declare PtrSafe Function SetWindowLongPtr Lib "user32" Alias "SetWindowLongPtrA" ( _
        ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
#Else
    Private Declare PtrSafe Function GetWindowLongPtr Lib "user32" Alias "GetWindowLongA" ( _
        ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr
    Private Declare PtrSafe Function SetWindowLongPtr Lib "user32" Alias "SetWindowLongA" ( _
        ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
#End If

Public Sub RemoveCloseButton(objForm As Object)
    Dim lngStyle As LongPtr
    Dim lngHWnd As LongPtr

    Dim lpClassName As String
    lpClassName = vbNullString
    If Val(Application.Version) >= 9 Then
       lpClassName = "ThunderDFrame"
    Else
       lpClassName = "ThunderXFrame"
    End If

    lngHWnd = FindWindow(lpClassName, objForm.Caption)
    lngStyle = GetWindowLongPtr(lngHWnd, mcGWL_STYLE)

    If lngStyle And mcWS_SYSMENU > 0 Then
        SetWindowLongPtr lngHWnd, mcGWL_STYLE, (lngStyle And Not mcWS_SYSMENU)
    End If
End Sub

ThunderDFrame?
The UserForms in Excel are actually of the Windows class ThunderDFrame, which is the class for all UserFroms in Microsoft Office applications after 2002. Before that, it was ThunderXFrame.

迅雷DFrame？
Excel中的UserForms其实是Windows类ThunderDFrame，是2002年以后Microsoft Office应用中所有UserFroms的类。在此之前，它是ThunderXFrame.

回答by Gravity Grave

A useful way to disable the button is to do the following:

禁用按钮的一种有用方法是执行以下操作：

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then Cancel = True
End Sub

Although this doesn't get ridof the button, but it does make clicking on it accomplish nothing.

虽然这并没有摆脱按钮，但它确实使点击它没有任何作用。

回答by Richard Briggs

Ask the user if they want to close the form - and lose edits (say). Based on ideas from Justin & Peter.

询问用户是否要关闭表单 - 并丢失编辑（例如）。基于 Justin & Peter 的想法。

Private Sub UserForm_QueryClose(Cancel As Integer, _
                            CloseMode As Integer)
Dim ans
If CloseMode = vbFormControlMenu Then
    Cancel = True
    ans = Msgbox("Cancel edit?", vbQuestion + vbYesNo)
    If ans = vbYes Then
       Me.Hide
    End if
End If
End Sub

Edit: Actually I realise this is a bit off topic as OP wanted to remove the X option - but still I find this handy for interactive forms.

编辑：实际上我意识到这有点偏离主题，因为 OP 想要删除 X 选项 - 但我仍然发现这对于交互式表单很方便。

回答by brit0n

I know this is an old question, but for the type of user form the OP cited, you don't have to remove, hide or disable the close button. There is a much simpler way ;)

我知道这是一个老问题，但是对于 OP 引用的用户表单类型，您不必删除、隐藏或禁用关闭按钮。有一个更简单的方法;)

For any user form which does not have any elements which the user interacts with (buttons etc) and which will close itself when it has finished its purpose, simply disabling the form is fine.

对于没有任何用户与之交互的元素（按钮等）并且在完成其目的后会自行关闭的任何用户表单，只需禁用该表单就可以了。

To disable the user form: In the user form's properties, against Enabled set False. The user form will show until it's code tells it to hide. The user will not be able to do anything to the form (cannot close, cannot move etc).

禁用用户表单：在用户表单的属性中，针对 Enabled 设置 False。用户表单将显示，直到它的代码告诉它隐藏。用户将无法对表单执行任何操作（无法关闭、无法移动等）。

Note also that whether you want the user to be able to do anything else in the main window while the user form is still showing decides whether you set ShowModal.

另请注意，您是否希望用户能够在用户窗体仍在显示时在主窗口中执行任何其他操作取决于您是否设置 ShowModal。