excel VBA对话框选择范围在不同的工作簿挂起

tf7tbtn2  于 2023-01-21  发布在  其他
关注(0)|答案(1)|浏览(125)

我用的代码是回应与链接我已经附上下面,我有一些问题,你能帮我吗?
VBA Dialog box to select range in different workbook
当我点击referedit框来选择我想使用的范围时,它挂起了,当我点击时,我听到一个声音,说有另一个窗口在某个地方打开,具有更高的优先级,但我找不到它。
基本上,我尝试做的是从一个工作簿中复制指定的列,并将它们粘贴到原始工作簿的一个工作表中。我知道我的“copyButton_Click()”编码也不正确,但我无法深入调试以修复它。以下是我的代码:
模块1:

Sub extractData()
    Dim FName As Variant
    Dim wb As Workbook
    Dim destSheet As String
    '
    Application.ScreenUpdating = False
    destSheet = "NewData"
    '
    'Selects and clears data
    Sheets(destSheet).Select
        Range("A2:I12000").Select
        Selection.delete Shift:=xlUp
        Range("A2").Select
    '
    'Prompts user to select updated ILP file to copy data from:
    FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls;*.xlsx;*.xlsm), *.xls;*.xlsx;*.xlsm")
        If FName <> False Then
        Set wb = Workbooks.Open(FName)
    '
    ExtractCompareUserForm.Show vbModeless
    '
        End If

    Application.ScreenUpdating = True

    End Sub

用户表单代码:

Private Sub UserForm_Initialize()
        Dim wb As Workbook

    '~~> Get the name of all the workbooks in the combobox
        For Each wb In Application.Workbooks
            ComboBox1.AddItem wb.Name
        Next

        ComboBox1 = ActiveWorkbook.Name
    End Sub

    '~~> This lets you toggle between all open workbooks
    Private Sub Combobox1_Change()
        If ComboBox1 <> "" Then Application.Workbooks(ComboBox1.Text).Activate

        Label1.Caption = "": RefEdit1 = ""
    End Sub

    '~~> This lets you choose the relevant range
    Private Sub RefEdit1_Change()
        Label1.Caption = ""

        If RefEdit1.Value <> "" Then _
        Label1.Caption = "[" & ComboBox1 & "]" & RefEdit1
    End Sub

    Private Sub copyButton_Click()
    Dim addr As String
    '
    addr = RefEdit1.Value
    '
    'Copy Data:
        UserForm1.addr = Selection.Address
        addr.Copy

    End Sub

    Private Sub PasteButton_Click()
    Dim destSheet As String
    '
    Workbooks(2).Close SaveChanges:=False
    '
    '       Now, paste to working workbook:
            Sheets("NewData").Activate
            Range("B2").Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
        Unload Me
        '
    Call CopyData

    End Sub

这里是它挂起的地方,不让我点击任何东西。

ny6fqffe

ny6fqffe1#

您已停用屏幕更新,您需要在Sub extractData()末尾重新激活它,然后显示您的用户表单:
您已经:

ExtractCompareUserForm.Show vbModeless
End If
Application.ScreenUpdating = True
End Sub

切换到:

Application.ScreenUpdating = True
    ExtractCompareUserForm.Show vbModal
End If
End Sub

相关问题