我用的代码是回应与链接我已经附上下面,我有一些问题,你能帮我吗?
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
这里是它挂起的地方,不让我点击任何东西。
1条答案
按热度按时间ny6fqffe1#
您已停用屏幕更新,您需要在
Sub extractData()
末尾重新激活它,然后显示您的用户表单:您已经:
切换到: