song1st

若想要在VBA運作時，先關掉所有的conrtrols，等運作完再打開可以參考以下的做法 Controls項目昰在UserForm上

Private Sub CommandButton_Click()
    'Disable All Controls while running
    DisableAllControls Me
 
    'Do anything you want...

    'Enable All Controls after finish
    EnableAllControls Me
End Sub


Function EnableAllControls(frm As UserForm)
    Dim ctrl As Controls
    For Each ctrl In frm.Control
        If TypeName(ctrl) = "CommandButton" Or TypeName(ctrl) = "ListBox" Or TypeName(ctrl) = "CheckBox" Or TypeName(ctrl) = "TextBox" Then
            ctrl.Enabled = True
        End If
    Next
    Set ctrl = Nothing
End Function

Function DisableAllControls(frm As UserForm)
    Dim ctrl As Controls
    For Each ctrl In frm.Control
        
        If TypeName(ctrl) = "CommandButton" Or TypeName(ctrl) = "ListBox" Or TypeName(ctrl) = "CheckBox" Or TypeName(ctrl) = "TextBox" Then
            ctrl.Enabled = False
        End If
    Next
    Set ctrl = Nothing
End Function

如果Controls昰放在Workbook上的

Private Sub CommandButton_Click()
    'Disable All Controls while running
    DisableAllControls
 
    'Do anything you want...

    'Enable All Controls after finish
    EnableAllControls
End Sub

Function EnableAllControls()
    Dim wks As Worksheet
    For Each wks In Worksheets
        Worksheet.DrawingObjects.Enabled = True
    Next
End Function

Function DisableAllControls()
    Dim wks As Worksheet
    For Each wks In Worksheets
        Worksheet.DrawingObjects.Enabled = False
    Next
End Function